We help IT Professionals succeed at work.

Query binding Error when building an or statement in a where clause

onaled777 asked

I am trying to build a where clause so i can use cfqueryparam when getting a query to filter by certain product IDs

I get a list of productids to start with, then I try to build a where clause with OR statements and store the string in the variable ProductWhereClause

If I should print out the value for the variable ProductWhereClause, paste it into the actual query I get results and run it in SQL Server Management Studio, I get the results

If I use the variable productWhereClause in the query however, then I get an error about invalid parameter bindings.

The details are in the code snippet

Can anyone provide a solution for this?
<!--- This is how I build where clause for the products ---->

			<cfloop list="#arguments.P_ID#" index="listIndex">
				<cfset listCounter = listCounter + 1>		
				<cfset productWhereClause = productWhereClause & "p.Product_ID = <cfqueryparam value='#listIndex#' cfsqltype='cf_sql_integer'>" >
				<cfif listCounter neq ListLen(arguments.P_ID)>
					<cfset productWhereClause = productWhereClause & ' OR ' >

<!--- This is how I insert it in the where clause --->

							   AND PreserveSingleQuotes(#ProductWhereClause#)

<!---This is the error that is being received--->

 	 [Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s). 

<!--- This is the string that is printed out in my log file with the statement: <cflog log="application" text="#ProductWhereClause#">

AND ( p.Product_ID = <cfqueryparam value='81' cfsqltype='cf_sql_integer'> OR p.Product_ID = <cfqueryparam value='31' cfsqltype='cf_sql_integer'> OR p.Product_ID = <cfqueryparam value='34' cfsqltype='cf_sql_integer'> OR p.Product_ID = <cfqueryparam value='80' cfsqltype='cf_sql_integer'> OR p.Product_ID = <cfqueryparam value='32' cfsqltype='cf_sql_integer'> OR p.Product_ID = <cfqueryparam value='85' cfsqltype='cf_sql_integer'> )

Open in new window

Watch Question


Just some more details on the error in case its helpful

Type         Database

queryError       [Macromedia][SQLServer JDBC Driver]Invalid parameter binding(s).

(param 1) = [type='IN', class='java.sql.Timestamp', value='2010-06-01 00:00:00.0', sqltype='cf_sql_timestamp']
 , (param 2) = [type='IN', class='java.sql.Timestamp', value='2010-06-11 23:59:59.998', sqltype='cf_sql_timestamp']
 , (param 3) = [type='IN', class='java.lang.Integer', value='81', sqltype='cf_sql_integer']
 , (param 4) = [type='IN', class='java.lang.Integer', value='31', sqltype='cf_sql_integer']
 , (param 5) = [type='IN', class='java.lang.Integer', value='34', sqltype='cf_sql_integer']
 , (param 6) = [type='IN', class='java.lang.String', value='80', sqltype='cf_sql_integer']
 , (param 7) = [type='IN', class='java.lang.String', value='32', sqltype='cf_sql_integer']
 , (param 8) = [type='IN', class='java.lang.String', value='85', sqltype='cf_sql_integer']
 , (param 9) = [type='IN', class='java.sql.Timestamp', value='2010-06-01 00:00:00.0', sqltype='cf_sql_timestamp']
 , (param 10) = [type='IN', class='java.sql.Timestamp', value='2010-06-11 23:59:59.998', sqltype='cf_sql_timestamp']
 , (param 11) = [type='IN', class='java.lang.String', value='81', sqltype='cf_sql_integer']
 , (param 12) = [type='IN', class='java.lang.String', value='31', sqltype='cf_sql_integer']
 , (param 13) = [type='IN', class='java.lang.String', value='34', sqltype='cf_sql_integer']
 , (param 14) = [type='IN', class='java.lang.String', value='80', sqltype='cf_sql_integer']
 , (param 15) = [type='IN', class='java.lang.String', value='32', sqltype='cf_sql_integer']
 , (param 16) = [type='IN', class='java.lang.String', value='85', sqltype='cf_sql_integer']
Bhavesh ShahLead Analyst
Top Expert 2010

you can only use <cfqueryparam> tag inside the body of <cfquery> tag. you can't use it the way you do.

instead, why don;t you build the where clause directly in your query using IN operator?:

<cfquery ...>
WHERE ... AND p.Product_ID IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#arguments.P_ID#">)

You need to find out if it is a problem with the query or how the query is being output.

I would try it without CFquery param, because then the error should show the actual query being executed, not just a list of parameters.

The fact that the parameters are being shown in the error message suggests that cfqueryparam is working. So if that is the case you just need to find out where the error in the query is.

I agree that using a list is a more efficient way to write the query, but it shouldn't matter. Your error lists timestamps, but the query you showed does not. In fact, there are a whole lot more parameters listed than you have displayed in your example - with repeating input. Are you repeating the WHERE clause unintentionally? Or do you really have 16 WHERE clauses? This could be the source of your problems.

I would first try not using cfqueryparam (for now), so you can see the actual query, and then try removing all of your WHERE parameters and adding them back one by one until you get the error.