...

View Full Version : Syntax error in UPDATE statement



designer_bhutan
09-11-2005, 05:43 PM
can any 1 help me with this code:

rs.Open "update status set current='" & current & "' where id like '" & id &"'", conn


i am getting the following error but cant figure out why:


Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.

thanks

miranda
09-12-2005, 01:55 AM
is the current field a text or memo data type?
if not then the code in blue is your error
is the id field a text or memo data type ?
if not then the code in red is your error
if neither field is of the text or memo data type then both fields are in error
rs.Open "update status set current='" & current & "' where id like '" & id &"'" , conn

also you do not need to use a recordset connection you can use the connection objects execute method and save on server overhead like so

if incorrect current data type
conn.Execute("update status set current=" & current & " where id = '" & id & "'")

if incorrect id data type
conn.Execute("update status set current='" & current & "' where id = " & id)

if both data types are incorrect
conn.Execute("update status set current=" & current & " where id = " & id)

glenngv
09-12-2005, 07:23 AM
Are you using Access as database?
I don't know if you are using any reserved keyword but try enclosing all the field names in square brackets.


rs.Open "update [status] set [current]='" & current & "' where [id] like '" & id &"'", conn

sonal
09-12-2005, 09:18 AM
if your your fields is numeric than remove single quote and check it.

designer_bhutan
09-12-2005, 11:14 AM
rs.Open "update status set current='" & current & "' where id like '" & id &"'", conn


current is a text field & id is an autonumber...

i tried the above code but still the same error.

glenngv
09-12-2005, 01:28 PM
Try this:

rs.Open "update [status] set [current]='" & current & "' where [id]=" & id, conn

designer_bhutan
09-12-2005, 04:01 PM
thanks buddy this one is working... :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum