I think this should be simple but please bear with me, its been a long day. 

 I have a multiline text field into which a user can enter some text.  this 

message is then written to a memo field in an Access database.  The problem 

I ran into is that the text is part of an sql insert string with quoted 

value fields separated by commas.  If the user includes a single quote, 

this throws off the sql string paramters and I get a formatting error 

message

cmd = "INSERT INTO tblRequests (DateTime, ...  ..., PriceList, Message) 

VALUES ('" & Now() & "', ...   ...,'" & TheMessage & "')"

Do I convert the single quote which might be in TheMessage to something 

else or is there a 'better' way.

Thanks in advance for the help.



Thomas

tom.esser@u...	

Use the Replace() function and replace all single quotes with two single

quote:



strVar = "O'Brien"

strVar = Replace(strVar, "'", "''")



I suggest that you encapsulate this into a function and call it to replace

single quotes in all your Form fields.



Likewise you need to replace all double quote marks with two double quote

marks.



Cheers

Ken



----- Original Message -----

From: tom.esser

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, June 08, 2000 9:55 PM

Subject: [asp_databases] Writing text string with quotes to Access database





> I think this should be simple but please bear with me, its been a long

day.

>  I have a multiline text field into which a user can enter some text.

this

> message is then written to a memo field in an Access database.  The

problem

> I ran into is that the text is part of an sql insert string with quoted

> value fields separated by commas.  If the user includes a single quote,

> this throws off the sql string paramters and I get a formatting error

> message

> cmd = "INSERT INTO tblRequests (DateTime, ...  ..., PriceList, Message)

> VALUES ('" & Now() & "', ...   ...,'" & TheMessage & "')"

> Do I convert the single quote which might be in TheMessage to something

> else or is there a 'better' way.

> Thanks in advance for the help.

>

> Thomas

>