WAlexxe
asked on
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.?
please post the query...
ASKER
<cfquery name="getCompanies" datasource="mydb">
select *
from comp_names
where comp_names = 'name from dropdown list'
</cfquery>
select *
from comp_names
where comp_names = 'name from dropdown list'
</cfquery>
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
where company_name in (#ListQualify(form.company
<cfquery name="getCompanies" datasource="mydb">
select *
from comp_names
where comp_names in (#ListQualify(form.company
</cfquery
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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 .
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 .
ASKER
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!!
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>
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>