sSQL = "INSERT INTO..... Syntax Error

[UnfitElf]

Hi all...

I am new to ASP and SQL but am trying to great a guest book that uses a database.

I have a form the code is:

<form name="form1" method="post" action="write.asp">
<input type="text" name="user">
<br>
<input type="text" name="date" value="<%= Date %>">
<br>
<textarea name="coments" cols="40" rows="5"></textarea>
<input type="submit" name="BUTTON" value="Submit">
</form>

Now i have the ASP/SQL:

sSQL = "INSERT INTO message (user, coments, date) VALUES ('" & Request("user") & "','" & Request("coments") & "','" & Request("date") & "')"

Set rsExecute = oConn.Execute (sSQL)

The problem is that i get a Syntax error

Syntax Error in INSERT INTO statement

Any help??

Thanks in advance

[andi_g69]

Do you always get the error (which detailed message?) or does it occur occasionally?

Your SQL statement will fail if any of the fields contains an apostrophe. You should escape the values before.
I tend to use a simple VB function to clean:
FUNCTION esc(value)
esc = replace(value,"'","''")
END FUNCTION

sSQL = "INSERT INTO message (user, coments, date) VALUES ('" & esc (Request("user")) & "','" &....

[UnfitElf]

Hey...

I only get the error sometimes. I have created a login page which has a register part that also writes to a database. I used the same SQL but just changed the VALUES and all. Im not too shure what you are doing with that esc thing (my = Bginner with ASP and SQL) But the thing that anoys me most is that it works on some thing then doesn't work on others...

Could you please help me to under stand??

Thanks Heaps

[andi_g69]

The reason why I am using this function:

SQL uses single quote as string delimiter. If any of your values from the HTML form contains a single quote the sql statement is terminated at the first occurence of an extra quote. In this case the DB driver throws an error because it cannot parse the statement.
In order to avoid this problem the function esc replaces all occurences of single quotes IN the data by two single quotes. This is the way to achieve that sql treats the quote as a character and not as a delimiter.

If you are not using a debugger for your ASP code you can place a Response.Write statement in your page to look at the SQL statement, e.g.:

sSQL = "INSERT INTO message (user, coments, date) VALUES ('" & Request("user") & "','" & Request("coments") & "','" & Request("date") & "')"

Response.Write sSQL


Do it once with your original statement and once with the revised version using esc and you will see the difference if you enter a singel quote into your comment field

[UnfitElf]

Hey thanks thats is real helpfull..

Just one more thing how come sometimes it works and other times it dosn't. This is the first tome i have EVER come across code that works then dosn't work. seems very strange to me..

Thanks Heaps

[UnfitElf]

Hi again...

um.. i did an experment and wrote this:

sSQL = "INSERT INTO message (user, coments, date) VALUES('Dan','testing','date')"
Response.Write sSQL

Now from this i get this output:

INSERT INTO message (user, coments, date) VALUES ('Dan','testing','date')

Now the funny thing is that this is an OK SQL statement from my knowledge... BUT when i go to exacute it, it still brings up the error..

so.. hummm

[andi_g69]

How did you define your date field? If it is of type datetime the statement will object to the string 'date' as this is not a valid date format...

This brings me to another point: You do not check the data posted from the form at all. This will not cause any problem on character data. However a date field needs a proper input in data format.
This could explain why your statement does work sometimes but sometimes not.

[UnfitElf]

Yes about that date thing i have set it to text as i thought that could be what was going wrong... BUT it still goes wrong

Any more ideas??

Thanks for your help...

[yasserhy]

try this:

instead of using the regular connection varbiable:

dbPath = Server.MapPath("datasource.mdb")
dbConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
dbConnection.Open dbPath

you can use one of the following:

1. dbPath = Server.MapPath("datasource.mdb")
dbConnection.Provider = "Microsoft Access Driver (*.mdb)"
dbConnection.Open dbPath

2. dbPath = Server.MapPath("datasource.mdb")
dbConnection.ConnectionString= "Driver={Microsoft Access Driver (*.mdb)};DSN=;DBQ=" & dbPath
dbConnection.Open

[Frank]

Do a debug dump of the SQL. Cut and paste the statement and go to the DB query or enterprise manager and execute the statement. All the missing quotes and invalid syntax will show.

Fk