 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ColdFusion / Advanced Techniques / September 2006Preserving Single Quotes
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# [B]8 : And ZipCode = #form.zipcode#[/B] 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 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? use the preservesinglequotes function. 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> [q][i]Originally posted by: [b][b]InkFasT![/b][/b][/i] <cfquery datasource="normmy_denver" name="events"> Select * From Calendar2 Where fee = No And Category = #form.category# And Subcategory = #form.subcategory# And ZipCode IN '#[b]ListQualify(Caller.passedreturneddata, "'")[/b]#' Order by date, time <cfquery>[/q] yes, as long as your Caller.passedreturneddata is a comma-delimited list, you can use it like that. 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 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. I read it, I didn't understand it. I got it to work with a variation of what you guys wrote... And ZipCode IN (#PreserveSingleQuotes (passedreturneddata)#) Thanks for all the help! D. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.