Preserving Single Quotes

Report · Sep 27, 2006

I'm using the following code to produce a list of zipcodes for a radius search:
<cfset Caller.passedreturneddata = Caller.passedreturneddata & "'#mystruct.zipcode#' or ">
The result is like '12345' or '23456' or '34567'.

CF is turning the single quotes into double quotes and giving SQL error below. How can I preserve the single quotes?

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'fee = No And Category = Horoscope And Subcategory = Horoscope3 And ZipCode = ''80212'' or ''80212'' or ''80212'' or ''80034'' or ''80214'' or ''80214'' or ''80214'' or ''80214'' or ''80211'' or ''80033'' or ''80033'' or ''80033'' or ''80001'' or ''80002'' or'.

The error occurred in eventsradius.cfm: line 8

6 : And Category = #form.category#
7 : And Subcategory = #form.subcategory#
8 : And ZipCode = #form.zipcode#
9 : Order by date, time
10 : </cfquery>

--------------------------------------------------------------------------------

SQL Select * From Calendar2 Where fee = No And Category = Horoscope And Subcategory = Horoscope3 And ZipCode = ''80212'' or ''80212'' or ''80212'' or ''80034'' or ''80214'' or ''80214'' or ''80214'' or ''80214'' or ''80211'' or ''80033'' or ''80033'' or ''80033'' or ''80001'' or ''80002'' or ''80002'' or ''80002'' or ''80204'' or ''80295'' or ''80257'' or ''80266'' or ''80266'' or ''80248'' or ''80217'' or ''80265'' or ''80292'' or ''80215'' or ''80215'' or ''80215'' or ''80255'' or Order by date, time
DATASOURCE normmy_denver
VENDORERRORCODE -3100
SQLSTATE 42000

Thanks!
David

Report · Sep 27, 2006

Try something like this. #Replace(key,"'","’")#

Report · Sep 27, 2006

i think your error is due to a wrong sql statement first of all... you have to use ZipCode = before each value you are checking fo, not just once like you do.

so you have to change
<cfset Caller.passedreturneddata = Caller.passedreturneddata & "'#mystruct.zipcode#' or ">
to
<cfset Caller.passedreturneddata = Caller.passedreturneddata & "'#mystruct.zipcode#' or ZipCode=">

on the other hand, why don't you change your Caller.passedreturneddata to a comma-delimited list and then use "... AND ZipCode IN '#Caller.passedreturneddata#' instead?

Report · Sep 27, 2006

use the preservesinglequotes function.

Report · Sep 27, 2006

Originally it was a comma delimited list, but frankly I wasn't sure how work with it that way. So if I understand you that would be...

<cfquery datasource="normmy_denver" name="events">
Select *
From Calendar2
Where fee = No
And Category = #form.category#
And Subcategory = #form.subcategory#
And ZipCode IN '#Caller.passedreturneddata#'
Order by date, time
<cfquery>

Report · Sep 28, 2006

quote:

Originally posted by: InkFasT!
<cfquery datasource="normmy_denver" name="events">
Select *
From Calendar2
Where fee = No
And Category = #form.category#
And Subcategory = #form.subcategory#
And ZipCode IN '# ListQualify(Caller.passedreturneddata, "'")#'
Order by date, time
<cfquery>

Report · Sep 27, 2006

yes, as long as your Caller.passedreturneddata is a comma-delimited list, you can use it like that.

Report · Sep 28, 2006

That worked for the single quotes, but I've never seen this error before. Not finding anything in Google either.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] In operator without () in query expression 'fee = No And Category = Horoscope And Subcategory = Horoscope3 And ZipCode IN

Select * From Calendar2 Where fee = No And Category = Horoscope And Subcategory = Horoscope3 And ZipCode IN ''80212',' 80212',' 80212',' 80034',' 80214',' 80214',' 80214',' 80214',' 80211',' 80033',' 80033',' 80033',' 80001',' 80002',' 80002',' 80002',' 80204',' 80295',' 80257',' 80266',' 80266',' 80248',' 80217',' 80265',' 80292',' 80215',' 80215',' 80215',' 80255',' '' Order by date, time

Report · Sep 28, 2006

You need parentheses when using the keyword IN in sql. It's right there in your error message, didn't you read it?

You should also know that you have changed your logic from what you were initially attempting to do.

Report · Sep 28, 2006

I read it, I didn't understand it.

Report · Sep 29, 2006

I got it to work with a variation of what you guys wrote...

And ZipCode IN (#PreserveSingleQuotes (passedreturneddata)#)

Thanks for all the help!
D.

Adobe Community

Preserving Single Quotes

1 Correct answer