Using IN keyword in data adapter.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all -

I've searched high and low to an answer to my question so I'm posting here as my last option

I'm trying to use the IN keyword in my WHERE clause of the data adapter to gather all IDs, separating them with commas, but I get an error that states that it could not convert a varchar value (due to the potential comma being there I needed to) to an int (which is what the ID field is defined as). Does anybody know what I can do to get around this issue or is there something I'm doing wrong?

Here's my query (@CustIDs can hold a value of 15, for example, or '15,16')

SELECT DISTINCT Customer.CustomerID, Customer.CompanyName, Customer.City + ', ' + Customer.State AS CityState FROM Customer INNER JOIN ClientServicesTable INNER JOIN CompanyServicesTable ON ClientServicesTable.CompanyServiceID = CompanyServicesTable.CompanyServiceID ON Customer.CustomerID = ClientServicesTable.ClientID WHERE (CompanyServicesTable.ServiceType = 'S' OR CompanyServicesTable.ServiceType

= 'B') AND (Customer.CustomerID = @ID OR Customer.CustomerID IN (@CustIDs)) ORDER BY Customer.CompanyNam

Much thanks
Mark
 
Hi Mark,

AFAIK there is no way. You would have to modify select statement.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Mark said:
Hello all -

I've searched high and low to an answer to my question so I'm posting here as my last option.

I'm trying to use the IN keyword in my WHERE clause of the data adapter to
Click to expand...
gather all IDs, separating them with commas, but I get an error that states
that it could not convert a varchar value (due to the potential comma being
there I needed to) to an int (which is what the ID field is defined as).
Does anybody know what I can do to get around this issue or is there
something I'm doing wrong??
Here's my query (@CustIDs can hold a value of 15, for example, or '15,16'):

SELECT DISTINCT Customer.CustomerID, Customer.CompanyName, Customer.City +
Click to expand...
', ' + Customer.State AS CityState FROM Customer INNER JOIN
ClientServicesTable INNER JOIN CompanyServicesTable ON
ClientServicesTable.CompanyServiceID = CompanyServicesTable.CompanyServiceID
ON Customer.CustomerID = ClientServicesTable.ClientID WHERE
(CompanyServicesTable.ServiceType = 'S' OR CompanyServicesTable.ServiceType
= 'B') AND (Customer.CustomerID = @ID OR Customer.CustomerID IN
Click to expand...
(@CustIDs)) ORDER BY Customer.CompanyName
 
Right. We've seen this before. Nope, you can't provide a Parameter to an IN
expression. It has to be present when the SP is created. There is a
work-around, but it's ugly (using EXEC SQL).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Mark said:
Hello all -

I've searched high and low to an answer to my question so I'm posting here as my last option.

I'm trying to use the IN keyword in my WHERE clause of the data adapter to
Click to expand...
gather all IDs, separating them with commas, but I get an error that states
that it could not convert a varchar value (due to the potential comma being
there I needed to) to an int (which is what the ID field is defined as).
Does anybody know what I can do to get around this issue or is there
something I'm doing wrong??
Here's my query (@CustIDs can hold a value of 15, for example, or '15,16'):

SELECT DISTINCT Customer.CustomerID, Customer.CompanyName, Customer.City +
Click to expand...
', ' + Customer.State AS CityState FROM Customer INNER JOIN
ClientServicesTable INNER JOIN CompanyServicesTable ON
ClientServicesTable.CompanyServiceID = CompanyServicesTable.CompanyServiceID
ON Customer.CustomerID = ClientServicesTable.ClientID WHERE
(CompanyServicesTable.ServiceType = 'S' OR CompanyServicesTable.ServiceType
= 'B') AND (Customer.CustomerID = @ID OR Customer.CustomerID IN
Click to expand...
(@CustIDs)) ORDER BY Customer.CompanyName
 
Hi Bill -

As long as it doesn't require creation of tables/stored procedures, since I don't want to give the ability for a general use to do that, I'll handle the ugly way if need be. If you wouldn't mind sharing the other route suggested, since I didn't locate any T-SQL information to go off of, I would greatly appreciate it as I'll use that option. Is it referring to this link below?

http://www.algonet.se/~sommar/dynamic_sql.html

Thank you all for your knowledge.

Mark

----- William (Bill) Vaughn wrote: -----

Right. We've seen this before. Nope, you can't provide a Parameter to an IN
expression. It has to be present when the SP is created. There is a
work-around, but it's ugly (using EXEC SQL).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Mark said:
Hello all -
Click to expand...
gather all IDs, separating them with commas, but I get an error that states
that it could not convert a varchar value (due to the potential comma being
there I needed to) to an int (which is what the ID field is defined as).
Does anybody know what I can do to get around this issue or is there
something I'm doing wrong??', ' + Customer.State AS CityState FROM Customer INNER JOIN
ClientServicesTable INNER JOIN CompanyServicesTable ON
ClientServicesTable.CompanyServiceID = CompanyServicesTable.CompanyServiceID
ON Customer.CustomerID = ClientServicesTable.ClientID WHERE
(CompanyServicesTable.ServiceType = 'S' OR CompanyServicesTable.ServiceType
 
Yes, my "ugly" solution is explained in that article.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Mark said:
Hi Bill -

As long as it doesn't require creation of tables/stored procedures, since
Click to expand...
I don't want to give the ability for a general use to do that, I'll handle
the ugly way if need be. If you wouldn't mind sharing the other route
suggested, since I didn't locate any T-SQL information to go off of, I would
greatly appreciate it as I'll use that option. Is it referring to this link
below?
 
Back
Top