Syntax error in UPDATE statement.

HighFlyer · March 25, 2005, 7:41pm

Hi guys, i’m having this error…

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/alle_new/modifydata.asp, line 14

below is my codes:

<%

username = Request.Form("username")
password = Request.Form("password")
firstName = Request.Form("first")
lastName = Request.Form("last")
emailName = Request.Form("email")
	
str = "UPDATE user_details SET username = " & username & ", password = " & password & ", firstName = " & firstName & ", lastName = " & lastName & ", email = " & email & " WHERE ID = " & id & ""	
id = Request.QueryString("id")
set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("user_db.mdb")
set rs = server.CreateObject ("ADODB.Recordset")
conn.execute str	
rs.Update

set rs=nothing
set conn=nothing

Response.Redirect("testviewusers.asp")

%>

whats wrong with it? please help thanx alot! : o)

largerabbit · March 25, 2005, 9:54pm

Hi there,

I think you need to
a) quote your strings
b) the value of the variable id is assigned after its use

that should fix it.

Cheers,

Paul.

doctorallia · March 25, 2005, 9:56pm

str = “UPDATE user_details SET username = '” & username & “', password = '” & password & “', firstName = '” & firstName & “', lastName = '” & lastName & “', email = '” & email & “’ WHERE ID = '” & id & “'”

try that.. i put in the single quotes around your data

HighFlyer · March 26, 2005, 6:10am

[QUOTE=doctorallia]
str = “UPDATE user_details SET username = '” & username & “', password = '” & password & “', firstName = '” & firstName & “', lastName = '” & lastName & “', email = '” & email & “’ WHERE ID = '” & id & “'”

try that.. i put in the single quotes around your data
[/QUOTE]

Hi, I tried this but same error…

HighFlyer · March 26, 2005, 6:11am

[QUOTE=largerabbit]
Hi there,

I think you need to
a) quote your strings
b) the value of the variable id is assigned after its use

that should fix it.

Cheers,

Paul.
[/QUOTE]

Hi, I’m kinda newbie don’t seems to understand can you please give me some examples? thanx alot

largerabbit · March 26, 2005, 9:44am

The quoted string from doctorallia look perfect, but in your code you need to move the line

id = Request.QueryString(“id”)

above the sql string. Otherwise your value of id in the SQL will always be nothing.

Also, there are soem variables coming from form input and the id is coming from a query string? Is that what you are doing?

If you need more help, post the page that calls this script.

Cheers.

doctorallia · March 26, 2005, 1:38pm

yea, if you are having trouble still.. post both your form input page and the asp page.

SubSpace · March 26, 2005, 4:22pm

[QUOTE=doctorallia]
str = “UPDATE user_details SET username = '” & username & “', password = '” & password & “', firstName = '” & firstName & “', lastName = '” & lastName & “', email = '” & email & “’ WHERE ID = '” & id & “'”
[/QUOTE]

While that code is sufficient to test if the quoting was the only cause of the problem (because it’s definitely syntactically incorrect). However, it’s dangerous to use in a ‘production’ environment because it’s vulnerable to SQL insertion.

You should write a function to properly escape all the string variables in your queries.

HighFlyer · March 27, 2005, 5:28pm

hi guys this are the codes for the form page this page will call for the asp page i posted above. thanx for all your help!

<%
id = Request.QueryString(“id”)
set conn = server.CreateObject (“ADODB.Connection”)
conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & server.MapPath (“user_db.mdb”)
set rs = server.CreateObject (“ADODB.Recordset”)
rs.Open “SELECT * FROM user_details WHERE userID=”& id &“”, conn

%>

Users Modify ">

Modify Account

Username

Password

First Name

Last Name

Email

">

<% set rs=nothing set conn=nothing %>

doctorallia · March 27, 2005, 6:03pm

try using this for your modify data page…

<%

username = Request.Form(“username”)
password = Request.Form(“password”)
firstName = Request.Form(“first”)
lastName = Request.Form(“last”)
emailName = Request.Form(“email”)

id = Request.QueryString(“id”)

str = “UPDATE user_details SET username = '” & username & “', password = '” & password & “', firstName = '” & firstName & “', lastName = '” & lastName & “', email = '” & email & “’ WHERE ID = '” & id & “'”

set conn = server.CreateObject (“ADODB.Connection”)
conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & server.MapPath (“user_db.mdb”)
set rs = server.CreateObject (“ADODB.Recordset”)
conn.execute str
rs.Update

set rs=nothing
set conn=nothing

Response.Redirect(“testviewusers.asp”)

%>

HighFlyer · March 28, 2005, 1:57pm

[QUOTE=doctorallia]
try using this for your modify data page…

<%

username = Request.Form(“username”)
password = Request.Form(“password”)
firstName = Request.Form(“first”)
lastName = Request.Form(“last”)
emailName = Request.Form(“email”)

id = Request.QueryString(“id”)

str = “UPDATE user_details SET username = '” & username & “', password = '” & password & “', firstName = '” & firstName & “', lastName = '” & lastName & “', email = '” & email & “’ WHERE ID = '” & id & “'”

set conn = server.CreateObject (“ADODB.Connection”)
conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & server.MapPath (“user_db.mdb”)
set rs = server.CreateObject (“ADODB.Recordset”)
conn.execute str
rs.Update

set rs=nothing
set conn=nothing

Response.Redirect(“testviewusers.asp”)

%>
[/QUOTE]

Hi again, i’ve tried the code you provide but i have this same error.

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/alle_new/modifydata.asp, line 17

Emagine · March 28, 2005, 2:16pm

is the ID field a number field ?

if so you dont need the ’ ’ .. you just need to add " & & strID & "

doctorallia · March 28, 2005, 4:40pm

[QUOTE=Emagine]
is the ID field a number field ?

if so you dont need the ’ ’ .. you just need to add " & & strID & "
[/QUOTE]

i did that… and then…
AND the REAL reason it isnt working… you use a different variable name for email when you are making the sql string..
look at your original code.. i corrected it below

<%

username = Request.Form(“username”)
password = Request.Form(“password”)
firstName = Request.Form(“first”)
lastName = Request.Form(“last”)
emailName = Request.Form(“email”)

id = Request.QueryString(“id”)

str = “UPDATE user_details SET username = '” & username & “', password = '” & password & “', firstName = '” & firstName & “', lastName = '” & lastName & “', email = '” & emailName & "’ WHERE ID = " & id & “”

set conn = server.CreateObject (“ADODB.Connection”)
conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & server.MapPath (“user_db.mdb”)
set rs = server.CreateObject (“ADODB.Recordset”)
conn.execute str
rs.Update

set rs=nothing
set conn=nothing

Response.Redirect(“testviewusers.asp”)

%>

Emagine · March 28, 2005, 5:24pm

good point i didn’t see the veriable change.

also you will need to check the ID veriable i suggested as it depends if its a number or ot if you require the ‘’

HighFlyer · March 30, 2005, 8:14am

erm… guys do you need my original file? i’ve been trying to solve this problem for weeks but still cant work. thanx alot!

largerabbit · March 30, 2005, 11:57am

Highflyer, the solution to the problem is above in doctorallia’s post.

HighFlyer · March 30, 2005, 5:16pm

[QUOTE=largerabbit]
Highflyer, the solution to the problem is above in doctorallia’s post.
[/QUOTE]

Still the same error…

HighFlyer · March 30, 2005, 5:22pm

my file. 1st page testviewusers.asp → modify.asp → modifydata.asp

sample2.zip (9.36 KB)

doctorallia · March 30, 2005, 6:00pm

okay, working on it .. making progress! i found some errors

doctorallia · March 30, 2005, 6:06pm

got it.. if you want me to explain what i changed let me know.

it was all in the modify data page.. so you can see it if you compare the old and new ones

fixed.zip (9.51 KB)