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

Posted on 2009-02-11
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 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

Question by:northbytes
    LVL 7

    Expert Comment

    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,


    Author Comment

    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.
    LVL 7

    Accepted Solution

    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.



    Author Comment

    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.

    Author Closing Comment

    Thanks for your help!


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
    This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
    The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
    HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now