Help~ Syntax Error in UPDATE Statement!

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
LVL 3
sonysgAsked:
Who is Participating?
 
GawaiCommented:
try any of the connection string. if still doest not work. let me know
here u can see the diff. diff connection string

http://www.connectionstrings.com/

you are using access 2000 should go for this one :  http://www.connectionstrings.com/?carrier=access

refer this as well.
http://www.aspdev.org/articles/asp-access-connect/
0
 
GawaiCommented:
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") & "'"


0
 
sonysgAuthor 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
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Pratima PharandeCommented:
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

0
 
Pratima PharandeCommented:
also make sure that Request.querystring("rec")  is returning you correct value
0
 
sonysgAuthor 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
0
 
Pratima PharandeCommented:

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'
0
 
Pratima PharandeCommented:
I am not able to download  from this link
0
 
sonysgAuthor 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
0
 
GawaiCommented:
i guess line 21 is :
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "',

 it is Passwd or Password ?
make sure
0
 
sonysgAuthor Commented:
My line 21 actually is an update statement
which is
conn.execute SQLUpdateEdit
0
 
sonysgAuthor 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~
0
 
GawaiCommented:
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("")
0
 
GawaiCommented:
sorry ... your code is correct i oversighted it
0
 
GawaiCommented:
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
%>
0
 
GawaiCommented:
it works now.
for your ref. here is the url
http://www.sahilonline.org/purchase/DisplayUser.asp
0
 
sonysgAuthor 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
0
 
GawaiCommented:
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)
0
 
sonysgAuthor 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
0
 
sonysgAuthor Commented:
thanks Gawai!!
it works now!!
appreaciate your answer and your effort!!!!!:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.