CFQUERY Problem

[xenos1000]

Hi,

I have a problem with the below CFQuery. I am stuck - please help!!!

**********
Here is the query

<!--- get this cat, all above and all one level below --->

<cfset cattree = &quot;&quot;>

<cfif IsDefined(&quot;ID&quot>

<cfset thisone = result.category>

<cfelse>

<cfset thisone = category>

</cfif>

<cfquery name=&quot;allbelow&quot;
DBTYPE=&quot;dynamic&quot;
maxrows = &quot;1&quot;
connectstring = &quot;Driver={SQL Server};Server=sql-001.affinity.com;Database=db70053;Uid=collec12;Pwd=7sychedelic;&quot;>

SELECT ParentID, ID FROM ArticleCategories WHERE ParentID = #thisone#

</cfquery>


*******************
The error I receive is :

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (100:1) to (103:115).


Any help would be really appreciated.

Cheers
NICK

 

[DeZiner]

Can you set cattree to x or a space?
<cfset cattree = &quot; &quot;> DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[xenos1000]

Hey Deziner,

I have changed cattree to &quot; &quot; - no problem - but I still get the same error.....

Thanx
NICK

 

[CFDude]

It looks like there is not a value for #thisone# getting passed. Which value is getting passed -- the result.category or category (is the ID defined?)

right before the cfquery, do a cfoutput on &quot;category&quot; and &quot;result.category&quot; -- do a cfabort right after that to prevent the query from trying to execute. I think the problem is with the variable that is getting passed.

Also, if possible, turn on the debugging in CFAdmin to show the SQL query to see what the actual query is getting sent.

 

[pcorreia]

If #thisone# might sometimes be an empty string, try changin your SQL statement to:

    SELECT ParentID, ID 
    FROM ArticleCategories 
    WHERE ParentID <cfif Len(thisone) LT 1>= '#thisone#'<cfelse>IS NULL</cfif>

 

[xenos1000]

Thanks for the help everyone, I shall try the new suggestions.

Cheers
NICK