Populating Drop Down Boxes from a database

[gskar]

We moved our wesite from a Box running Coldfusion 4.0 to a box running
Coldfusion MX. We populate some drop down boxes with information from a
database table that has 10 Fields. Each of the fields are independant from
each other. Some of the fields have 40 rows of information - while some fields
only have 3 rows of information. I run my Query to bring in all my information
from the table using (*). When I go to my drop down boxes - I will get 37 rows
of NOTHING for the fields where I want to display only 3 rows of stuff. I use
the CFSelect command to populate the drop down boxes. Under ColdFusion 4.0 -
only the rows that had data were displayed - now all 40 rows are displayed -
even the ones with no data. Help! Do I need to change my Query or do I need
to make a change to my CFSelect command?

[Azeem]

You may want to try the following: 1. Use query-of-query on the main query to
crate separate ones to be used for each of your cfselects. In the q-of-q make
sure you only select rows that have data. or 2. Instead of passing the query
attribute to cfselect, use a cfloop and a conditional if to ouput the option
tags. Best of luck

[Abinidi]

Azeem is right. It is best to to a cfloop over the query. Do your query, then
loop it as such: <select name='fieldname'> <cfloop query='queryname'> <cfif
queryname.value IS 'whateveryouwant'>
<option><cfoutput>#queryname.value#</cfoutput></option> </cfif> </cfloop>
</select>

[gskar]

The suggestions I got were GREAT - I got it to work. NOW I need one more bit
of help if possible. Within the options in my Drop Down Boxes I need to select
one of them as the DEFAULT. Before I used the CFLoop command it did work - the
SELECTED option worked - now it does not. Any help would be appreciated.
Below is the code: LEAD2 is the value of my default which is one of the options
from the drop down box LOOP statement. <CFSELECT name='lead7' size='1'
selected='#Lead2#'> <cfloop query='QueryTableSeries3'> <cfif
QueryTableSeries3.Lead IS NOT ''> <option>
<cfoutput>#QueryTableSeries3.Lead#</cfoutput> </option>
</CFIF> </cfloop> </CFSELECT>

[zoeski80]

You haven't got a VALUE parameter on the OPTION perhaps that is what the CFSELECT SELECTED attribute uses to determine which option to mark as selected.

<OPTION VALUE="#QueryTableSeries3.Lead#">