Multiple Selections From Listbox Into Query

multiple selections from listbox 2 query

Page 1 of 1

7 Replies - 30954 Views - Last Post: 03 September 2009 - 07:30 AM Rate Topic: -----

#1 golden  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 14-June 04

Multiple Selections From Listbox Into Query

Post icon  Posted 14 June 2004 - 07:45 AM

Hi all,

Am getting stuck on how to code the query using multiple selections from a listbox (named which_fields). Have read various threads and can't get what others say to work.

The scenario for this prob is: I want the user to be able to select from a listbox (which contains a list of all the fields in the GENERAL_NEEDS_CLEAN_test table in the database) which fields they want to process in the query. Multiple fields selections are needed. So I have created a listbox (which allows multiple selections) using the following code (note: only the first part of the cfform code shown here but the CFForm tag works fine):

<CFFORM name="DateForm" ACTION="Download.cfm" METHOD="post">
     
     <tr><td><B>Step 1. Select fields to download:</B>
     <cfselect name="which_fields" display= "label" required="yes" multiple="YES" size="5">
     <option value="All FIELDS" selected="selected">All Fields</option>
     <cfoutput><cfloop list="#database_fields.ColumnList#" index="MyColumnName">
     <option value="#MyColumnName#">#MyColumnName#</option></cfloop></cfoutput></cfselect></td><td></td></tr>



Then I send the form variables through to the action page using CFForm and use the following query....but when I run the query I get no results.


<cfquery name="export_selected_all_records" datasource="supporting_peopleTESTDB">
SELECT *
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#' AND 'form.which_fields' = '#form.which_fields#'
</cfquery>



What am I getting wrong?? It is possible to do what I'm asking in ColdFusion. All the other posts I've read have done slightly different things.

Can anyone advise me?? :huh:

Is This A Good Question/Topic? 0
  • +

Replies To: Multiple Selections From Listbox Into Query

#2 cyberscribe  Icon User is offline

  • humble.genius
  • member icon

Reputation: 10
  • View blog
  • Posts: 1,062
  • Joined: 05-May 02

Re: Multiple Selections From Listbox Into Query

Posted 14 June 2004 - 08:52 AM

Looks like one for Chris. I always use real HTML for multiple listboxes and checkboxes and just make the variables arrays with:
name="variable_name[]"


Was This Post Helpful? 0
  • +
  • -

#6 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Multiple Selections From Listbox Into Query

Posted 14 June 2004 - 10:03 AM

Alrighty, here's what you've got when using multi-select.

which_fields (the multi-select) outputs as a comma delimited list. I'm not sure what you're trying to accomplish with your which_fields = which_fields, however, you can do a few different things. Convert the list to an array [ListToArray()], then you can loop through it, reference a specific element, etc. etc.

The other thing, and I do this all the time to keep the options selected still selected:
<option value="#tests.test_id#" <cfif (isDefined("test")) AND (ListFind(test, #tests.test_id#) NEQ "0")>SELECTED</cfif>>#tests.test_name#</option>


Using ListFind(list, string) it looks for a specific item in the list, if it finds it, it returns the location (you can then use ListGetAt(list, position) to print it back out or whatever) or it returns 0 if it's not in the list. So if you are having to compare or find a userid based on something selected in a list, you'd want to loop through the list or the original record set, then using listfind() check to see if it was selected.

I hope that helps a little.
Was This Post Helpful? 1

#7 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Multiple Selections From Listbox Into Query

Posted 14 June 2004 - 10:05 AM

One other thing, since form.which_fields is a list, you can change your query to:
<cfquery name="export_selected_all_records" datasource="supporting_peopleTESTDB">
SELECT *
FROM GENERAL_NEEDS_CLEAN_test
WHERE ManagingAssoc = '#CLIENT.id#' AND 'form.which_fields' IN '#form.which_fields#'
</cfquery>


Using IN will pull back all records where form.which_fields (column) is any of 1, 2, 3, 4, 5.... (or whatever records were selected)

I think that is what you are trying to do and doing that should work just fine.
Was This Post Helpful? 0
  • +
  • -

#8 alustie  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 13-October 05

Re: Multiple Selections From Listbox Into Query

Posted 29 March 2006 - 06:56 PM

Well -- CFSELECT
I'm trying to use it so a client can select multiple names from a drop-down list and I can populate the drop-down list fine with this query:
<cfquery name="getPartner" datasource="lustie" username="lustie" password="2510aol">
SELECT partner_ID, name_1 from tblPartner
ORDER BY name_1
</cfquery

I can select more than one value (name) from the list -- that is, several are highlighted when I use the control + Click method.

However, none of the names get inserted! I have read messages on the forums, read documentation, etc., but can't seem to make it work.

Here is my CFSELECT statement (part of a CFFORM)
Name(s) of Partners

<cfselect name = "getName"
query="getPartner"
value="name_1"
display="name_1"
SIZE="8"
required = "Yes"
multiple = "Yes"
width="100"
selected="1,2,3"
labels="Partner Name: ">

</cfselect>

Anyone's ideas would be helpful, or changes to code, or ???
Was This Post Helpful? 0
  • +
  • -

#9 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Multiple Selections From Listbox Into Query

Posted 29 March 2006 - 07:23 PM

How are you inserting your values in to the database? Can you add this to the page which inserts your values:

<cfdump var="#form.getPartner#">


Copy and paste the output. Normally, a multi-select will send values in a comma seperated list. If you need each value inserted individually, you'll have to loop over the list.
Was This Post Helpful? 0
  • +
  • -

#10 ritexcorp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 03-September 09

Re: Multiple Selections From Listbox Into Query

Posted 03 September 2009 - 07:24 AM

View Postalustie, on 29 Mar, 2006 - 05:56 PM, said:

Well -- CFSELECT
I'm trying to use it so a client can select multiple names from a drop-down list and I can populate the drop-down list fine with this query:
<cfquery name="getPartner" datasource="lustie" username="lustie" password="2510aol">
SELECT partner_ID, name_1 from tblPartner
ORDER BY name_1
</cfquery

I can select more than one value (name) from the list -- that is, several are highlighted when I use the control + Click method.

However, none of the names get inserted! I have read messages on the forums, read documentation, etc., but can't seem to make it work.

Here is my CFSELECT statement (part of a CFFORM)
Name(s) of Partners

<cfselect name = "getName"
query="getPartner"
value="name_1"
display="name_1"
SIZE="8"
required = "Yes"
multiple = "Yes"
width="100"
selected="1,2,3"
labels="Partner Name: ">

</cfselect>

Anyone's ideas would be helpful, or changes to code, or ???

Was This Post Helpful? 0
  • +
  • -

#11 ritexcorp  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 03-September 09

Re: Multiple Selections From Listbox Into Query

Posted 03 September 2009 - 07:30 AM

View Postritexcorp, on 3 Sep, 2009 - 06:24 AM, said:

View Postalustie, on 29 Mar, 2006 - 05:56 PM, said:

Well -- CFSELECT
I'm trying to use it so a client can select multiple names from a drop-down list and I can populate the drop-down list fine with this query:
<cfquery name="getPartner" datasource="lustie" username="lustie" password="2510aol">
SELECT partner_ID, name_1 from tblPartner
ORDER BY name_1
</cfquery

I can select more than one value (name) from the list -- that is, several are highlighted when I use the control + Click method.

However, none of the names get inserted! I have read messages on the forums, read documentation, etc., but can't seem to make it work.

Here is my CFSELECT statement (part of a CFFORM)
Name(s) of Partners

<cfselect name = "getName"
query="getPartner"
value="name_1"
display="name_1"
SIZE="8"
required = "Yes"
multiple = "Yes"
width="100"
selected="1,2,3"
labels="Partner Name: ">

</cfselect>

Anyone's ideas would be helpful, or changes to code, or ???



http://livedocs.adob...le=00001348.htm

//name of select control- selectDepts

select * from DeptTBL where Dept_Name IN (#ListQualify(Form.selectDepts, "'")#)
will give you this:

SELECT *
FROM Departmt
WHERE Dept_Name IN ('Marketing','Sales')
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1