How do I escape apostrophes in textareas?

[KellyHero]

Hi all:

I recently converted an Access db to an SQL db and now I'm getting the following errors when trying to update a record:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-4.0.13-nt]You have an error in your SQL
syntax. Check the manual that corresponds to your MySQL server version
for the right syntax to use near 's delight. All of the <A
href=&quot;

http://www.homes-on-tour.com/upl

/file.asp, line 153

Line 153 reads:

Conn.Execute(&quot;UPDATE communities SET co_tourinfo='&quot; & Replace(co_tourinfo, &quot;'&quot;, &quot;''&quot & &quot;' WHERE co_num=&quot; & co_num)

I looked in the database and see that the text is being stored with an &quot;\&quot; whereever there is an apostrophe. I'm not sure if this is a result of the db conversion or if I need to fix my INSERT or UPDATE statements.

Please help.

 

[kevpho]

Hi Kelly,

I am not familiar with MySQL db but the UPDATE statement appears to be right. Do you mean that entries in the database had &quot;\&quot; instead of apostrophes when you converted the database, or when you run the page and update an entry the apostrophes in the entry are changed to &quot;\&quot;. If it's the second case that's odd because no update should be taking place if you're getting the error. If it's the first case then it's probably because of the db conversion. Anyhow, I'm new to ASP and SQL so I'm not totally sure. But, your UPDATE statement should be fine the way it is. Hope you find the error.

Kevin

 

[KellyHero]

Sorry, I didn't make it very clear what's happening. When I look at the data in the SQL database, there is an &quot;\&quot; in front of every apostrophe. For example, if the text contains the word chef's, it looks like chef\'s in the database. When the text is retrieved from the db into the form for editing, it contains &quot;\&quot;s. Trying to update gives the error mentioned above. Once the &quot;\&quot;s are removed from the text input area, the record can be updated.

Again, I'm not sure if it's happening because of the conversion from Access to SQL, in which case I could just edit the records in the db, or if I need to change either the code that inserts the record in the first place or the code that updates the record.

 

[KellyHero]

Hi again:

I ran a test for this error by creating a new record containing apostrophes in the text. Then I checked the db and the &quot;\&quot;s are not showing up. I then pulled the new record up for editing and the &quot;\&quot;s did not show up there either. I guess the problem was created by the conversion to the SQL database, and going in and manually removing the &quot;\&quot;s will solve it.

 

[kevpho]

Ya I suppose the db conversion screwed things up a little bit. Hopefully you don't have too many apostrophes to change ... but if you have a large number of records that need changing perhaps you could run an UPDATE statement looking for &quot;\'&quot; and replacing it with &quot;'&quot; ? just an idea, anyhow. Manually doing it will probably be faster if it's a small db. Glad you found the root of the problem.