i am using asp to update a record and keep getting an error in the update statement. SQL = "UPDATE info SET dept='" & dept & "',strsect='" & sect & "',person='" & person & "',comp='" & comp & "',role='" & role & "' WHERE ID=" & ID Code (markup): response.write gives: UPDATE info SET dept='this',strsect='is',person='a',comp='test',role='update' WHERE ID=5359 Code (markup): Utilizing an access database, any help would be appreciated. Im going insane!
Thanks for your reply. The error i am receiving is: Error Type: Microsoft JET Database Engine (0x80040E14) Syntax error in UPDATE statement. /update_record.asp, line 39
Yes, ID is autonumber, all the rest are text. There is a username and date field that are not being updated as well, but both are text.
There doesn't seem to be any error in the update statement itself.. How are you executing the update statement?
contents of update_record.asp <%@ language="vbscript" %> <% option explicit %> <html> <head><title>update record</title> </head> <body> <% dim sql, connection, recordset, id dim sconnstring, dept, sect, person, comp, role id = request.form("id") dept = request.form("dept") sect = request.form("sect") person = request.form("person") comp = request.form("comp") role = request.form("role") sql = "update info set dept='" & dept & "',strsect='" & sect & "',person='" & person & "',comp='" & comp & "',role='" & role & "' where id=" & id set connection=server.createobject("adodb.connection") sconnstring="provider=microsoft.jet.oledb.4.0;" & "data source=" & server.mappath("db.mdb") connection.open(sconnstring) connection.execute(sql) 'response.write sql connection.close set connection=nothing response.write "<br><div align='center'>record updated.</div>" %> <a href="table.asp">display table</a> </body> </html> Code (markup):
You will need to put brackets in role like this [role] sql = "update info set [dept]='" & dept & "',[strsect]='" & sect & "',[person]='" & person & "',[comp]='" & comp & "',[role]='" & role & "' where id=" & id
role is a reserved word. By the way you should know that constructing the query the way you do is very unsafe for your application. Should anyone attempt to hack your application by inserting sql statements in those variables you couldn't end up with a lot of mess in the database. Google a bit on parametrized query asp.net
Or you may at least want to replace the>> ' if entered in the fields to avoid errors. eg dept = Replace(dept, "'", "")