Syntax error in UPDATE statement.

HighFlyer · Mar 25, 2005

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 · Mar 25, 2005

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 · Mar 25, 2005

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 · Mar 26, 2005

doctorallia said:
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

Hi, I tried this but same error...

HighFlyer · Mar 26, 2005

largerabbit said:
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.

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

largerabbit · Mar 26, 2005

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 · Mar 26, 2005

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

SubSpace · Mar 26, 2005

doctorallia said:
str = "UPDATE user_details SET username = '" & username & "', password = '" & password & "', firstName = '" & firstName & "', lastName = '" & lastName & "', email = '" & email & "' WHERE ID = '" & id & "'"

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 · Mar 27, 2005

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

%>
<html>

<head>

<title>Users Modify</title>
</head>

<body>

<form method="POST" action="modifydata.asp?id=<%= rs.Fields("userID").Value %>">
<p>Modify Account</p>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; text-align: center" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="20%">Username</td>
<td width="20%">Password</td>
<td width="20%">First Name</td>
<td width="20%">Last Name</td>
<td width="20%">Email</td>
</tr>
<tr>
<td width="20%">
<input type="text" name="username" size="20" value="<%= rs.Fields("username").Value %>"></td>
<td width="20%">
<input type="text" name="password" size="20" value="<%= rs.Fields("password").Value %>"></td>
<td width="20%">
<input type="text" name="first" size="20" value="<%= rs.Fields("firstName").Value %>"></td>
<td width="20%">
<input type="text" name="last" size="20" value="<%= rs.Fields("lastName").Value %>"></td>
<td width="20%">
<input type="text" name="email" size="20" value="<%= rs.Fields("email").Value %>"></td>
</tr>
<tr>
<td width="100%" colspan="5">
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="46%" id="AutoNumber2">
<tr>
<td width="50%"><input type="submit" value="Submit" name="B1"></td>
<td width="50%"><input type="reset" value="Reset" name="B2"></td>
</tr>
</table>
</td>
</tr>
</table>
</form>

</body>

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

doctorallia · Mar 27, 2005

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 · Mar 28, 2005

doctorallia said:
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")

%>

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 · Mar 28, 2005

is the ID field a number field ?

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

doctorallia · Mar 28, 2005

Emagine said:
is the ID field a number field ?

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

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 · Mar 28, 2005

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 · Mar 30, 2005

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 · Mar 30, 2005

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

HighFlyer · Mar 30, 2005

largerabbit said:
Highflyer, the solution to the problem is above in doctorallia's post.

Still the same error....

HighFlyer · Mar 30, 2005

my file. 1st page testviewusers.asp -> modify.asp -> modifydata.asp

doctorallia · Mar 30, 2005

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

doctorallia · Mar 30, 2005

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

Syntax error in UPDATE statement.

Guppy

Perch

Perch

Guppy

Guppy

Perch

Perch

Bass

Guppy

Perch

Guppy

Perch

Perch

Perch

Guppy

Perch

Guppy

Guppy

Attachments

Perch

Perch

Attachments