?
Solved

coldfusion query dynamic where statement...

Posted on 2012-08-22
7
Medium Priority
?
788 Views
Last Modified: 2013-11-15
I have a drop down list that contains several company names. When ALL is selected the list is passed from the form to the cfquery but the query returns 0 info. I am a beginer at dynamic SQL statements but I think I need a WHERE statement that allows a comma seperated list to be passed and returned.?
0
Comment
Question by:WAlexxe
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:ansudhindra
ID: 38319462
please post the query...
0
 

Author Comment

by:WAlexxe
ID: 38319758
<cfquery name="getCompanies" datasource="mydb">
select *
from comp_names
where comp_names = 'name from dropdown list'
</cfquery>
0
 
LVL 13

Expert Comment

by:srikanthmadishetti
ID: 38319777
use some thing like this


where company_name  in (#ListQualify(form.company_name,"'",",")#)

<cfquery name="getCompanies" datasource="mydb">
select *
from comp_names
where comp_names    in (#ListQualify(form.company_name,"'",",")#)
</cfquery
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 13

Accepted Solution

by:
ansudhindra earned 2000 total points
ID: 38319791
Use below query..
form.comp_name is the form select element name.

<cfquery name="getCompanies" datasource="mydb">
    select *
    from comp_names
    <cfif trim(form.comp_name) neq "ALL">
        where comp_name = '#form.comp_name#'
    </cfif>
</cfquery>

Open in new window

0
 
LVL 13

Expert Comment

by:srikanthmadishetti
ID: 38319826
@ansudhindra

Pls read what WAlexxe is asking about  

>>>  I need a WHERE statement that allows a comma seperated list to be passed "

If it is a comma seperated list then what you are suggesting will not work .
0
 

Author Closing Comment

by:WAlexxe
ID: 38320050
Once I could see what you were doing with the if statement I was able to modify it with an else statement and got it to working! Thanks!!
0
 
LVL 13

Expert Comment

by:srikanthmadishetti
ID: 38321636
No points

WAlexxe

Please be clear on your req , You mentioned about getting a list of companies name  in your form variable .

I was under impression that you are trying to give mutiselect  for your select box as you were talking about a list of company names being passed .

and one more suggestion For all just send blank for company name and change your if condition to

<cfif len(trim(form.comp_name))>


and your select drop down should be like

<select name="comp_name">
<option value="">ALL</option>
</select>
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you are like me and like multiple layers of protection, read on!
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 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