10 Replies Latest reply on Sep 29, 2006 9:38 AM by Inkfast

Preserving Single Quotes

Inkfast Sep 27, 2006 9:29 AM

I'm using the following code to produce a list of zipcodes for a radius search:
<cfset Caller.passedreturneddata = Caller.passedreturneddata & "'#mystruct.zipcode[x]#' 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

1. Re: Preserving Single Quotes

murpg Sep 27, 2006 10:00 AM (in response to Inkfast)

Try something like this. #Replace(key,"'","’")#
Like Show 0 Likes(0)

Actions
2. Re: Preserving Single Quotes

azadisaryev Sep 27, 2006 10:05 AM (in response to Inkfast)

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[x]#' or ">
to
<cfset Caller.passedreturneddata = Caller.passedreturneddata & "'#mystruct.zipcode[x]#' 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?
Like Show 0 Likes(0)

Actions
3. Re: Preserving Single Quotes

Dan Bracuk Sep 27, 2006 10:41 AM (in response to Inkfast)

use the preservesinglequotes function.
Like Show 0 Likes(0)

Actions
4. Re: Preserving Single Quotes

Inkfast Sep 27, 2006 3:23 PM (in response to Dan Bracuk)

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>
Like Show 0 Likes(0)

Actions
5. Re: Preserving Single Quotes

azadisaryev Sep 27, 2006 4:06 PM (in response to Inkfast)

yes, as long as your Caller.passedreturneddata is a comma-delimited list, you can use it like that.
Like Show 0 Likes(0)

Actions
6. Re: Preserving Single Quotes

Stefan_K. Sep 28, 2006 2:09 AM (in response to Inkfast)

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>
Like Show 0 Likes(0)

Actions
7. Re: Preserving Single Quotes

Inkfast Sep 28, 2006 10:32 AM (in response to Inkfast)

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
Like Show 0 Likes(0)

Actions
8. Re: Preserving Single Quotes

Dan Bracuk Sep 28, 2006 10:54 AM (in response to Inkfast)

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.
Like Show 0 Likes(0)

Actions
9. Re: Preserving Single Quotes

Inkfast Sep 28, 2006 11:35 AM (in response to Dan Bracuk)

I read it, I didn't understand it.
Like Show 0 Likes(0)

Actions
10. Re: Preserving Single Quotes

Inkfast Sep 29, 2006 9:38 AM (in response to Inkfast)

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

And ZipCode IN (#PreserveSingleQuotes (passedreturneddata)#)

Thanks for all the help!
D.
Like Show 0 Likes(0)

Actions