Problem in SELECT Statement

Sortfield value as "Show All" is selected from the Category dropdown
I have a drop down menu to select Category and based on the selected category, I run a query against Inventory table to display items that belong to the selected category.
But when I select "Show All", I like to display all items from the Inventory.
However when I do that, the screen stays the same.
So I displayed the value of SORTFIELD varialbe using <cfoutput> and found out that it kept the previous selected Category ID instead of showing "" when "Show All" was selected from the drop-down.

Is that because  of this statement?                     <CFIF #FORM.SORTFIELD# GT 0>  

Here is the code:
************************************************
      
<CFparam NAME="FORM.SORTFIELD" Default=0>
<cfoutput>#FORM.SORTFIELD#</cfoutput>
            
            <CFQUERY NAME="GetCategory" datasource="WhatsThePoint">
            SELECT   c.Category, c.ID, Count(inv.CategoryID) as CategoryCount
            FROM     Category C LEFT JOIN Inventory inv on inv.categoryID = c.ID
            GROUP BY c.Category, C.ID
            ORDER BY c.Category
            </CFQUERY>

<CFQUERY NAME="GetFeaturedItems" datasource="WhatsThePoint">
SELECT      Inventory.* ,  Category.Category, Designer.DesignerName
                     FROM         Inventory, Category, Designer
                    WHERE Inventory.CategoryID = Category.ID and Inventory.DesignerID = Designer.ID
                  and    Inventory.Status = 'A'
                        <CFIF #FORM.SORTFIELD# GT 0>
                             AND Inventory.CategoryID = #FORM.SORTFIELD# <!--- Display only the items in selected Category from drop down --->
                       </CFIF>
                </CFQUERY>

<FORM ACTION="Inventory.cfm" METHOD="post">
                  <FONT color="black" FACE="MS Sans Serif" SIZE=-1>Select Category:
                  <SELECT NAME="SORTFIELD" onChange="this.form.submit()">
                  <OPTION VALUE="" selected>Show All</OPTION>
                  <CFOUTPUT query="GetCategory">
                  <OPTION VALUE="#ID#">#Category#(#CategoryCount#)</OPTION>
                  </CFOUTPUT>
                  </SELECT>  
*********************************************
LVL 1
sgleeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
When form.sortField is empy... is empty greater than or less than zero?  I don't know...

<CFIF #FORM.SORTFIELD# GT 0>  

Change to ...

<CFIF val(FORM.SORTFIELD) GT 0>  

Now any non-numeric will resolve to zero.

Note I removed the unnecessary #s.
sgleeAuthor Commented:
Did not make the difference.
gdemariaCommented:
The number you've circled in red, what variable are you displaying?   FORM.SORTFIELD ?
sgleeAuthor Commented:
gdemaria,
 
  Sorry I have not answer your question. I have been busy.
  Below is  actual code at the top of CFM page where it displays the value of variable "SORTFIELD".

  <CFparam NAME="FORM.SORTFIELD" Default=0>
<cfoutput>#FORM.SORTFIELD#</cfoutput>
James RodgersWeb Applications DeveloperCommented:
Whwen you load the page after selecting a  category do you set the option in the dropdown to that category or does it show "Show All"

If after loading the page after a successful category filter the dropdoppwn is set on "show all" selecting   "show all" from teh dropdown will not fire off the onchange event in the select

default optios is set to "show all"
select americana
form submits
page displays results for americana

on the drop down if it has show all selected after page load , this will not register as change when picking that option from the dropdown  

try updating the dropdown to this

<SELECT NAME="SORTFIELD" onChange="this.form.submit()">
	<OPTION VALUE="" selected>Show All</OPTION>
	<CFOUTPUT query="GetCategory">
		<OPTION VALUE="#ID#"<cfif ID EQ  form.sortfield> selected="selected"</cfif>>#Category#(#CategoryCount#) </OPTION>
	</CFOUTPUT>
</SELECT>

Open in new window


also clean up your submitted data, never rely on the submitted data

<cfset filter_category_id = 0 />
<cfif isDefined('form.sortfield' and isNumeric(form.sortfield)>
<cfset filter_category_id = form.sortfield />
</cfif>

Open in new window


then use filter_category_id in your SQL

never use submitted data directly in an SQL query, it can be used maliiciously

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.