Failed Update Query

[lorena]

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 didn't 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 (didn't 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

[Afraits]

Try putting [] around the ID to make your SQL as follows:

Update NumberGen set Counter = ~ where [iD] like 'SK'

 

Previously I've 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.

[lorena]

Thanks for the idea. I tried it and I am still getting the same error

[Afraits]

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 you've checked what strSQL contains after the replace, though posting that might help shed some light on it.

[lorena]

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!!

[Afraits]

No problem, its one of those things I banged my head against the wall over a year or two back, trust me, you won't forget this solution :)