thanx for people's interest and help. I think Steve might be on the right track here with the extra single quotes within <CFQUERY>
using <CFOUTPUT> to display the SQL just before the <CFQUERY> SELECT * FROM tArticle WHERE tArticle.idCategory = 2 AND tArticle.titleArticle LIKE '%q%' which looks - and works (within MSAccess) - fine. the SQL as displayed by the error message executing the <CFQUERY> SELECT * FROM tArticle WHERE tArticle.idCategory = 2 AND tArticle.titleArticle LIKE ''%q%'' echoed in the error message Syntax error in query expression 'tArticle.idCategory = 2 AND tArticle.titleArticle LIKE ''%q%'''. (note the extra quotes) While I am using *PreserveSingleQuotes(FORM.txtTitle)* it's only on the reading of text values - not over the whole strSQL. as to why do it this way? having so much conditional logic within a <CFQUERY> tag (and this is a simple example) looks like crap and is hard to follow. I'd rather encapsulate such stuff in a function that takes in the variables (whether it has a value or not) and returns the correct SQL to use. This is especially useful where the number of parameters to be used for the query changes and you're working with a db that can't handle optional parameters (eg: JET/Access) doing this with ASP is piss easy. all SQL is just a text command to be executed by the connection object. I think what I really need is a better understanding on how <CFQUERY> works - under the hood stuff. One thing I'm finding challenging is that <CFQUERY> ain't MS's ADO... any ideas? thanx barry.b PS: isn't PreserveSingleQuotes() just replace() tailored for quotes? -----Original Message----- From: Taco Fleur [mailto:[EMAIL PROTECTED] Sent: Monday, 23 June 2003 8:05 AM To: CFAussie Mailing List Subject: [cfaussie] RE: <CFQUERY > SQL as a string command Could you mail us the final sql statement generated? i.e. can we see #strSQL# evaluated? Taco -----Original Message----- From: Beattie, Barry [mailto:[EMAIL PROTECTED] Sent: Sunday, 22 June 2003 10:21 PM To: CFAussie Mailing List Subject: [cfaussie] <CFQUERY > SQL as a string command Hi all. I'm using some of my ASP tricks and trying to build a SQL statement as a string (eg: with conditional WHERE clauses) and executed with <CFQUERY> I'm getting "Syntax error in query expression" although the SQL statement itself looks - and works - fine. To make the code (below) work, I've had to move the WHERE condition building to within the CFQUERY tag with the rest of the SQL This works (sort of) in this simple example but it's not the way I like to work when things get more complicated. is doing it this way (a string for a SQL command) impossible in CFMX? eg: <cfquery name="qArticle" datasource="weblog"> #strSQL# </cfquery> any ideas, thoughts, etc appreciated cheers barry.b (PS: yeah I know about SP's) <!--- the code I'm using ---> <cfparam name="sqlWhereClause" default="" type="string"> <cfparam name="FORM.selCategory" default="2"> <cfparam name="FORM.txtTitle" default="q"> <cfif FORM.selCategory GREATER THAN 0><!--- category IS selected ---> <cfif trim(FORM.txtTitle) eq ""><!--- title clue NOT selected ---> <cfset sqlWhereClause = " WHERE tArticle.idCategory = #form.selCategory#"> <cfelse> <!--- title clue IS selected ---> <cfset sqlWhereClause = " WHERE tArticle.idCategory = #form.selCategory# AND tArticle.titleArticle LIKE '%#PreserveSingleQuotes(FORM.txtTitle)#%'"> </cfif> <cfelse> <!--- category NOT selected ---> <cfif trim(FORM.txtTitle) neq ""><!--- title clue IS selected ---> <cfset sqlWhereClause = " WHERE tArticle.titleArticle LIKE '%#PreserveSingleQuotes(FORM.txtTitle)#%'"> </cfif> </cfif> <cfset strSQL = "SELECT * FROM tArticle " & sqlWhereClause> <!--- check how it looks ---> <cfoutput>#strSQL#</cfoutput> <!--- run it ---> <cfquery name="qArticle" datasource="weblog"> #strSQL# </cfquery> --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ ---------------------------------------------------------------------------- This email, together with any attachments, is intended for the named recipient(s) only and may contain privileged and confidential information. If received in error, please inform the sender as quickly as possible and delete this email and any copies from your computer system network. If not an intended recipient of this email, you must not copy, distribute or rely upon it and any form of disclosure, modification, distribution and/or publication of this email is prohibited. Unless stated otherwise, this email represents only the views of the sender and not the views of the Queensland Government. ---------------------------------------------------------------------------- --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/ --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
