Drop down list and CFquery

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?
LVL 1
lhaluskaAsked:
Who is Participating?
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.

PluckaCommented:
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
0
JRockFLCommented:
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>
0
digicidalCommented:
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.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

lhaluskaAuthor 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>
0
digicidalCommented:
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.
0
lhaluskaAuthor 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>

 
0
digicidalCommented:
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.
0

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
lhaluskaAuthor 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>
0
digicidalCommented:
Ah OK... so you really needed a combination.  Cheers & happy to be of assistance!
0
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
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.