coldfusion query dynamic where statement...

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.?
WAlexxeAsked:
Who is Participating?
 
ansudhindraConnect With a Mentor Commented:
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
 
ansudhindraCommented:
please post the query...
0
 
WAlexxeAuthor Commented:
<cfquery name="getCompanies" datasource="mydb">
select *
from comp_names
where comp_names = 'name from dropdown list'
</cfquery>
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
srikanthmadishettiCommented:
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
 
srikanthmadishettiCommented:
@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
 
WAlexxeAuthor Commented:
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
 
srikanthmadishettiCommented:
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
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.

All Courses

From novice to tech pro — start learning today.