We help IT Professionals succeed at work.

My drop down list pulls data from a database correctly but clicking ALL does not add all of the data required.

Medium Priority
Last Modified: 2012-05-06
I have a page that present data pulled from a database. The page has a drop down list also pulled from the database that lists only states that have data. Basically the list on my dev box is ALL NY (3) CA (2) WA (1). I have it set up without a submit button, the user only needs to click the state name for that states specifics or ALL to get everything. The problem I am having is that ALL when clicked does nothing.

When the page first comes up ALL of the information gets displayed as intended. Also If I add something like --- Select --- to the list thus moving ALL to the second spot on the list ALL works.
<CFQUERY NAME="getStateID" DATASOURCE="#request.ds#">
<!--- SELECT 'All' AS state, '1_ALL' AS state_abv
SELECT state, state_abv FROM _lk_states (nolock) WHERE country_id = 1 AND NOT state_abv IN ('AS', 'GU', 'PW', 'VI', 'PR')
ORDER BY state_abv --->
<!--- <cfset getCategoryAuctions = "C:\Inetpub\wwwroot\Marketing\bankowned\Queries\qry_landing.cfm"> --->
SELECT 'All' AS state, '2_ALL' AS state_abv
select distinct state + ' ' + '(' + rtrim(cast(count(auctionid) as char)) + ')' as 'state', state_abv
from getCategoryAuctions A join _lk_states (nolock) s on a.locatedstate = s.state_abv
group by state, state_abv
  <form name="States" action="index.cfm" method="post">
	 Refine Your Search: 
        <td align="right"> States: </td>
        <td align="center">			
		    <SELECT name="state" class="selectbox" STYLE="width: 140px" onchange='this.form.submit()'>
			<cfoutput query="getStateID">
			<option value="#getStateID.state_abv#">#getStateID.state#</option>
Also from my query page...
<CFPARAM NAME="attributes.sortOrder" DEFAULT="0">
<CFPARAM NAME="attributes.sortOrderToggle" DEFAULT=attributes.sortOrder>
<CFPARAM NAME="attributes.sortProperty" DEFAULT="1">
<CFPARAM NAME="attributes.page" DEFAULT="1">
<CFPARAM DEFAULT="0" NAME="attributes.level1">
<CFPARAM DEFAULT="0" NAME="attributes.level2">
<CFPARAM DEFAULT="0" NAME="attributes.level3">
<cfquery name="getdistinctchannelcodes" datasource="#request.ds#">
	select distinct channelcode from asset where channelcode >0
<CFSET assetDisplayDetails = UDF_NR(ValueList(getdistinctchannelcodes.channelcode), 1, attributes.sortOrder, attributes.sortProperty)>
<!--- Get all of this store / categories auctions--->
<cfparam name="local.sortDir" default="DESC">
<cfparam name="local.sortBy" default="[Time Left]">
<cfif trim(attributes.sortProperty) eq "2">
<!---	<cfset local.sortBy = "[highbid]">
<cfelse> --->
	<cfset local.sortBy = "[Time Left]">
<cfif trim(attributes.sortOrder) eq "1">
	<cfset local.sortDir = "ASC">
	<cfset local.sortDir = "DESC">
<cfset local.sortBy = "[Time Left]">
<cfparam name="form.state" default="2_All" />
<CFQUERY NAME="getCategoryAuctions" DATASOURCE="#request.ds#">
<!--- Dharriman getCategoryAuctions is now a view in SQL --->
select * from getCategoryAuctions 
<cfif form.State neq "2_ALL">
	WHERE LocatedState = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.State#" />
ORDER BY #local.sortBy# DESC <!--- List the auction data by closing soonest --->
<CFQUERY NAME="getDistinctCategoryIDs" DBTYPE="query">
	SELECT DISTINCT categoryID FROM assetDisplayDetails
<!--- Get all of this store's category names--->
<CFIF len(assetDisplayDetails.categoryID)>
	<CFQUERY NAME="getCatNames" DATASOURCE="#request.ds#">
		SELECT distinct lev1_id,lev1<!---,lev2,lev2_id,lev3,lev3_id,categoryID---> 
		  from dbo.Categories()
         where StatusID = 1 AND categoryID in (
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#ValueList(getDistinctCategoryIDs.categoryID)#" list="true">
		Order By lev1
		<!--- and lev3 is not null --->

Open in new window

Watch Question

this is because the onchange event isn't triggered when you pull down the combo box and select 'ALL' again (seemingly so because by default, 'ALL' is selected, which when clicking to 'ALL' again it would seem unchanged).

i suggest adding the '-- Select State --' option as your first combo box option.

Hope that helps,



I did that this morning but the boss wants me to have the list without the --- Select State --- he also wants me to have it keep the drop down item that has been selected viewable. So if I click on NY (3) that the users first choice would then be NY (3).

Thanks for replying. Is there anyway to get this to work without resorting to --- Select State --- ???

Thanks! Adrew for your help.
the event is pretty limited to onchange, but you could do what other major sites do:

    display the current selected state as the first option, then add a '---' on the second option.

the combo list should look like:

NY (3)
CA (2)
WA (1)

at first, and if for example you choose NY you'd have

NY (3)
CA (2)
WA (1)

make sure not to register --- as a state. :D
but as far as HTML and JavaScript goes, it's really a dead end.



Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Thanks andrew I am going to talk to him about this in the morning and see if he will let me do it this way. If not Ill need to keep looking for another solution.


Ill get you those points soon too.


Thanks for your help!

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.