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.

The usual way is to replace one single quote with two (that's '' not ")

The SQL statement requires the single quote ' to denote text. When it encounters the ' that is part of the string I want to store, it interprets it as the end of the string to include. What is the operator for SQL / Access that takes the place of the \ operator in Javascript that causes whatever follows to be treated as part of the string rather than the end of the string?

To repesent a literal single quote mark, you escape it by prepending another single quote mark:

INSERT INTO myTable (myField) VALUES ('This is text with a quote '' in it')

and the text:

This is test with a quote ' in it

will be inserted into your field.

Cheers
Ken

Microsoft MVP - Windows Server (IIS)
www.adOpenStatic.com

Finally understand it.

Thanks for the help

you can use the method 'replace' to replace all instances of ' in the text area eg

replace (stringtoclean, "character(s) to replace", "replacement character(s)")

get the data from the textare assign it to a variable(dirtytxt) and then

cleantxt = replace(dirtytxt, "'", "''")


this will replace all instances of a single ' with a '' so they can be entered in the database
if you need to clean multiple text strings you could write this into a function so you dont need to keep rewriting it

Hope this helps

I tried that first, [ MyString.Replace("'", "''") ] but was only replacing the first incidence of the matching text. I know it SHOULD get all of them, but that was not the experience I was having.

Rich

Try this

<%
txtval = "a'b'c'd"
txtval = replace(txtval, "'", "''")
response.write txtval
%>

the output should be

a''b''c''d

this should work, not too sure about MyString.Replace("'", "''") i seem to get an error about using parentheses when calling a sub


Hope this helps

Try this

if you want store (') to database: replace(str,"'","''")
if you want store (") to database: replace(str,""","""")

Hope this helps

I've gotten it.

Thanks,
Rich