ASP Insert single-quotation character error

[new2unix]

Hi,

Ran into a couple problems when I tried to do an "INSERT INTO" to my Access2K database from the Guestbook form ASP. Here is one of them when trying to insert some single quotes character into a database memo field called "COMMENT":

1) When a line in the form field, "txtCOMMENT" contains double-quotation characters or other symbols, the field was inserted into the db okay.

2) When a line contained 2 single-quotation characters consecutively, the database updated correctly but only 1 single-quote character was stored.

3) When a line contained 2 single-quotation characters separated by at least one space, I got the following error about missing operator.

The browser error message is as followed:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''This is the first line 1. This is the 3rd line with 3 spaces. This is the 5th line with symbol: &quot;DOUBLE QUOTE&quot; This is the 6th line with symbol: ~!@#$%^&*()<>{}|?/&quot; This is the 7th line with symbol: ' ' ''.


Thanks for any advice on how to work around this problem.

 

[forty7]

Because single quotes are special characters in SQL (they're used to begin and end a string), you must overload them if you're using them within a string. The way I handle this is to run a little function that takes a string and replaces single quotes with two single quotes. Think of it like wanting to add a double quote in the middle of a VB (or VBScript) string.

 

[new2unix]

Hi forty7,

I thought about using some function to substitute ' with &quot;. But the problem is my form may also be collecting some scripting tips from the visitor and the ' may be used in the text as comment. So just substitute the ' to &quot; will probably not work out when I eventually need to display the record from the db.

Thanks

Mike