INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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)

CODE -->

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

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

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

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

(OP)
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?

CODE --> 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?

CODE --> Values

// 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.

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

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

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

(OP)
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 -
  • The First machine Pervasive.Data.SqlClient File version = 4.0.0.004010
  • The Second machine Pervasive.Data.SqlClient File version = 3.2.45.000545
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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close