Single Quotes give me a syntax error!

[whammy]

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!