forming a SQL Where string with CFQUERYPARAM- Not working

[meensi]

<cfset strSQLWhere = ''/>

<cfset strSQLWhere  = strSQLWhere & " Platform_desc =" & "<cfqueryparam cfsqltype = 'cf_sql_varchar2' value = 'B300/350'/>  AND "  />

<cfset strSQLWhere  = strSQLWhere &  " Created_on_dt >= " & "<cfqueryparam cfsqltype = 'cf_sql_date' value = '10/25/2010'/>"/>

<cfquery name='test_Param' datasource = "testDB">

    SELECT

        Platform_ID,

        Platform_desc,

        Created_On_Dt

    FROm

        Test_Table

    WHERE 

   #preserveSinglequotes(strSQLWhere)#

</cfquery>

My Question is the above query is not working. Its always telling me that its missing expresssion or the parameters are not binded. I know that CFQUERYPARAM will work only if it inside the CFQUERY tag, but in my code, they are trying to build this strSQLWHERE string from different pages and connecting it here.

so I cant use the conditional statements inside the cfquery, which would make the query very big and complex. so i m trying to build the where clause and use it in my query.

Though the below query works fine.

<cfquery name='test_Param' datasource = "testDB">

    SELECT

        Platform_ID,

        Platform_desc,

        Created_On_Dt

    FROm

        Test_Table

    WHERE 

Platform_desc = <cfqueryparam cfsqltype = 'cf_sql_varchar2' value = 'B300/350'/> AND

Created_on_dt >= <cfqueryparam cfsqltype = 'cf_sql_date' value = '10/25/2010'/>

</cfquery>

I m adding CFQUERYPARAM in my queries to improve the performance; Any help would be really appreciated.

Thanks in advance!

[Owainnorth]

You need to put the conditional statement inside the cfquery, you can't just wrap up CFML into a string and hope it'll get parsed, as it won't.

[meensi]

Hmm, Thanks for the reply as I expected you are the first to reply.

In my case I have some 10 columns to be used in where clause, and I have nearly 10 to 15 conditions to build the STRSQLWHERE variable.

so you mean to say that forming a variable with CFQUERY PARAM wont work...

Instead I should use of all the conditional statement inside the query if i want to use CFQUERYPARAM.

[Owainnorth]

That is correct, technically there can be no more code involved in putting the statement inside your query than outside it.

<cfquery>

select something

from somewhere

where something is something

<cfif something eq something >

  AND something = <cfqueryparam />

<cfelse>

  AND somethingelse <cfqueryparam />

</cfif>

Etc. Just be very careful to check the debugging to make sure it's sending the right queries.

[Adam Cameron.]

If you're on CF9 you can use Query.cfc to include your parameter placeholders in the SQL string, and pass the parameter values separately.  No need for <cfqueryparam> tags at all.

--

Adam

[meensi]

Hi Adam,

Can you throw some light on query.cfc? I m using Coldfusion 9

[Adam Cameron.]

Fortunately it's all covered in the docs:

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0693d5dae123bcd28f6d-7ffb.html

--

Adam