Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help~ Syntax Error in UPDATE Statement!

Posted on 2007-10-11
20
Medium Priority
?
241 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
0
Comment
Question by:sonysg
  • 8
  • 8
  • 4
20 Comments
 
LVL 20

Expert Comment

by:Gawai
ID: 20063063
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
 
LVL 3

Author Comment

by:sonysg
ID: 20063095
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20063159
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20063161
also make sure that Request.querystring("rec")  is returning you correct value
0
 
LVL 3

Author Comment

by:sonysg
ID: 20063176
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20063183

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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20063187
I am not able to download  from this link
0
 
LVL 3

Author Comment

by:sonysg
ID: 20063202
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
 
LVL 20

Expert Comment

by:Gawai
ID: 20063492
i guess line 21 is :
SQLUpdateEdit = SQLUpdateEdit & " Passwd='" & strPassword & "',

 it is Passwd or Password ?
make sure
0
 
LVL 3

Author Comment

by:sonysg
ID: 20063519
My line 21 actually is an update statement
which is
conn.execute SQLUpdateEdit
0
 
LVL 3

Author Comment

by:sonysg
ID: 20063525
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
 
LVL 20

Expert Comment

by:Gawai
ID: 20063858
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
 
LVL 20

Expert Comment

by:Gawai
ID: 20063865
sorry ... your code is correct i oversighted it
0
 
LVL 20

Expert Comment

by:Gawai
ID: 20063962
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
 
LVL 20

Expert Comment

by:Gawai
ID: 20063967
it works now.
for your ref. here is the url
http://www.sahilonline.org/purchase/DisplayUser.asp
0
 
LVL 3

Author Comment

by:sonysg
ID: 20064014
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
 
LVL 20

Expert Comment

by:Gawai
ID: 20064271
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
 
LVL 3

Author Comment

by:sonysg
ID: 20064428
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
 
LVL 20

Accepted Solution

by:
Gawai earned 1000 total points
ID: 20065923
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
 
LVL 3

Author Comment

by:sonysg
ID: 20069990
thanks Gawai!!
it works now!!
appreaciate your answer and your effort!!!!!:)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month10 days, 12 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question