Link to home
Start Free TrialLog in
Avatar of js_vaughan
js_vaughan

asked on

CFQUERYPARAM causing a Syntax error in SQL

The following query is producing the error: "Syntax error converting the varchar '10065,10066,10069' value to a column of data type int.

<cfquery name="updateImpressions" datasource="#datasource#">
  UPDATE tblHalls
  SET Impressions = Impressions + 1
  WHERE PropertyID IN(<cfqueryparam cfsqltype="cf_sql_longvarchar" value="#hallImpressions#">)
</cfquery>

It worked fine before when the query was used without CFQUERYPARAM like below

<cfquery name="updateImpressions" datasource="#datasource#">
  UPDATE tblHalls
  SET Impressions = Impressions + 1
  WHERE PropertyID IN(#hallImpressions#)
</cfquery>

The variable hallImpressions is simple a list of ID numbers strung together earlier on the page with the ListAppend() function.  Does anyone know a way to get this working and keep the CFQUERYPARAM or do I have to drop the CFQUERYPARAM?  This query is run quite often, so performance of the code is an issue.

Thanks
-Jeff
ASKER CERTIFIED SOLUTION
Avatar of mrichmon
mrichmon

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of js_vaughan
js_vaughan

ASKER

Thanks much mrichmon!

I was wondering why the cfsqltype="cf_sql_integer" didnt work for me in earlier test - I've never used the list attribute before, fixed it right up!
If you didn't have the list attribute then "1,2,3" is not a valid integer because it says oh there are commas in the number that do not make it an integer, but once you tell it that you are sending a list then it says oh okay I see that it is a list of integers separated by commas