We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Drop down list and CFquery

lhaluska
lhaluska asked
on
Medium Priority
180 Views
Last Modified: 2013-12-24
I have a drop down list that has values to reference to a MSSQL database but what I am having trouble with is how do I make the query so when someone doesn't select any it sends all of the names on the list and how would I make the query?
Comment
Watch Question

Commented:
lhaluska,

What you would do, is on your action page, check if nothing was selected and if so, just do the query again to get a list of all the values.

Regards
Plucka

Commented:
On the action page, check to see if the drop down has a value, if it's blank select all the records. If not, then filter it by the user selection.

<cfif form.dropdown EQ "">
  <cfquery name="qRecs" datasource="yourDSN">
  SELECT *
  FROM table
  </cfquery>
<cfelse>
  <cfquery name="qRecs" datasource="yourDSN">
  SELECT *
  FROM table
  WHERE field = #form.dropdown#
  </cfquery>
</cfif>
You can put your conditional inside your query too if you find it's easier to read.

  <cfquery name="qRecs" datasource="yourDSN">
  SELECT *
  FROM table
  <cfif form.dropdown NEQ "">WHERE field = #form.dropdown#</cfif>
  </cfquery>

Time to process should be identical.  Depending on the type of data passed in the value of the option you could alternately use IsNumeric(form.dropdown) or Len(Trim(form.dropdown)) GT 0 instead of NEQ "".
As a best practice I would recommend (as Adobe/Macromedia do) to use <cfqueryparam value="#form.dropdown#"> in your query as this will speed excusion on SQL due to caching better and adds a level of protection from SQL String attacks as well.

Author

Commented:
Here is my query now but I am getting an error?
<cfquery name="qryAccessData" datasource="NSKInfo">
SELECT     a.systemName, a.systemNumber, a.numberofcpu, a.numberofio, organization.organization, a.division, b.director, a.type, a.comment,convert(varchar,a.disposition,110) as disdate, locations.Building_Number_Compaq, convert(varchar,a.dateleftbldg,110) as leftdate, a.rmano, a.redeploy, a.lightson, a.footprint, a.srdate, a.srby, a.onallcount
FROM         dbo.NSKSystemInfo a
INNER JOIN dbo.director b ON b.director_ID = a.director_id
INNER JOIN locations ON locations.location_id  = a.loc_id
INNER JOIN organization ON organization.organization_id  = a.organization_id
WHERE     a.onallcount = 'Yes' or
<cfif isNumeric(form.organization)>WHERE field = #form.organization#</cfif>
<cfif isNumeric(form.numberofcpu)>WHERE field = #form.numberofcpu#</cfif>
order by a.systemname
</cfquery>
Don't you mean the two conditionals to be this?:
<cfif isNumeric(form.organization)>WHERE organization.organization_id = #form.organization#</cfif>
<cfif isNumeric(form.numberofcpu)>WHERE a.numberofcpu = #form.numberofcpu#</cfif>

Also with this I'm assuming that you never have a possibility of the form specifying both an organization and a numberofcpu because that will cause errors.  You should probably use:

<cfif isNumeric(form.organization)>
     WHERE organization.organization_id = #form.organization#
     <cfif isNumeric(form.numberofcpu)>
         AND a.numberofcpu = #form.numberofcpu#
     </cfif>
<cfelseif isNumeric(form.numberofcpu)>
     WHERE a.numberofcpu = #form.numberofcpu#
</cfif>

Or a similar construct.  That way if either value is specified the appropriate WHERE clause is generated, but if both are specified, then a WHERE ... AND ... clause is generated rather than two separate WHERE's.

Author

Commented:
What happens with the where clause for the first where a.onallcount?

WHERE     a.onallcount = 'Yes' or
<cfif isNumeric(form.organization)>
     WHERE organization.organization_id = #form.organization#
     <cfif isNumeric(form.numberofcpu)>
         AND a.numberofcpu = #form.numberofcpu#
     </cfif>
<cfelseif isNumeric(form.numberofcpu)>
     WHERE a.numberofcpu = #form.numberofcpu#
</cfif>
order by a.systemname
</cfquery>

 
Ah OK sorry I missed that... then you don't need my secondary conditional... you can just use yours without the WHERE'S:

WHERE     a.onallcount = 'Yes'
<cfif isNumeric(form.organization)>OR organization.organization_id = #form.organization#</cfif>
<cfif isNumeric(form.numberofcpu)>OR a.numberofcpu = #form.numberofcpu#</cfif>
ORDER by a.systemname

So you'd get anything with an onallcount of 'Yes' and anything specified in the form.  So the form would 'Expand' your results.  Either that or if you used AND instead it would 'drill down' your results.

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

Ask the Experts

Author

Commented:
Cool Thank you very much it worksthis is what I did.
<cfif isNumeric(form.organization)>
   WHERE organization.organization_id = '#form.organization#' and onallcount ='yes'
     <cfif isNumeric(form.director)>
         AND b.director_ID = '#form.director#' and onallcount ='yes'
     </cfif>
<cfelseif isNumeric(form.director)>
   WHERE a.director_id = '#form.director#' and onallcount ='yes'
</cfif>
order by a.systemname
</cfquery>
Ah OK... so you really needed a combination.  Cheers & happy to be of assistance!
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.

OR

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.