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

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
0
js_vaughan
Asked:
js_vaughan
  • 2
1 Solution
 
mrichmonCommented:
cf_sql_longvarchar should be cf_sql_integer

Then you should have the list set to yes

<cfquery name="updateImpressions" datasource="#datasource#">
  UPDATE tblHalls
  SET Impressions = Impressions + 1
  WHERE PropertyID IN(<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#hallImpressions#">)
</cfquery>
0
 
js_vaughanAuthor Commented:
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!
0
 
mrichmonCommented:
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
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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