C# Pervasive Data Provider Update command causing Syntax error, but not on development machine.

[mSportsman]

I am developing a C#.NET app using the three tier data model.

On my development machine, this works flawlessly. One other machines with same OS.
An exception occurs - "Pervasive.Data.SqlClient.Lna.k: [LNA][ODBC Engine Interface]Syntax Error."
... This is the full message

Other info I got from the debugger in C# - SQLState proerty from the PsqlException = 37000

(See atachement for full code Link)

                connection.Open();
                int count = updateCommand.ExecuteNonQuery();      // THIS LINE IF THROWING AN EXCEPTION
                if (count > 0)
                    return true;
                else
                    return false;

Any ideas on what is going on here? How to troubleshoot? etc?
Thanks,
Mike

 

[mirtheil]

A few questions:
1. Are you specifying the same values on both machine? You said it's the same OS, but are you giving the same values?
2. Are you accessing the same database or different databases?
3. What does your actual UPDATE statement look like when it fails?


Mirtheil

http://www.mirtheil.com

 

[mSportsman]

Answer to questions:
1. Are you specifying the same values on both machine? You said it's the same OS, but are you giving the same values?

Values From Machine That Works			     Values From Machine That Throws Exception	
   Paramerter p1= 	1		                Paramerter p1= 	1
   Paramerter p2= 	1		                Paramerter p2= 	1
   Paramerter p3= 			                Paramerter p3= 	
   Paramerter p4= 	JCB		                Paramerter p4= 	JCB
   Paramerter p5= 	16 13861		        Paramerter p5= 	16 13861
   Paramerter p6= 	Truss Bearing Assembly		Paramerter p6= 	Truss Bearing Assembly
   Paramerter p7= 	2		                Paramerter p7= 	2
   Paramerter p8= 	1		                Paramerter p8= 	1
   Paramerter p9= 	1		                Paramerter p9= 	1
   Paramerter p10= 	1		                Paramerter p10= 	1
   Paramerter p11= 	JCB		                Paramerter p11= 	JCB
   Paramerter p12= 	16 13861		        Paramerter p12= 	16 13861
   Paramerter p13= 	Truss Bearing Assembly		Paramerter p13= 	Truss Bearing Assembly
   Paramerter p14= 	4		                Paramerter p14= 	4
   Paramerter p15= 	1		                Paramerter p15= 	1

These values are taken from the output window of the debugger, and are the same for both cases

2. Are you accessing the same database or different databases?

I am accessing the same database.​

3. What does your actual UPDATE statement look like when it fails?

// From machine that works
updateCommand.CommandText =
"UPDATE PartRequisitionLines SET RequisitionLineID = ?, RequisitionID = ?, StockPartID = ?, Manufacture = ?, MfgPartNumber = ?, PartDescription = ?, QuantityRequired = ?, UnitsOfMeasureID = ? \r\nWHERE      RequisitionLineID = ? AND RequisitionID = ? AND StockPartID  IS NULL AND Manufacture  = ? AND MfgPartNumber  = ? AND PartDescription = ? AND QuantityRequired = ? AND UnitsOfMeasureID  = ? ;"
 

// From machine that throws exception
updateCommand.CommandText =
"UPDATE PartRequisitionLines SET RequisitionLineID = ?, RequisitionID = ?, StockPartID = ?, Manufacture = ?, MfgPartNumber = ?, PartDescription = ?, QuantityRequired = ?, UnitsOfMeasureID = ? \r\nWHERE      RequisitionLineID = ? AND RequisitionID = ? AND StockPartID  IS NULL AND Manufacture  = ? AND MfgPartNumber  = ? AND PartDescription = ? AND QuantityRequired = ? AND UnitsOfMeasureID  = ? ;"


These values are taken from the output window of the debugger, and are the same for both cases.  This is as close to the actual SQL statement I can get because of the parameters.

Other interesting notes:
Even though I am getting this exception the data is being updated. When I come back into the program a second time, the values that are loaded reflect the changes that were made.

 

[mirtheil]

A couple more questions and a suggestion:
- Does the exception happen every time on the other machine or only specific cases?
- Does it every happen on the Development machine?
- Are both machines using the same version of your app and the same version of the Pervasive Client (including the Pervasive.Data.SqlClient.DLL)?

One test, remove the semicolon from the end of the statement. The semicolon is not needed by Pervasive and is used as a statement separator in the Pervasive Control Center.


Mirtheil

http://www.mirtheil.com

 

[mSportsman]

The exception does happen every time and never one the first machine. I have even installed visual studio on the other machine so I can run under the IDE debugger.
The first machine always succeeds and the second machine always throws exception.

Both machines are using the same version of the app. But the Pervasive data provider may be different - Here is what I found -
[ul]
[li]The First machine Pervasive.Data.SqlClient File version = 4.0.0.004010[/li]
[li]The Second machine Pervasive.Data.SqlClient File version = 3.2.45.000545[/li]
[/ul]

Also I removed the semicolon from the end of the SQL statements, and now the program is working on the both machines.
Must have been something to do with the semicolon next to the parameter marker in the older *.dll.

I always put the semicolon at the end in case I every end up pasting it into the PCC for trouble shooting or what ever. I like having it in case I am dealing with multiple statements, it is just a habit of mine. Looks like it may be a bad habit.

Thank you once again for all of your help. I learn a little more all the time thanks to you Mirtheil.

Mike

 

[mirtheil]

Glad you got it working. I wonder if there was defect in the older DLL that caused the engine to think there was a second statement after the semicolon, the engine tried to execute it, and it failed with the syntax error.


Mirtheil

http://www.mirtheil.com