See this script in action: click here
About two months ago, I got done writing a large application used to look up listings
for different towns. The thing was, I never actually thought of the fact that some towns
have " ' " (quotes) in their names. This was not
good, considering the site was live, and I had an error. For example:
I would have a town, and its name would be: John's Town (not real). What ended up
happening was when the query string was created, the single quote was added to the string
and it looked like this:
"SELECT * FROM table WHERE town='John's Town';"
An error will occur when you execute this SQL statement, saying unexpected ending.
I needed to solve this quick, and quick I did. I used the following function to search
through the string and put an extra quote in where needed.
For i = 1 to len(strStmt)
If Mid( strStmt, i, 1 ) = "'" Then
strOut = strOut & "'"
strOut = strOut & Mid(strStmt, i,1)
convertToSQL = strOut
In an SQL statement you do not need to enclose the value in quotes if it is a number.
However, when you are using a string, you need to enclose the value.
Dim town = "John's Town"
strQuery = "SELECT * FROM table WHERE town='" & convertToSQL(town)
The resulting string would be as followed.
"SELECT * FROM table WHERe town='John''s Town'"