Update sql command errors out if text field has a single quote in it

If you have a a form on your web page that let's the user type in a text value, and they type in a single apostrophe in the text field, and you try to update a database field using that form field, you will get an error message.

In ASP, if you use the replace function after the form posts:


NOTE: you want to replace the single quote with two single quotes so SQL can handle it in an update command.

It's hard to see, so here is the same thing but with a space so you can really see what is happening. But take out the spaces to use it for real:

replace(field," ' "," ' ' '')

Another approach:

replace ' with a special character:



If you are retrieving a text field from the database that has a ' in it, use this replace: