Hi Jeremy,
"I don't want to 'miss' you. OR I would like's to have Beer's"
What about a string like the above. See the issues is I want the single quotes to remain where they are but in my SQL statement it reads the ' mark as an end tag to the SQL statement. This is incorrect so therefor it throws thy error.
Does that make sense?
yes it does, it is a common problem as a single quote often appears in strings as you have shown. CF is designed to automatically escape such quotes in a string entered as data in a CF query. IE you don't have to do a thing, CF will handle it. eg, this will work:
<cfset thisString = "I don't want to 'miss' you.">
UPDATE myTable set txt = '#thisString#' where blah
that will be automagically translated by CF into:
UPDATE myTable set txt = 'I don''t want to ''miss'' you.' where blah
so it will work fine.
The PreserveSingleQuotes() function is for when you do want the single quotes to stay as such in the string and not get escaped. This might be when you use a CF variable in an IN clause or similar, eg:
<cfset ListOfNames = "'fred','joe','bert'">
SELECT something WHERE name IN (#PreserveSingleQuotes(ListOfNames)#)
in such a case you want the single quotes to stay as single quotes because the end result that you want is:
SELECT something
WHERE name IN ('fred','joe','bert')Note, the PreserveSingleQuotes() function only works on simple variables so if you are getting the string from an array or structure then you need to make it "simple" first by using a cfset, eg:
<cfset thisString = ArrayofQuotes[2]>
HTH
Kym (one of those rare folk not at MXDU <g>)
--- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] Aussie Macromedia Developers: http://lists.daemon.com.au/
