passing a list as a parameter to a TableAdapter Fill method

  • Thread starter Thread starter Crazy Cat
  • Start date Start date
C

Crazy Cat

I've created a TableAdapter which implements the following SQL
statement
SELECT MSC.MSC_NAME AS NAME, Capacity.Capacity AS CAPACITY
FROM Capacity INNER JOIN
MSC ON Capacity.MSC_KEY = MSC.MSC_KEY
WHERE (MSC.ED_MARKET_KEY in ( @ED_MARKET_KEY))

the type of the parameter @ED_MARKET_KEY is a string (varchar(40) in
the database). When @ED_MARKET_KEY is a single value everything works
fine (such as 5834934, however when @ED_MARKET_KEY is a comma delimited
list I get no matches.

What gives? I've tried putting the individual keys between single
quotes so that the list is '5328923','2349328' but neither
5328923,2349328 or '5328923','2349328' works. The
statement works fine with either list in SQL Server 2005 Standard
(which is the database I'm targeting).

Please help!!!
 
Yeah I've run into this problem before (with SQL Server 2000). You'll have
to take out the @parameter and do the classic good ol' SQL concatenation.
"where bla in (" + myDelimitedList + ")"
 
Back
Top