How to insert text include ( " or ' ) to database?

[Kosal]

Hi I had some problem with asp when i insert string include ' or " in the string I cannot insert to database and error message like:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''For centuries China stood as a leading civilization, outpacing the rest of the world in the arts and sciences. But in the 19th and early 20th centuries, China was beset by civil unrest, major famines, military defeats, and foreign occupation. After World Wa'.
/ptmasia/admin/au_country_exe.asp, line 21


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Avant Browser; .NET CLR 2.0.50727)

Page:
POST 771 bytes to /ptmasia/admin/au_country_exe.asp

POST Data:
idregion=1&country=China&information=For+centuries +China+stood+as+a+leading+civilization%2C+outpacin g+the+rest+of+the+world+in+the+arts+and+sciences.+ But+in+the+19th+and+early+20th+centuries%2C+China+ . . .

Please kinldy help me to solve this problem

[jhardman]

OK, I'll help. But do I have to do it kindly?

Please remember that quote marks (") and single quote marks (') are considered special characters in either VBScript or SQL. Since you are using both to script the page and connect to the db, there are several opportunities to create errors unless you replace them with non-special characters. If you need those characters in the string for the purposes of searching or cataloguing, then you can double them, this let's SQL know that you intend the special character to be inserted in the db:

description = replace (request("description"), "'", "''") 
'places two apostrophe's everywhere you have one

On the other hand, if you just want these characters for display purposes, you should "escape them" or replace them entirely with something that will look the same, but not cause errors:

description = replace (request("description"), """, """) 
'replaces quote mark with the HTML character code for quote mark

[guny]

or else, you may use chr(34) to substitute ", chr(39) to substitute ' .
Don't forget to use & before and after the code
For instance, text = I'd love to use "Hello World"
Then
string = "I" & chr(39) & "d love to use " & chr(34) & "Hello World" & chr(34)