By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,694 Members | 1,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,694 IT Pros & Developers. It's quick & easy.

passing a list as a parameter to a TableAdapter Fill method

P: n/a
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!!!

Feb 7 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Crazy,

You can't specify a comma delimited list as the value of a parameter.

Here is an article that discusses options:

http://www.sommarskog.se/arrays-in-sql.html

Kerry Moorman
"Crazy Cat" wrote:
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!!!

Feb 8 '06 #2

P: n/a
CMM
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 + ")"
Feb 8 '06 #3

P: n/a

Kerry Moorman wrote:
Crazy,

You can't specify a comma delimited list as the value of a parameter.

Here is an article that discusses options:

http://www.sommarskog.se/arrays-in-sql.html
Thanks.

Kerry Moorman
"Crazy Cat" wrote:
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!!!


Feb 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.