Replacing all special characters before inserting into SQL

[Rachel80]

Hi, I have some textarea form fields to allow user to enter any text.. using ASP and MS Access. Thus I need to replace all commas, apostrophes, single-quotes double-quotes and special characters etc with something(perhaps with ASCII values??) in the text variable before i execute the INSERT command or else I will encounter syntax error.

1. May I know the syntax for replacing all the special characters in ASP before inserting?
2. And how to revert back the characters when retrieving the data for display? Have to use ASCII values?

Please help, its very urgent, thanx alot!!

new ASP programmer ^_^

 

[LucyP]

I remember this problem - we struggled with it for weeks.

Firstly you should be adding the contents of the textbox to the database within quotes, so the only special characters you need to worry about are quotes (single quotes that is, or apostrophes) themselves - SQL knows to ignore everything else.

Fantastically, if you replace all 's with a double '', sql will handle everything and will even automatically reconvert the characters when you retrieve the data. So you use a replace:

sql = "INSERT INTO Table (fieldname) VALUES ('" & replace(request.form("textbox&quot,"'","''&quot & "')"

The only problem with this is that the replace will fail if the textbox is empty, so you have to check for this somehow, or write your own function to replace only if the contents of the textbox are not empty. Something like:

textdata = request.form("textbox&quot
if textdata <> &quot;&quot; then textdata = replace(textdata,&quot;'&quot;,&quot;''&quot

and then obviously add textdata to the database.

hope that helps.

 

[MaeJams]

Hi Rachel,

I've used the following SQL statement to successfully input free text (complete with special characters) into an access database:

Assume Field2 has the need to store any special characters.

INSERT INTO Table1 (Field1, Field2, Field3)
SELECT '&quot; & strValue1 & &quot;', &quot; & chr(34) & Server.HTMLEncode(strValue2) & chr(34) & &quot;, '&quot; & strValue3 & &quot;';&quot;

Note there are no single quotes around the value for strValue2. Instead of single quotes you use the ascii for the double quotes and HTML encode the value from your free text field. When displaying the info from the database, just display the field as you normally would and the HTML-encoded values should show up as expected.

If you have any problems or questions, please let me know.

I hope this helps.
Rich

 

[Rachel80]

LucyP,
Wow, your solution is marvellous!! Its really a great help for me, thanx

RJoub,
thanx for your contribution too!

HAPPY EASTER!

Rachel