We help IT Professionals succeed at work.

Help~ Syntax Error in UPDATE Statement!

sonysg
sonysg asked
on
298 Views
Last Modified: 2008-01-09
hi all,
i'm 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,EditUser.asp), 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.,please note that "rec" is the variable that i pass thru from view form.

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.querystring("rec") & "'"
---------------------------------------------------------------------- ----------------------------------------------------------------

Not sure whether can help me to check the code
I have uploaded to webspace for those experts to tried it out ...

http://m.domaindlx.com/sonysg/purchase.zip.html

please rename the purchase.zip.html to zip file ,inside have the database (access mdb) and the DisplayUser.asp(view the user table) ,EditUser.asp(this one get error)

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

Thank in advance

eugene
Comment
Watch Question

Commented:
seems "user" is Id Field that you updating. Dont update that field
now u try this code
SQLUpdateEdit = "UPDATE usertbl SET"
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "', "
SQLUpdateEdit = SQLUpdateEdit & " ARight='" & strAright & "', "
SQLUpdateEdit = SQLUpdateEdit & " DeptCode='" & strDeptCode & "', "
SQLUpdateEdit = SQLUpdateEdit & " Email='" & strEmail & "'"
SQLUpdateEdit = SQLUpdateEdit & " WHERE user = '"& Request.querystring("rec") & "'"


Author

Commented:
hi gawai, after removing the "user",i get an error.

----------------------------
Error Type:
Microsoft JET Database Engine (0x80040E57)
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
/purchase/UpdateUserRec.asp, line 22
CERTIFIED EXPERT

Commented:
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.querystring("rec") & "'"


after this try
Reasponse.write(SQLUpdateEdit)

See hows the query constructed , try it directly on database

which is line 21 ?

paste the query here also

CERTIFIED EXPERT

Commented:
also make sure that Request.querystring("rec")  is returning you correct value

Author

Commented:
hi it id pass the correct value

this is the query result after i executed
UPDATE usertbl SET user='ADELINEK', Passwd='akoh13', ARight='USER', DeptCode='BHS', Email='adelinek@tymca.sg' WHERE user = 'ADELINEK'

pratima_mcs, can you try to download this file
http://m.domaindlx.com/sonysg/purchase.zip.html
rename .html to .zip will extract out the coding & database

thks
eugene
CERTIFIED EXPERT

Commented:

have you tried it in database directly

UPDATE usertbl SET user='ADELINEK', Passwd='akoh13', ARight='USER', DeptCode='BHS', Email='adelinek@tymca.sg' WHERE user = 'ADELINEK'
CERTIFIED EXPERT

Commented:
I am not able to download  from this link

Author

Commented:
hi,if possible can you send me an e-mail ,i will send back the zip file to you to try it out.
my email is sonysg@gmail.com

thks
eugene

Commented:
i guess line 21 is :
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "',

 it is Passwd or Password ?
make sure

Author

Commented:
My line 21 actually is an update statement
which is
conn.execute SQLUpdateEdit

Author

Commented:
i know "password" is actually a reserved word in SQL
so i used "passwd" in my table INSTEAD OF "password" so think it wouldn't interfere at all once do the update~

Commented:
u need to change form method to get it work
<form name="Form" method="post" ...........
to
<form name="Form" method="get"

if you are using GET  u need to use Request.QueryString("")
and  of POST method use Request.Form("")

Commented:
sorry ... your code is correct i oversighted it

Commented:
well it was the problem of connecting string
i changed to

Dim conn, ConnectString

ConnectString = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=;"
ConnectString = ConnectString & "DBQ=c:\sites\purchase.mdb"

Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnectString
%>

Commented:
it works now.
for your ref. here is the url
http://www.sahilonline.org/purchase/DisplayUser.asp

Author

Commented:
Hi, i get this error after modify the connection.asp
i running under my IIS directory,which is INETPUB/purchase directory so i changed it to
"DBQ=purchase.mdb" should it be correct?My dsn connection is set to purchase.mdb anything i need to declare to make your coding work on my server?
This is the error i getting..


Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x484 Thread 0xb4c DBC 0x2000b0c Jet'.
/purchase/connection.asp, line 17

Commented:
http://www.aspisfun.com/errors/volatile.html

and make sure you have given write permissions to the directory where the Access database is and to the windows %temp% directory.(IUSR account)

Author

Commented:
the writing permissions assign already on the folder,my other adding module(add user) can write the data inside the table, i know these code can work in web server but unable work in IIS?
gawai did you try to test in your IIS(c:\inetpub\wwwroot\purchase),something like that, does it wok?
i need to make it work as  the database is hosted inside our intranet not on internet,really appreciate your help on testing on your internet hosting web space:)
could you modify the code to suit on intranet?

rgds
eugene
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
thanks Gawai!!
it works now!!
appreaciate your answer and your effort!!!!!:)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.