You can't pass cfqueryparam statements to the DB - they are CF statements. They won't be evaluated by CF either, way you are using them here.
On 12/21/06, RichL <[EMAIL PROTECTED]> wrote: > 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 > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264700 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
