4 Replies Latest reply on Mar 27, 2007 5:31 AM by Dan Bracuk

    Converting to a list from a recordset

    Level 7
      I have a list of items from various queries. In this example I have 2
      states, MI and IN in the database for this user. I am then trying to conver
      the output of the query into a list to weight against. Its only getting the
      last state entered. Any ideas on why am I not getting all the states set to
      'MyStates', only the last one entered? Thanks.

      <cffunction name="GetJobs" access="public" returntype="Query">
      <cfargument name="pcode" type="string" required="yes">
      <cfquery name="States" datasource="">
      Select prefered_state
      FROM state_preference
      Where PEOPLE_ORG_CODE_ID = '#arguments.pcode#'
      </cfquery>
      <cfquery name="Venues" datasource="">
      Select prefered_venue
      FROM venue_preference
      Where PEOPLE_ORG_CODE_ID = '#arguments.pcode#'
      </cfquery>
      <cfquery name="Areas" datasource="">
      Select prefered_practice
      FROM practice_preference
      Where PEOPLE_ORG_CODE_ID = '#arguments.pcode#'
      </cfquery>
      <cfset MyStates = ListQualify(States.prefered_state,"",",","CHAR")>
      <cfset MyVenues = ListQualify(Venues.prefered_venue,"",",","CHAR")>
      <cfset MyAreas = ListQualify(Areas.prefered_practice,"",",","CHAR")>
      <!---Begin Job Search--->
      <cfquery name="GetJobs" datasource="CareerServices">
      Select *
      FROM JOBS
      WHERE publish = 1
      <cfif #MyVenues# NEQ "">AND venue IN ('#myvenues#')</cfif>
      <cfif #MyAreas# NEQ "">AND area IN ('#myareas#')</cfif>
      <cfif #MyStates# NEQ "">AND state IN ('#mystates#')</cfif>
      ORDER BY DatePosted DESC
      </cfquery>
      <cfreturn GetJobs>
      </cffunction>


        • 1. Re: Converting to a list from a recordset
          Dan Bracuk Level 5
          Look up the valuelist and quotedvaluelist function in the cfml reference manual. If you don't have one, the internet does.
          • 2. Re: Converting to a list from a recordset
            Level 7
            I changed it from a listqualify to a quotedvaluelist, but still getting
            errors.

            <cfset MyStates = #QuotedValueList(States.prefered_state,",")#>

            It outputs:
            Select * FROM JOBS WHERE publish = 1 AND state IN (''IN'',''MI'') ORDER BY
            DatePosted DESC which seems correct, but I am getting an error because the
            surrounding quote marks are actually 2 single quotes. I ran it through my
            SQL query analizer and if I remove one of each extra single quote marks, it
            works fine.

            I tried using just the valuelist and it, of course, gave me errors from no
            quote marks. Any ideas?

            Code:
            ...
            <cfset MyStates = #QuotedValueList(States.prefered_state,",")#>
            <cfset MyVenues = #QuotedValueList(Venues.prefered_venue,",")#>
            <cfset MyAreas = #QuotedValueList(Areas.prefered_practice,",")#>
            <!---Begin Job Search--->
            <cfquery name="GetJobs" datasource="CareerServices">
            Select *
            FROM JOBS
            WHERE publish = 1
            <cfif #MyStates# NEQ "">AND state IN (#mystates#)</cfif>
            <cfif #MyVenues# NEQ "">AND venue IN (#myvenues#)</cfif>
            <cfif #MyAreas# NEQ "">AND area IN (#myareas#)</cfif>
            ORDER BY DatePosted DESC
            </cfquery>
            <cfreturn GetJobs>
            </cffunction>


            • 3. Re: Converting to a list from a recordset
              Level 7
              try the following:

              <cfset MyStates = ValueList(States.prefered_state,",")>
              <cfquery name="GetJobs" datasource="CareerServices">
              SELECT *
              FROM JOBS
              WHERE publish = 1
              <cfif MyStates NEQ "">AND state IN (<cfqueryparam
              cfsqltype="cf_sql_varchar" value="#mystates#" list="yes">)</cfif>
              ORDER BY DatePosted DESC;
              </cfquery>

              --

              Azadi Saryev
              Sabai-dee.com
              http://www.sabai-dee.com
              • 4. Re: Converting to a list from a recordset
                Dan Bracuk Level 5
                Use valuelist to generate your list.
                Use cfqueryparam list="yes" in your query.