Apostrophe inclusion for text fields -ASP to SQL 1

[nzgirl]

Hi All...
I have a 'Notes' entry field on my screen and my users will be able to enter: don't forget to brush your teeth

We all know that SQL doesn't like the single apostrophe in don't... but is fine if you do:
Notes = Replace(Notes, ', '') which gives: don''t forget to brush your teeth
now if I Insert this directly using Query Analyzer -its all good..
but if I insert it using:
set cmdObj = Server.CreateObject("ADODB.Command&quot
set cmdObj.ActiveConnection = conObj
cmdObj.CommandText = "INSERT INTO Table1 (Date1, PersonID, Notes) VALUES(?, ?, ?)"
cmdObj.Parameters.Append cmdObj.CreateParameter("Date1", adDate, adParamInput, 8, ScreenDate)
cmdObj.Parameters.Append cmdObj.CreateParameter("PersonID", adInteger, adParamInput, 4, PersonID)
cmdObj.Parameters.Append cmdObj.CreateParameter("Notes", adVarChar, adParamInput, 255, Notes)
cmdObj.execute

it inserts to the database as don''t forget to brush yout teeth...

How can I fix up the above to either be equivalent to insert "don't forget to brush your teeth" or insert 'don''t forget to brush your teeth'

Worse case I'll change it to a straight insert statement -but I've inherited the code and would rather not...

Any advice appreciated

 

[rmusgrove]

I think for that kind of an insert, you don't want to repeat the single quote. Using that technique for an insert eliminates the need for it. The way you're "injecting" columns with the CreateParameter method bypasses any need for single quotes to delimit a text constant. Hence, 1 single quote is correct.

 

[nzgirl]

Thanks for your response....
you are correct...
I found the bug in another statement
select * where Notes = '"& Notes &"' needed to be changed to
select * where Notes = '"& Replace(Notes,"'","''&quot &"'
(5 hrs later...)