Help! Syntax Error in UPDATE Statement! ASP

[sonysg]

Help me someone please.
i'am developing user login form ,Adding, Viewing the data is fine, but when I try to do the final phase of updating the database (editing a user), i get the following message:

----------------------------------------------------------------------
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/purchase/UpdateUserRec.asp, line 21

i use the following code to do the update.,plsease note that "rec" is the variable that i pass thru from view form.
---------------------------------------------------------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#Include file="connection.asp"-->
<%
'DecLare the variables
Dim SQLUpdateEdit

strUser = Request.Form("txtUser")
strPassword = Request.Form("txtPassword")
strARight = Request.Form("txtAccessRight")
strDeptCode = Request.Form("txtDeptCode")
strEmail = Request.Form("txtEmail")

SQLUpdateEdit = "UPDATE usertbl SET"
SQLUpdateEdit = SQLUpdateEdit & " user='" & strUser & "', "
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "', "
SQLUpdateEdit = SQLUpdateEdit & " ARight='" & strAright & "', "
SQLUpdateEdit = SQLUpdateEdit & " DeptCode='" & strDeptCode & "', "
SQLUpdateEdit = SQLUpdateEdit & " Email='" & strEmail & "'"
SQLUpdateEdit = SQLUpdateEdit & " WHERE user = '"& Request("rec") & ";"

conn.execute SQLUpdateEdit

conn.close
SET conn = Nothing
response.Redirect("EditSuccess.asp")
%>

----------------------------------------------------------------
thanks in advance!

[JuDDer]

It's probably this line causing you the problem:

SQLUpdateEdit = SQLUpdateEdit & " WHERE user = "&"'"& Request("rec") & ";"

Try changing it to this:

SQLUpdateEdit = SQLUpdateEdit & " WHERE user = "& Request("rec") & ";"

[sonysg]

hi JuDDer ,tried and paste the above command,
the same error msg still appears.

[edited by: sonysg at 8:46 am (utc) on Sep. 14, 2007]

[JuDDer]

Your code sample appears to have changed since I replied, but you're missing an apostrophe now.

Try changing this line:

SQLUpdateEdit = SQLUpdateEdit & " WHERE user = '"& Request("rec") & ";"

To this:

SQLUpdateEdit = SQLUpdateEdit & " WHERE user = '"& Request("rec") & "' ;"

[sonysg]

STILL the same error msg appear .no idea.

[JuDDer]

Try printing the command to the screen by adding response.write SQLUpdateEdit and then commenting out the execute and redirect code:

response.write SQLUpdateEdit
'conn.execute SQLUpdateEdit

conn.close
SET conn = Nothing
'response.Redirect("EditSuccess.asp")

that will print the command string to your screen so you can look to see what values are being used for your variables

[sonysg]

the query is something like this
think the statement is ok, but got no idea why cannot save .

UPDATE usertbl SET user='KAWARA', Passwd='kawau', ARight='USER', DeptCode='ACCTS', Email='[e-mail]' WHERE user = 'KAWARA' ;

thanks

[JuDDer]

I can't see anything there that's causing you the error. Have you tried removing the ; from the end of the statement just in case thats something to do with it?

Everything else looks fine to me so I'm at a loss as well

[sonysg]

after remove ';'.IT WORKS and rock!
ok already!JuDDer!
big thanks

[JuDDer]

We try and help through the forums where possible so if somebody else has the same problem they can read the whole thread to see what was tried and worked/didn't work.

Do all of the column names in your update statement exactly match the column names in your database?

[JuDDer]

Oh it works :)
Great job!

[sonysg]

after remove ';'.IT WORKS
but i make a mistake then i realise it actually didn't work out!

SQLUpdateEdit = SQLUpdateEdit & " WHERE user = '"& Request.querystring("rec") & "'"

response.write SQLUpdateEdit
'conn.execute SQLUpdateEdit

conn.close
SET conn = Nothing
response.Redirect("EditSuccess.asp")

%>

it direct to EditSuccess.asp but the table is not updated
after i remove ' for conn.execute SQLUpdateEdit
the same error message appear again.WEIRD~~

Help needed again.

[JuDDer]

Is the "rec" querystring definitely being sent to this page?

[sonysg]

Judder i do PM your my attachment,hope you can resolve the problem for me.
Thanks

eugene

[sonysg]

hi all,
not sure whether can help me to check the code
i have uploaded to webspace for those experts to tried it out ...
[ymca.sg...]

If happen that anyone can resolve the error ,post the comment over here.

THank in advance

[sonysg]

anyone can help?
still stuck on it.

thanks