Help with the IN() function

[Pooch398]

Hello I am working on a query where I am trying to SELECT a record using the IN() function:

<cfquery name="getAddresses" datasource="#application.datasource#">
    SELECT ID, COMP_NAME, ADDRESS1_1, CITY1, STATE1, ZIP1, PHONE1_1, FAX1_1, CATEGORY
    FROM Vendors
    WHERE #id# IN (CATEGORY)
    ORDER BY COMP_NAME
</cfquery>

id is a cfparam passed in on the url and CATEGORY is a varchar.

I am getting the following error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '1,2,9' to a column of data type int.

Any help would be appreciated, I have tried everything!

[hariharanmca]

Hello I am working on a query where I am trying to SELECT a record using the IN() function:

<cfquery name="getAddresses" datasource="#application.datasource#">
    SELECT ID, COMP_NAME, ADDRESS1_1, CITY1, STATE1, ZIP1, PHONE1_1, FAX1_1, CATEGORY
    FROM Vendors
    WHERE #id# IN (CATEGORY)
    ORDER BY COMP_NAME
</cfquery>

id is a cfparam passed in on the url and CATEGORY is a varchar.

I am getting the following error:

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Syntax error converting the varchar value '1,2,9' to a column of data type int.

Any help would be appreciated, I have tried everything!

In() function should also contain select query
like

SELECT ID, COMP_NAME, ADDRESS1_1, CITY1, STATE1, ZIP1, PHONE1_1, FAX1_1, CATEGORY
    FROM Vendors
    WHERE #id# IN (Select Id_Category from CATEGORY)
    ORDER BY COMP_NAME

[Afterlife]

Hello I am working on a query where I am trying to SELECT a record using the IN() function:

<cfquery name="getAddresses" datasource="#application.datasource#">
    SELECT ID, COMP_NAME, ADDRESS1_1, CITY1, STATE1, ZIP1, PHONE1_1, FAX1_1, CATEGORY
    FROM Vendors
    WHERE #id# IN (CATEGORY)
    ORDER BY COMP_NAME
</cfquery>

id is a cfparam passed in on the url and CATEGORY is a varchar.

I guess you're using ColdFusion? (the cf... and MacroMedia hint to that)
I don't have any experience or knowledge of CF, but #id# (probably an integer?) will never be part of a varchar field CATEGORY. The types just won't match.
And why check if the #id# is part of the CATEGORY field in the same record? Doesn't sound logical or useful to me...

[cfEngineers]

Dont you mean
WHERE CATEGORY IN (#id#)

Hope that helps!