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

    Preserving Single Quotes

    Inkfast Level 1
      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 &apos;fee = No And Category = Horoscope And Subcategory = Horoscope3 And ZipCode = &apos;&apos;80212&apos;&apos; or &apos;&apos;80212&apos;&apos; or &apos;&apos;80212&apos;&apos; or &apos;&apos;80034&apos;&apos; or &apos;&apos;80214&apos;&apos; or &apos;&apos;80214&apos;&apos; or &apos;&apos;80214&apos;&apos; or &apos;&apos;80214&apos;&apos; or &apos;&apos;80211&apos;&apos; or &apos;&apos;80033&apos;&apos; or &apos;&apos;80033&apos;&apos; or &apos;&apos;80033&apos;&apos; or &apos;&apos;80001&apos;&apos; or &apos;&apos;80002&apos;&apos; or&apos;.

      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
          Try something like this. #Replace(key,"'","&rsquo;")#
          • 2. Re: Preserving Single Quotes
            azadisaryev Level 1
            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?
            • 3. Re: Preserving Single Quotes
              Dan Bracuk Level 5
              use the preservesinglequotes function.
              • 4. Re: Preserving Single Quotes
                Inkfast Level 1
                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>
                • 5. Re: Preserving Single Quotes
                  azadisaryev Level 1
                  yes, as long as your Caller.passedreturneddata is a comma-delimited list, you can use it like that.
                  • 6. Re: Preserving Single Quotes
                    Stefan_K.
                    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>


                    • 7. Re: Preserving Single Quotes
                      Inkfast Level 1
                      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 &apos;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
                      • 8. Re: Preserving Single Quotes
                        Dan Bracuk Level 5
                        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.
                        • 9. Re: Preserving Single Quotes
                          Inkfast Level 1
                          I read it, I didn't understand it.
                          • 10. Re: Preserving Single Quotes
                            Inkfast Level 1
                            I got it to work with a variation of what you guys wrote...

                            And ZipCode IN (#PreserveSingleQuotes (passedreturneddata)#)

                            Thanks for all the help!
                            D.