[Sia]

Hi,
I have a ASP form which is inserted into an Access 2000 DB.
I was testing it and kept on getting this error:

=========================

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''','','','','','','','','3','','','','');'.

/QA_Submit.asp, line 145


======================
I finally figured what the problem is:
If I use the ' simple any where on the fields that are inserted, it will cause the error, cause the sql statment gets messed up. Here is my SQL Statment:

strSQL="INSERT INTO Survey (QAID,Suite,Fname,Lname,Title,Phone,Fax,Email,Comm ent,SatRate,Suggest,DoContact,DoNotify,HowNotify) VALUES

('"&QAID&"','"&Suite&"','"&FName&"','"&LName&"','" &Title&"','"&Phone&"','"&Fax&"','"&Email&"','"&Com ment&"','"&SatRate&"','"&Suggest&"','"&DoContact&" ','"&DoNotify&"','"&HowNotify&"');"


===============

Since I'm using VBscript I need to seperate the values by ', there fore if the ' is typed into any text box it will cause the error.
Do you know how I should fix this?

[wakhy]

replace the quotes or double quotes before constructing the sql query

u can use the folowing functions:

FUNCTION escapeQuotes(str)
DIM str_
IF str="" OR IsEmpty(str) OR IsNull(str) THEN
str_ = ""
ELSE
str_ = Replace(str, chr(34), """)
str_ = Replace(str_, chr(39), "'")
str_ = Replace(str_, vbCr, "<br>")
'str = Replace(str, " ", "&nbsp;")
END IF
escapeQuotes = str_
END FUNCTION

'************************************************* **********
FUNCTION unEscapeQuotes(str)
DIM str_
IF IsEmpty(str) OR IsNull(str) THEN
str_ = ""
ELSE
str_ = Replace(str, "<br>", vbCr)
str_ = Replace(str_, "'", chr(39))
str_ = Replace(str_, "&quot;", chr(34))
END IF
unEscapeQuotes = str_
END FUNCTION

use escapeQuotes before constructing the sql query on the values that must be part of the query and when you retreive the content from the db you can apply the unEscapeQuotes

strSQL="INSERT INTO Survey (QAID,Suite,Fname,Lname,Title,Phone,Fax,Email,Comm
ent,SatRate,Suggest,DoContact,DoNotify,HowNotify) VALUES

('"& escapeQuotes(QAID) &"','"& escapeQuotes(Suite) &"','"& escapeQuotes(FName) &"','"& escapeQuotes(LName) &"','"& escapeQuotes(Title) &"','"&escapeQuotes(Phone)&"','"& escapeQuotes(Fax) &"','"& escapeQuotes(Email) &"','"& escapeQuotes(Comment) &"','"& escapeQuotes(SatRate) &"','"& escapeQuotes(Suggest)&"','"& escapeQuotes(DoContact) &"','"& escapeQuotes(DoNotify) &"','"& escapeQuotes(HowNotify) &"');"


have fun

[Sia]

Thanks

[JonathanB]

You can also use the "addnew" method of the Recordset object. This does not require you to remove " ' " from data you are inserting into your database.