whammy
11-15-2002, 12:13 AM
Ok, since this question is asked very frequently, I decided to post this sticky thread.
Many developers new to ASP and SQL find out that they get a syntax error when inserting a record into a database where the user's input (or the variable itself perhaps) contains a single quote.
In SQL, you can keep this from erroring by escaping a single quote with another one., i.e.:
myvar = "Bob's Diner"
SQL = "INSERT INTO tablename (myvar) VALUES ('" & myvar & "')"
Set rs = Conn.Execute(SQL)
Will throw an error, since there is a single quote in the variable.
The easiest way to solve this is to just comment out the quote with another:
myvar = "Bob's Diner"
SQL = "INSERT INTO tablename (myvar) VALUES ('" & Replace(myvar,"'","''") & "')"
Set rs = Conn.Execute(SQL)
However, as you can see, that can get really messy if you're using a lot of variable that can potentially contain a single quote. In order to get around this, just create a function, and ONLY use it when inside a SQL statement (so you don't end up with multiplying single quotes in your data). i.e.:
Function CSQ(byVal str)
If IsNull(str) Then str = ""
CSQ = Replace(str,"'","''")
End Function
myvar = "Bob's Diner"
SQL = "INSERT INTO tablename (myvar, myvar2) VALUES ('" & CSQ(myvar) & "','" & CSQ(myvar2) & "')"
Set rs = Conn.Execute(SQL)
In the above code, "CSQ" stands for "Comment Single Quotes"... but you can name the function whatever suits your fancy.
Hope this helps!
:)
Many developers new to ASP and SQL find out that they get a syntax error when inserting a record into a database where the user's input (or the variable itself perhaps) contains a single quote.
In SQL, you can keep this from erroring by escaping a single quote with another one., i.e.:
myvar = "Bob's Diner"
SQL = "INSERT INTO tablename (myvar) VALUES ('" & myvar & "')"
Set rs = Conn.Execute(SQL)
Will throw an error, since there is a single quote in the variable.
The easiest way to solve this is to just comment out the quote with another:
myvar = "Bob's Diner"
SQL = "INSERT INTO tablename (myvar) VALUES ('" & Replace(myvar,"'","''") & "')"
Set rs = Conn.Execute(SQL)
However, as you can see, that can get really messy if you're using a lot of variable that can potentially contain a single quote. In order to get around this, just create a function, and ONLY use it when inside a SQL statement (so you don't end up with multiplying single quotes in your data). i.e.:
Function CSQ(byVal str)
If IsNull(str) Then str = ""
CSQ = Replace(str,"'","''")
End Function
myvar = "Bob's Diner"
SQL = "INSERT INTO tablename (myvar, myvar2) VALUES ('" & CSQ(myvar) & "','" & CSQ(myvar2) & "')"
Set rs = Conn.Execute(SQL)
In the above code, "CSQ" stands for "Comment Single Quotes"... but you can name the function whatever suits your fancy.
Hope this helps!
:)