CFQUERY Coldfusion 9 database country in Europe

I keep getting errors in dynamic built query - coldfusion9

<cfset EuropeCountryList = 
"Albania,Andorra,Armenia,Austria,Azerbaijan,Belarus,Belgium,Bosnia & Herzegovina,Bulgaria,Croatia,Cyprus,Czech Republic,Denmark,Estonia,Finland,France,Georgia,Germany,Greece,Hungary,Iceland,Ireland,Italy,Kosovo,Latvia,Liechtenstein,Lithuania,Luxembourg,Macedonia,Malta,Moldova,Monaco,Montenegro,The Netherlands,Norway,Poland,Portugal,Romania,Russia,San Marino,Serbia,Slovakia,Slovenia,Spain,Sweden,Switzerland,Turkey,Ukraine,United Kingdom,England,Wales,Northern Ireland,Wales,Scotland,Vatican City"
>

<!--- dynamically build query from form fields --->

<cfif IsDefined("form.country")
			and len(trim(form.country))
			and form.country is "europe">
     		and members.Country IN  "#EuropeCountriesList#" 
            
</cfif>

Open in new window


Throws this error

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.  <br>The error occurred on line 216.


Thanks for your help
Ian WhiteOwner and FounderAsked:
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.

Robert ShermanOwnerCommented:
Does it work as expected when the stuff inside the cfif statement is not included (i.e. when form.country is NOT europe)?  

Are you sure you have "members.Country" named correctly?

It would help to see the rest of the code, or atleast the full SQL statement portion.
0
gdemariaCommented:
Is this SQL Server?

The format for an "in" clause is this..


   and members.Country IN  ('#EuropeCountriesList#')



You are missing the (  ) and should use single quotes...

and members.Country IN  "#EuropeCountriesList#"
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
Ian WhiteOwner and FounderAuthor Commented:
Thanks that worked - even better to use  cfquerparam

<cfif IsDefined("form.country")
			and len(trim(form.country))
			and form.country is "europe">
     		and m.Country  IN
 (<cfqueryparam value="#EuropeCountryList#" cfsqltype="CF_SQL_VARCHAR" list="yes" >)
       

     
</cfif>

Open in new window

0
_agx_Commented:
cfqueryparam is definitely the way to go.  

The original wouldn't have worked anyway. Though it wouldn't throw an error, this statement:

     WHERE members.Country IN  ( '#EuropeCountriesList#' )

tells the db to looks for a single record that equals  'Albania,Andorra,Armenia,...' ,  instead of a country that equals one of the list elements. So it's the same as saying:

     WHERE members.Country  =  'Albania,Andorra,Armenia,...'

For it to work correctly, you'd need to quote each element individually:

  WHERE members.Country IN  (  'Albania', 'Andorra' , 'Armenia', '...'  )

But that causes new problems due to CF's auto escaping of quotes, so .. you're better off using <cfqueryparam list="true" ....>
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
ColdFusion Language

From novice to tech pro — start learning today.