- see footer for list info -<
You can't use cfqueryparam outside of a query, and coldfusion tags wont work
inside a coldfusion string.
Instead of using cfsets, just put the code directly inside the cfquery tags.
eg:
<cfquery>
SELECT stuff
FROM whereever
WHERE 1=0
<cfloop index="i" from="1" to="10">
OR (centreid = <cfqueryparam value="#i#" cfsqltype="cf_sql_numeric">
AND cplname = <cfqueryparam value="#attributes["cplname_#i#"]#"
cfsqltype="cf_sql_varchar">
)
</cfloop>
</cfquery>
On 12/21/06, RichL <[EMAIL PROTECTED]> wrote:
>- see footer for list info -<
Guys
I am trying to build up a where clause dynamically as follows:
<cfset whereClause = 'Where 1 = 0 or '>
loop with i as a numeric index value...
<cfset whereClause = whereClause & '(centreid = <cfqueryparam
cfsqltype="cf_sql_numeric" value="#i#"> and cplname = <cfqueryparam
cfsqltype="cf_sql_varchar" value="#attributes["cplname_#i#"]#">)'>
end loop
I am running a query as follows: select * from x #whereclause#
The SQL is falling over with 'Line 1: Incorrect syntax near '<'.' but
on the same error message it outputs the SQL being run as:
select * from rik_viewCPLReferenceData Where 1 = 0 or (centreid =
<cfqueryparam cfsqltype="cf_sql_numeric" value="78"> and cplname =
<cfqueryparam cfsqltype="cf_sql_varchar" value="BSc Accounting for
Management">)
which is exactly what I want and if I copy and paste this and run it
directly, all is well.
my suspicion is that this SQL being shown on the screen is correct
after it has been rendered to screen but not at the time CF picks it
up and passes it to SQL Server?
I have messed about with htmleditformat, htmlcodeformat, urldecode,
urlencodedformat, de, evaluate, using '<' '>' both within the
initial where clause creation and the insertion of the #whereClause#
variable in the query and can't get anything to work.
Can anybody shed some light on this or suggest a better way to do it
please?
Many thanks
--
Rich
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
>- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
>- Lists hosted by www.Gradwell.com -<
>- CFdeveloper is run by Russ Michaels, feel free to volunteer your help
-<
--
\ \
Peter Boughton
blog.bpsite.net
/ /
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- cfdeveloper Hosting provided by www.cfmxhosting.co.uk -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
