• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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#" 

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 White
Ian White
1 Solution
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.
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#"
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" >)


Open in new window

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" ....>
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now