asked on

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

Robert Sherman

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.

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ian White

ASKER

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

_agx_

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