ListQualify Problem

[thysonj]

I am trying to get the contents of a list enclosed with single quotes around each item. I have been using the list qualify and it seems to work to get the list correctly formatted but when I use the list in a query it comes out wrong. I will put the code below and the erro I am getting.

<CFSET qualifiedList=ListQualify(#strProductNames#,&quot;'&quot;)>
<cfoutput>
<cfquery name=&quot;getIDs&quot; datasource=&quot;#dbms#&quot;>
SELECT ProductId_pk, ProductId_fk, NIN, ProductName
FROM tblProducts INNER JOIN tblPackages ON ProductId_fk = ProductId_pk
WHERE (tblProducts.ProductName in (#qualifiedList#))
</cfquery>
</cfoutput>

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


[Microsoft][ODBC SQL Server Driver][SQL Server]Line 3: Incorrect syntax near 'TEST2'.


SQL = "SELECT ProductId_pk, ProductId_fk, NIN, ProductName FROM tblProducts INNER JOIN tblPackages ON ProductId_fk = ProductId_pk WHERE (tblProducts.ProductName in (''TEST2'',''Test1''))"

If I output the qualifiedlist it shows the list with one set of single quotes around each item.
notice it seems sql is putting its own set of single quotes around each item and that is the reason I am getting the error. Any ideas on what is going on and why?

 

[SidianConsulting]

ColdFusion is escaping single quotes in the query. You can either use the PreserveSingleQuotes() function around the variable when you set it, or use it directly in your SQL code inside your cfquery.

-Tek

 

[Silvano]

I have had similar problem; this solution worked for me:

<cfset url.fileID = &quot;142,454,228,668&quot;>

in the SQL:
...AND Orders.orderID IN (#ListQualify(url.fileID, &quot;&quot#);

the ListQualify() is used to avoid errors if the passed list have any extra commas (,142,,454,228,668); Sylvano
dsylvano@hotmail.com