Query used to work

[mikey999]

I have this one query it used to work in cfm 5.0 but now gives an error in cfm
6.0 <b>The query is</b> <CFQUERY NAME='ParentOrganization'
datasource='mydatasource'> SELECT Organizations.OrganizationName,
LinkAdv.LinkAdvID, LinkAdv.Custom1, LinkAdv.DateCreated,LinkAdv.configID FROM
LinkAdv INNER JOIN Organizations ON LinkAdv.OrgID = Organizations.OrgID WHERE
LinkAdv.LinkAdvID = #GetParentID.Custom13# AND LinkAdv.StatusID = 'Approved'
AND DesignatedPages NOT LIKE '%UNGREENBIZ%' AND (LinkAdv.configID = 14 or
LinkAdv.configID = 12) <CFIF #ConfigIDGooD# is '14'>AND LinkAdvID NOT IN
(#GetData.LinkAdvID#)</cfif> Order By Custom1 </CFQUERY> <b>I get this
error</b> error Error Executing Database Query. [Macromedia][SQLServer JDBC
Driver][SQLServer]Incorrect syntax near the keyword &amp;apos;AND&amp;apos;.
The error occurred in
D:\web\customers\greenbiz\includes\Refrence_OtherL inks.cfm: line 37 Called from
D:\web\customers\greenbiz\reference\events_record. cfm: line 222 Called from
D:\web\customers\greenbiz\includes\Refrence_OtherL inks.cfm: line 37 Called from
D:\web\customers\greenbiz\reference\events_record. cfm: line 222 35 : AND
(LinkAdv.configID = 14 36 : or LinkAdv.configID = 12) 37 : <CFIF
#ConfigIDGooD# is '14'>AND LinkAdvID NOT IN (#GetData.LinkAdvID#)</cfif> 38 :
Order By Custom1 39 : </CFQUERY>
--------------------------------------------------------------------------------
SQL SELECT Organizations.OrganizationName, LinkAdv.LinkAdvID,
LinkAdv.Custom1, LinkAdv.DateCreated,LinkAdv.configID FROM LinkAdv INNER JOIN
Organizations ON LinkAdv.OrgID = Organizations.OrgID WHERE LinkAdv.LinkAdvID =
AND LinkAdv.StatusID = 'Approved' AND DesignatedPages NOT LIKE '%UNGREENBIZ%'
AND (LinkAdv.configID = 14 or LinkAdv.configID = 12) Order By Custom1
DATASOURCE sunmaster_new VENDORERRORCODE 156 SQLSTATE HY000 The sql
looks right no??

[paross1]

I don't see anything wrong either, so you might troubleshoot by removing parts
of the query, like the CFIF part, to see if you can isolate the exact phrase
that is causing the error.

Also, you might consider replacing

AND (LinkAdv.configID = 14 or LinkAdv.configID = 12)
with
AND LinkAdv.configID IN (12, 14)

as it may be a little more efficient, and/or readable. (There really isn't
anything wrong with the way that you did it, though.)

Phil

[The ScareCrow]

It should not make a difference, but try putting a space after each clause.
Sometimes if you have

Select ColumnFrom TableWhere Column = value

You will get an error, now the driver should do this (replace a space for the
CR) but maybe it's not.

Ken

[mikey999]

I tried making the changes you two suggested but this did not fix this issue.
Here is the web site page the error is happening on in case this shines any
light on this
[url]http://www.greenbiz.com/reference/events_record.cfm?LinkAdvID=63234[/url]

[Tulsa]

My guess is that you need single quotes around the 12 and 14 in (LinkAdv.configID = 14
or LinkAdv.configID = 12)

[paross1]

I visited your link and looked at the error message. You have a problem here:
LinkAdv.LinkAdvID = #GetParentID.Custom13# It looks to me like
#GetParentID.Custom13# is null or blank.

Attached is your query as it showed at the bottom of the error message. Notice
where it says WHERE LinkAdv.LinkAdvID = AND. Fix #GetParentID.Custom13# and
you will probably be OK.

Phil



SELECT Organizations.OrganizationName, LinkAdv.LinkAdvID, LinkAdv.Custom1,
LinkAdv.DateCreated,LinkAdv.configID FROM LinkAdv INNER JOIN Organizations ON
LinkAdv.OrgID = Organizations.OrgID WHERE LinkAdv.LinkAdvID = AND
LinkAdv.StatusID = 'Approved' AND DesignatedPages NOT LIKE '%UNGREENBIZ%' AND
LinkAdv.configID IN (12, 14) Order By Custom1