FAQ #2: A user attempted to edit or add data to the database that had a single quote in the name (for example Bill's Fish Shop or O'Reilly). Now the form gives an error when adding or updating.

This is a very common error message when updating or adding to databases. Let us look at 3 SQL statements:

UPDATE customer FIELDS (Name,City,State,Zip) _
    VALUES ('Acme Inc.','Rockville','MD','20849') _
    WHERE custid=20
works fine!

UPDATE customer FIELDS (Name,City,State,Zip) _
    VALUES ('Acme's Store','Rockville','MD','20849') _
    WHERE custid=20
will fail because it the single ' confuses the SQL parser.

UPDATE customer FIELDS (Name,City,State,Zip) _
    VALUES ('Acme''s Store','Rockville','MD','20849') _
    WHERE custid=20
will succeed because it the single ' was entered as '' which satisfies the SQL parser.

UPDATE customer FIELDS (Name,City,State,Zip) _
    VALUES ('Ledos','Pike's Peak','CO','000000') _
    WHERE custid=20
will fail because it the single ' confuses the SQL parser.

UPDATE customer FIELDS (Name,City,State,Zip) _
    VALUES ('Ledos','Pike''s Peak','CO','000000') _
    WHERE custid=20
will succeed because it the single ' was entered as '' which satisfies the SQL parser.

In your code you may be building your SQL statement from variables, i.e.

co=request("company")
cy=request("city")
st=request("state")
id=request("keycust")
mySQL = "UPDATE customer FIELDS ("
mySQL = MySQL & "Name,City,State,Zip) "
mySQL = MySQL & "VALUES ('" & co & "',"
mySQL = MySQL & "'" & cy & "',"
mySQL = MySQL & "'" & st & "',"
mySQL = MySQL & "'" & zip & "'"
mySQL = MySQL & "WHERE keycust=" & id

and this will work fine as long as the user never enter an ' in the data. But to be robust, you should use a built-in function called replace that can find characters in a string and replace them with alternate characters. For example the code above could be replaced with:

co=request("company")
cy=request("city")
st=request("state")
id=request("keycust")
co=replace(co,"'","''")
cy=replace(cy,"'","''")
mySQL = "UPDATE customer FIELDS ("
mySQL = MySQL & "Name,City,State,Zip) "
mySQL = MySQL & "VALUES ('" & co & "',"
mySQL = MySQL & "'" & cy & "',"
mySQL = MySQL & "'" & st & "',"
mySQL = MySQL & "'" & zip & "'"
mySQL = MySQL & "WHERE keycust=" & id

and it would work even if the user input ' in the company or city field because they would be doubled up and acceptable to the SQL parser.

Additional Information:

Check MS knowledgebase article Q178070 (HOWTO : Handle Quotes and Pipes in Concatenated SQL Literals) which suggests all pipe characters be replaced with chr(124)

The article can be found at http://support.microsoft.com/support/kb/articles/q178/0/70.asp

There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.