I am trying to create a textarea that can be inserted into an Access 2000 DB memo field. I am hoping to set the textarea up with no restrictions on the entries, but am having trouble dealing with single and double quotes ( ' " ).

The following is the information typed into the text area between the []'s:

[

"Double"

'Single'

]

I need to pick up the quotes and the CrLf characters...haven't gotten to the crlf yet, but am having trouble formatting the INSERT command.

I can get the double quotes into the DB with the following format(JavaScript):

DevotionalTextNew = "\"Double\" "

SQL = "INSERT INTO DevotionalData "
+ "(EffDate, Devotion) "
+ "VALUES ('" + EffDate + "', '" + DevotionalTextNew + "')";

but when I try to add the single quotes, I'm not able to get the syntax setup correctly.


DevotionalTextNew = "\"Double\" and \'Single\' "

SQL = "INSERT INTO DevotionalData "
+ "(EffDate, Devotion) "
+ "VALUES ('" + EffDate + "', '" + DevotionalTextNew + "')";

gives error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''"Double" and 'Single' ''.

/Devotional/PostDevotionalToDBSQL.asp, line 93

How do I format a text string with single quotes for insertion into the DB?

Thanks for the help.

__________________
Ego is a faithful friend; He stays with us all the way to the crater.

hoffman,

This is a problem i have suffered with myself. Here is how I handled it.

There are 2 ways to do it

Solution 1. Double charsactors
use "" and '' the problem with this is that the "" is useful only in the VB elements of the script and the '' is only useful in the SQL query parts of the script.
Not very nice but it does work.

Solution 2. prefer option.
Do it the way HTML handles it convert the charactors to something else and convert them back when you want to use them.
use the Replace command

newtxt = Replace(txt,"'","''") 'This works for Jet (Access) SQL
newtxt = Replace(newtxt,"<","&lt;")
newtxt = Replace(newtxt,">","&gt;")
newtxt = Replace(newtxt,"""","&quot;")
newtxt = Replace(newtxt,vbCrlf,"<br>&nbsp;")
newtxt = Replace(newtxt,"\n","<br>&nbsp;")