>- see footer for list info -<
Don't think you can build up the sql externally whilst using cfqueryparam
like that.
Try something like
<cfquery>
select * from rik_viewCPLReferenceData Where 1 = 0 or
loop with i as a numeric index value...
(centreid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#i#">
and cplname = <cfqueryparam cfsqltype="cf_sql_varchar"
value="#attributes["cplname_#i#"]#">
)
end loop
</cfquery>
Cheers
Robin
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of RichL
Sent: 21 December 2006 10:54
To: cfdev
Subject: [CF-Dev] Dynamic Where Syntax
>- 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 -<
_______________________________________________
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 -<
