apostrophe crashes my MySQL database

set rscountrycode = con.execute("select CountryCode from dbo_tp_countryname where CountryName like '%" & country & "%'")


the variable country sometimes has a apostrophe ( ' ) in it, however, when it does it knocks out my MySQL statement and crashes my DB.

Although, i need to have this apostophe in my statement as when i am searching for CountryName of 'Korea (Democratic People's Republic Of)' then it will not return any records due to the apostrophe.

Other than removing all apostrophies from the DB, which i have posted another related question to in the MySQL forum, how can I get around this????

TIA.

Picco

There is a thread on this at the top of the forum.
http://www.codingforums.com/showthread.php?t=9843

set rscountrycode = con.execute("select CountryCode from
dbo_tp_countryname where CountryName like '%" & Replace(country, "'", "''") & "%'")

Fix is in red. Use Replace to subsititue one apostrophe for two apostrophes. In case that is hard to read after country, it's double quote, single quote, double quote, comma, double quote, single quote, single quote and then double quote.

The replace will work if you only have one or two instances, otherwise it is far easier to do what is illustrated in the sticky that Spudhead already linked to and create a function to do it.