469,890 Members | 1,541 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

passing a list as a parameter to a TableAdapter Fill method

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
3 12465
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
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

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.

Similar topics

reply views Thread by ZoeNeedsHelp | last post: by
17 posts views Thread by John | last post: by
4 posts views Thread by tg.foobar | last post: by
5 posts views Thread by Programatix | last post: by
3 posts views Thread by jrogers.sw | last post: by
9 posts views Thread by Chris | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.