>- see footer for list info -< You know what far be it from me to tell people how to write code - but that is one of my pet peeves - I cringe when I review code where people build up dynamic queries in variables using cfset... not picking on you at all Rich and I realize you wanted to use cfqueryparam but using savecontent for building up dynamic queries is just so much more readable....
Just my 2p Kola > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:dev- > [EMAIL PROTECTED] On Behalf Of Duncan Cumming > Sent: 21 December 2006 11:30 > To: [email protected] > Subject: Re: [CF-Dev] Dynamic Where Syntax > > >- see footer for list info -< > have you tried using cfsavecontent? because of the loop you could perhaps setup a > 1D array, store each individual 'whereclause' line in a separate cell in that array, using > cfsavecontent to save it, then loop through that array in your query. > > Duncan Cumming > New Media Developer > Customer Relations Management / Education > Fife Council > 700 4105 / 01592 414105 > > >>> [EMAIL PROTECTED] 21/12/2006 10:54 >>> > >- 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 -< > > > ******************************************************************** > ** > This email and any files transmitted with it are confidential and intended solely for the > use of the individual or entity to whom they are addressed and should not be > disclosed to any other party. > If you have received this email in error please notify your system manager and the > sender of this message. > > This email message has been swept for the presence of computer viruses but no > guarantee is given that this e-mail message and any attachments are free from > viruses. > > Fife Council > Tel: 08451 55 00 00 > ************************************************ > > _______________________________________________ > > 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 -<
