Handling Apostrophes in ASP and SQL

An important step when using character data in SQL and ASP is making sure that any apostrophes (') in the data doesn't interfere with your SQL statement! Your SQL statement is going to have apostrophes around each text value. If your text value has an apostrophe IN it, the SQL parser will think it's hit the end of the text value, before it really has.

That is, let's say you have a text value of

'Lisa Shea'

that's nice and simple, and you can put that value into a field. But let's say that instead, the text value is

'Lisa D'Ofronia'

You see the problem? The SQL text parser will get to the D, see the apostrophe after the D, and think the text value is done. It will then think the rest of the line is garbage.

To handle this, you need to turn the apostrophe between the D and O into a double apostrophe (''). That is how SQL knows that this apostrophe goes into the database, and isn't part of its knowing where fields begin and end.

So for both the name and comment, you should be sure to turn any single apostrophes into double apostrophes. You do this with:

So while Lisa Shea would remain Lisa Shea, Lisa D'Ofronia would turn into Lisa D''Ofronia. This may look silly to your eyeballs, but when you use that in a SQL statement, SQL will know to put just ONE apostrophe into the database.