Failed Update Query

lorena

lorena

Well-known member
Joined
Oct 23, 2003
Messages
134
Location
Phoenix, Arizona
I have an asp.net form that adds a record to a table and then increments a counter variable in another table before closing the connection. The add record part works fine but I am getting "Syntax error in UPDATE statement" when I try to update the incremented field in the counter table. (I started trying to do the operation as an INSERTbut that didnt work either)
Here is my code:
strSQL = "Update NumberGen set Counter = ~ where ID like SK"
strSQL = Replace(strSQL, "~", intCounter)
objCommand = New OleDb.OleDbCommand(strSQL, objConn)
objCommand.ExecuteNonQuery()
objConn.Close()
I have tried substituting an actual number in place of intCounter (didnt work) and also tried running it against the access database with an actual number in place of the variable (worked fine).
Any help would be appreciated. Thanks
 
Try putting [] around the ID to make your SQL as follows:
Update NumberGen set Counter = ~ where [ID] like SK

Previously Ive encountered problems such as this, the Access SQl parser seems to correctly handle it but when running from VB or .NET code I needed to put [] around certain field names to prevent them being interpretted as keywords. Date, Time, Key are other common causes of this.
 
Maybe fully qualify all the fields so it looks like

Update [NumberGen] set [NumberGen].[Counter] = ~ where ([NumberGen].[ID] like SK)

I can see nothing wrong with the syntax you have so this is the only thing I can think of. I presume youve checked what strSQL contains after the replace, though posting that might help shed some light on it.
 
IT WORKS!!!!! You are right, it must be some wierd thing with the parser but as soon as I set it up the way you suggested, it works fine.
Thank you so very much!!
 
No problem, its one of those things I banged my head against the wall over a year or two back, trust me, you wont forget this solution :)
 
Back
Top