Microsoft SQL Server
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > Microsoft SQL Server
Reload this Page

Syntax error in UPDATE statement.

Discuss Syntax error in UPDATE statement. in the Microsoft SQL Server forum on Dev Articles.
Syntax error in UPDATE statement. Microsoft SQL Server forum discussing this extremely popular DBMS for the Windows operating system. Learn to optimize your SQL Server database for optimum application performance.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old July 9th, 2004, 05:44 AM
Sharmishtha Sharmishtha is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: India
Posts: 6 Sharmishtha User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 59 sec
Reputation Power: 0
Unhappy Syntax error in UPDATE statement.
hi!
I m trying to update the data in Ms Access Database
This is my code:

<html>
<%
set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("ApplicantDetails.mdb")
conn.Open DSNtemp, SQLstmt
SQLstmt = "Update Applicant SET "
SQLstmt = SQLstmt & "UserName='" & TRIM(Request.Form("uname")) & "', "
SQLstmt = SQLstmt & "Password='" & TRIM(Request.Form("pass")) & "', "
SQLstmt = SQLstmt & "FirstName='" & TRIM(Request.Form("fst")) & "', "
SQLstmt = SQLstmt & "LastName='" & TRIM(Request.Form("lst")) & "', "
SQLstmt = SQLstmt & "Address='" & TRIM(Request.Form("add"))& "', "
SQLstmt = SQLstmt & " MobileNo='" & TRIM(Request.Form("mob"))& "', "
SQLstmt = SQLstmt & " Email='" & TRIM(Request.Form("email"))& "', "
SQLstmt = SQLstmt & " Occupation='" & TRIM(Request.Form("occ"))& "', "
SQLstmt = SQLstmt & " WHERE IdNo='" & TRIM(Request.Form("id"))& "',"
conn.Execute(SQLstmt)------>Error
Response.Write "Your account Information has modified successfuly"
%>
</html>

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Please help me to solve the error.
Reply With Quote
  #2  
Old July 9th, 2004, 07:42 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
  
Join Date: Aug 2002
Location: Sydney, AU
Posts: 2,058 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 8 m 57 sec
Reputation Power: 9
Send a message via ICQ to stumpy Send a message via MSN to stumpy
Remove the red commas
Code:
SQLstmt = SQLstmt & " Occupation='" & TRIM(Request.Form("occ"))& "', "
SQLstmt = SQLstmt & " WHERE IdNo='" & TRIM(Request.Form("id"))& "',"
__________________
DevArticles Moderator
BlueSix - Web Development and Consulting
Reply With Quote
  #3  
Old July 12th, 2004, 02:27 AM
Sharmishtha Sharmishtha is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: India
Posts: 6 Sharmishtha User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 59 sec
Reputation Power: 0
Angry Still getting Syntax error in UPDATE statement
If I removed the red commas then my code is as follows:

<html>
<%
set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("ApplicantDetails.mdb")
conn.Open DSNtemp, SQLstmt
SQLstmt = "Update Applicant SET "
SQLstmt = SQLstmt & "UserName='" & TRIM(Request.Form("uname")) & "'"
SQLstmt = SQLstmt & "Password='" & TRIM(Request.Form("pass")) & "'"
SQLstmt = SQLstmt & "FirstName='" & TRIM(Request.Form("fst")) & "'"
SQLstmt = SQLstmt & "LastName='" & TRIM(Request.Form("lst")) & "'"
SQLstmt = SQLstmt & "Address='" & TRIM(Request.Form("add"))& "'"
SQLstmt = SQLstmt & " MobileNo='" & TRIM(Request.Form("mob"))& "',"
SQLstmt = SQLstmt & " Email='" & TRIM(Request.Form("email"))& "'"
SQLstmt = SQLstmt & " Occupation='" & TRIM(Request.Form("occ"))& "'"
SQLstmt = SQLstmt & " WHERE IdNo='" & TRIM(Request.Form("id"))& "'"
conn.Execute(SQLstmt)
Response.Write "Your account Information has modified successfuly"
%>
</html>

But still i m getting ERROR as:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''dae2'Password='abcdef'FirstName='dsfa'LastName=' fsdf'Address='fdsfs' MobileNo='57877''.
Reply With Quote
  #4  
Old July 12th, 2004, 04:41 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
  
Join Date: Aug 2002
Location: Sydney, AU
Posts: 2,058 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 8 m 57 sec
Reputation Power: 9
Send a message via ICQ to stumpy Send a message via MSN to stumpy
You only need to remove the exact ones I specified, not all of them.
Reply With Quote
  #5  
Old July 13th, 2004, 02:18 AM
Sharmishtha Sharmishtha is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: India
Posts: 6 Sharmishtha User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 59 sec
Reputation Power: 0
Thumbs down But still it giving me error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Reply With Quote
  #6  
Old July 13th, 2004, 03:06 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
  
Join Date: Aug 2002
Location: Sydney, AU
Posts: 2,058 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 8 m 57 sec
Reputation Power: 9
Send a message via ICQ to stumpy Send a message via MSN to stumpy
We'll need to see the query
Reply With Quote
  #7  
Old July 13th, 2004, 06:20 AM
Sharmishtha Sharmishtha is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: India
Posts: 6 Sharmishtha User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 59 sec
Reputation Power: 0
Cool I don't found any error in this query.
My code is as follows:
<html>
<%
set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("ApplicantDetails.mdb")
conn.Open DSNtemp, SQLstmt
SQLstmt = "Update Applicant SET "
SQLstmt = SQLstmt & "UserName='" & TRIM(Request.Form("uname")) & "',"
SQLstmt = SQLstmt & "Password='" & TRIM(Request.Form("pass")) & "',"
SQLstmt = SQLstmt & "FirstName='" & TRIM(Request.Form("fst")) & "',"
SQLstmt = SQLstmt & "LastName='" & TRIM(Request.Form("lst")) & "',"
SQLstmt = SQLstmt & "Address='" & TRIM(Request.Form("add"))& "',"
SQLstmt = SQLstmt & " MobileNo='" & TRIM(Request.Form("mob"))& "',"
SQLstmt = SQLstmt & " Email='" & TRIM(Request.Form("email"))& "',"
SQLstmt = SQLstmt & " Occupation='" & TRIM(Request.Form("occ"))& "'"
SQLstmt = SQLstmt & " WHERE IdNo='" & TRIM(Request.Form("id"))& "'"
conn.Execute(SQLstmt)
Response.Write "Your account Information has modified successfuly"
%>
</html>

If anyone knows whts wrong with it please help me to solve it.
Its urgent!
Last edited by Sharmishtha : July 13th, 2004 at 08:27 AM. Reason: to change some text
Reply With Quote
  #8  
Old July 13th, 2004, 09:38 AM
stumpy's Avatar
stumpy stumpy is offline
May contain nuts.
Dev Articles Regular (2000 - 2499 posts)
  
Join Date: Aug 2002
Location: Sydney, AU
Posts: 2,058 stumpy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 8 m 57 sec
Reputation Power: 9
Send a message via ICQ to stumpy Send a message via MSN to stumpy
The first thing you should always do when debugging error messages is to print the string to screen that's causing you problems... in this case, your SQL string...so, before you execute the statement:
Code:
response.write(SQLstmt)
conn.Execute(SQLstmt)
Reply With Quote
  #9  
Old July 13th, 2004, 10:12 AM
EiSa EiSa is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Sep 2002
Location: Norway
Posts: 184 EiSa User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 22 m 24 sec
Reputation Power: 7
Are some of your field numeric and not text? If so, no single quotes should be used on those.

What happens if you try this?
SQLstmt = SQLstmt & " WHERE IdNo=" & TRIM(Request.Form("id"))

If it doesn't work, do a response.write as stumpy say.
__________________
Strictly CSS | Poker Cognac XO
Reply With Quote
  #10  
Old July 14th, 2004, 05:16 AM
Sharmishtha Sharmishtha is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: India
Posts: 6 Sharmishtha User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 59 sec
Reputation Power: 0
Wink Thanx a lot!
Hi!
Thanx to stumpy & EiSa. My code is working now!
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Syntax error in UPDATE statement.

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway. 
Stay green...Green IT