Ditto Steve's question. You're trying to use cfqueryparam outside of a
cfquery block. Coldfusion is just going to see #sqlStatement# as a block
of literal text, so it will pass
WHERE firstName LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%bar%" />
into the DB exactly as written. And SQL will complain of course. You can
successfully build dynamic queries if you do it within the cfquery block,
like:
<cfquery>
SELECT otherID FROM thirdTable
WHERE firstName LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%bar%" />
<cfif someCondition>
OR lastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar"
value="%bar%" />
</cfif>
</cfquery>
------
Bill.
On 23 January 2013 07:57, Steve Milburn <[email protected]> wrote:
>
> If you just output the sqlStatement variable, how does it look? Also, any
> particular reason you are not just building your sql statement inside the
> <cfquery> tags?
>
>
> On Tue, Jan 22, 2013 at 3:33 PM, Tom McNeer <[email protected]> wrote:
>
> >
> > Hi,
> >
> > I need to build up a complex dynamic query statement. I have built
> methods
> > to add queryParam statements, and built up valid SQL.
> >
> > If I do:
> >
> > <cfset sqlStatement = "SELECT DISTINCT tableName.ID FROM tableName
> > LEFT OUTER JOIN secondTable ON tableName.ID=secondTable.fkID
> > WHERE ( tableName.clientID = 'D35DAF11-DCB2-4341-B26C-0D31325CD51B' AND
> > tableName.otherID IN
> > (SELECT otherID FROM thirdTable WHERE (firstName LIKE
> > <cfqueryparam cfsqltype="cf_sql_varchar" value="%bar%" /> OR lastName
> LIKE
> > <cfqueryparam cfsqltype="cf_sql_varchar" value="%bar%" />))
> > )" />
> >
> > And then do:
> >
> > <cfquery name="foo">
> > #sqlStatement#
> > </cfquery>
> >
> >
> > I get an error from the SQL Server driver that points to the first part
> of
> > the client ID - "Incorrect syntax near 'D35DAF11'" and a "nextException"
> > that says "Incorrect syntax near '<'."
> >
> > Yet if I paste the above statement directly into the cfquery tag, it runs
> > perfectly.
> >
> > Can anyone suggest where I'm going wrong, please? I suppose it could be a
> > single quotes problem of some sort. But certainly preserveSingleQuotes
> has
> > no affect. And if I wrap the first varchar (the clientID) in a queryParam
> > statement, it simply eliminates the first error and immediately shows the
> > "Incorrect syntax near '<'." error.
> >
> > --
> > Thanks,
> >
> > Tom
> >
> > Tom McNeer
> > MediumCool
> > http://www.mediumcool.com
> > 1735 Johnson Road NE
> > Atlanta, GA 30306
> > 404.589.0560
> >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:354010
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
