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_longvarc har" 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
<cfquery name="updateImpressions" datasource="#datasource#">
UPDATE tblHalls
SET Impressions = Impressions + 1
WHERE PropertyID IN(<cfqueryparam cfsqltype="cf_sql_longvarc
</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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
I was wondering why the cfsqltype="cf_sql_integer"