Converting to a list from a recordset

[Newsgroup_User]

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>

[Dan_Bracuk]

Look up the valuelist and quotedvaluelist function in the cfml reference manual. If you don't have one, the internet does.

[Newsgroup_User]

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>

[Newsgroup_User]

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

[Dan_Bracuk]

Use valuelist to generate your list.
Use cfqueryparam list="yes" in your query.