[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Drop down list and CFquery

Posted on 2006-04-03
9
Medium Priority
?
156 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?
0
Comment
Question by:lhaluska
9 Comments
 
LVL 18

Expert Comment

by:Plucka
ID: 16366457
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
 
LVL 8

Expert Comment

by:JRockFL
ID: 16366589
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
 
LVL 9

Expert Comment

by:digicidal
ID: 16367355
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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 1

Author Comment

by:lhaluska
ID: 16396288
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
 
LVL 9

Expert Comment

by:digicidal
ID: 16396518
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
 
LVL 1

Author Comment

by:lhaluska
ID: 16396577
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
 
LVL 9

Accepted Solution

by:
digicidal earned 2000 total points
ID: 16396620
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
 
LVL 1

Author Comment

by:lhaluska
ID: 16396647
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
 
LVL 9

Expert Comment

by:digicidal
ID: 16399357
Ah OK... so you really needed a combination.  Cheers & happy to be of assistance!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

834 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