sonysg
asked on
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.as
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
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.as
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
ASKER
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.as
--------------------------
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.as
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(SQLUpdateE
See hows the query constructed , try it directly on database
which is line 21 ?
paste the query here also
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(SQLUpdateE
See hows the query constructed , try it directly on database
which is line 21 ?
paste the query here also
also make sure that Request.querystring("rec")
ASKER
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
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
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'
I am not able to download from this link
ASKER
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
my email is sonysg@gmail.com
thks
eugene
i guess line 21 is :
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "',
it is Passwd or Password ?
make sure
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "',
it is Passwd or Password ?
make sure
ASKER
My line 21 actually is an update statement
which is
conn.execute SQLUpdateEdit
which is
conn.execute SQLUpdateEdit
ASKER
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~
so i used "passwd" in my table INSTEAD OF "password" so think it wouldn't interfere at all once do the update~
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("")
<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("")
sorry ... your code is correct i oversighted it
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
conn.open ConnectString
%>
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
conn.open ConnectString
%>
ASKER
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
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
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)
and make sure you have given write permissions to the directory where the Access database is and to the windows %temp% directory.(IUSR account)
ASKER
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\pur
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
gawai did you try to test in your IIS(c:\inetpub\wwwroot\pur
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Gawai!!
it works now!!
appreaciate your answer and your effort!!!!!:)
it works now!!
appreaciate your answer and your effort!!!!!:)
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")