Closed Thread Icon

Topic awaiting preservation: sql statement error Pages that link to <a href="https://ozoneasylum.com/backlink?for=12941" title="Pages that link to Topic awaiting preservation: sql statement error" rel="nofollow" >Topic awaiting preservation: sql statement error\

 
Author Thread
Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-17-2003 06:24

Hi, guys. Error messages says:

Syntax error in UPDATE statement.

code:
ID = Request.Form("ID")
sql = "UPDATE tblWine SET name='" & Request.Form("name") & "',"
sql = sql & "price='" & Request.Form("price") & "',"
sql = sql & "' WHERE tblWine.[ID]=" & ID



Is my code wrong??? Hmm....Pretty confusing to write multiple lines sql statement using VBscript.
Thanks.

Hiroki Kozai

[This message has been edited by Hiroki (edited 10-17-2003).]

Rhino
Bipolar (III) Inmate

From: New Jersey, USA
Insane since: Jul 2003

posted posted 10-17-2003 09:54

You included an extra comma at the end of your second line which would cause an error. This is the way that it should look

sql = "UPDATE tblWine SET name='" & Request.Form("name") & "',"
sql = sql & "price='" & Request.Form("price") & "'"
sql = sql & "' WHERE tblWine.[ID]=" & ID

Notice that after the Update of Price that I removed the comman.

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-20-2003 00:09

Hi, mate. thanks for your reply.
well, still not working yet, I am afraid.
I've found and changed another line as well.
Now it is look like:

code:
ID = Request.Form("ID")
sql = "UPDATE tblWine SET name='" & Request.Form("name") & "'"
sql = sql & "price='" & Request.Form("price") & "'"
sql = sql & "' WHERE tblWine.[ID]=" & ID



Hmm........Is anything wrong????

Hiroki Kozai

Ramasax
Paranoid (IV) Inmate

From: PA, US
Insane since: Feb 2002

posted posted 10-20-2003 00:26

This should work.... I think :

code:
sql = "UPDATE tblWine SET name =" & Request.Form("name") & ", price =" & Request.Form("price") & "WHERE ID=" & ID



Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-20-2003 01:31

hi, mate. Many thanks for your reply.
I tried it. But I got this error message;

quote:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression '52WHERE ID=18'.
/hiroki/3_ADO_ASP/wine_db_edit.asp, line 28



Hmm.....Syntax error....

Hiroki Kozai

Ramasax
Paranoid (IV) Inmate

From: PA, US
Insane since: Feb 2002

posted posted 10-20-2003 03:49

woops, put a space between the quotes and WHERE. That should fix it, I tested it out on my server.

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-20-2003 05:58

Hi, mate. Thanks a lot for your replies.
Not yet working. Sorry.....
I deleted space like you told me.
Here is my code:

code:
sql = "UPDATE tblWine SET name="&Request.Form("name")&",price="&Request.Form("price")&"WHERE tblWine.[ID]="&ID



Is this still wrong????

Hiroki Kozai

Rhino
Bipolar (III) Inmate

From: New Jersey, USA
Insane since: Jul 2003

posted posted 10-20-2003 09:48

sql = "UPDATE tblWine SET name=" & Request.Form("name") & ",price=" & Request.Form("price") &" WHERE tblWine.[ID]=" & ID

Ramasax was saying that you should put a space right before the "WHERE tblWine.[ID]=" so it looks like " WHERE tblWine.[ID]=" so the statements don't run together. The only other thing that I would look out for is if any of the values you are inserting are strings. Wouldn't they have to be enclosed in single quotes before being inserted ?


Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-21-2003 00:09

Hi, mate. Thanks for your replies. sorry about my siliness.
Yeah, I put space there but still doesn't work.
Then I changed like this:

code:
Sub Update_Record()

ID = Request.Form("ID")

sql="UPDATE tblWine SET "
sql=sql & "Name='" & Request.Form("Name") & "',"
sql=sql & "Price='" & Request.Form("Price") & "',"
sql=sql & " WHERE tbleWine.[ID]='" & ID & "'"

conn.Execute sql, RecordsAffected
conn.Close

Response.Write("<html><script>window.opener.do_Refresh();window.close();</script></html>")
Response.End

End Sub



Actually this SQL code come from example code from w3schools.com.
I guess it is fine. But just doesn't work.
Always say syntax error on line 24.
Line 24 is:

conn.Execute sql, RecordsAffected

Pretty hard to understand.....
Help

Hiroki Kozai

Rhino
Bipolar (III) Inmate

From: New Jersey, USA
Insane since: Jul 2003

posted posted 10-21-2003 12:21

I am thinking the error message refers to the bad SQL Sytax. You have incorporated the space before the WHERE clause, but you still have the comma after the price update statement. Let me see if I can put in what everyone has said.

Sub Update_Record()
ID = Request.Form("ID")

sql="UPDATE tblWine SET "
sql=sql & "Name='" & Request.Form("Name") & "',"
sql=sql & "Price='" & Request.Form("Price") & "'"
sql=sql & " WHERE tbleWine.[ID]='" & ID & "'"

conn.Execute sql, RecordsAffected
conn.Close

Response.Write("<html><script>window.opener.do_Refresh();window.close();</script></html>")
Response.End
End Sub


Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 10-21-2003 22:59

hi,mate. Thanks for your words. I really appreciate that.
Finally I made it work. Feeling fantastic about it.
Here is my code:

code:
sql="UPDATE tblWine SET "
sql=sql & "Name='" & Request.Form("Name") & "',"
sql=sql & "Price='" & Request.Form("Price") & "'"
sql=sql & " WHERE tblWine.[ID]=" & ID



By the way, are you using PHP as well for your backend stuff?

Hiroki Kozai

« BackwardsOnwards »

Show Forum Drop Down Menu