473,327 Members | 2,016 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 13071
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: ZoeNeedsHelp | last post by:
I have an asp.net application in C# and i'm trying to call a stored procedure with a parameter. When i run the web app i get this message: Procedure 'sp_loadCustByID' expects parameter...
1
by: lolomgwtf | last post by:
I have a managed C++ method that wraps unmanaged code and creates a managed object holding data retrieved form an unmanged one. I want create an instance of this managed class in C#, pass it to...
2
by: lolomgwtf | last post by:
I have a managed C++ method that wraps unmanaged code and creates a managed object holding data retrieved form an unmanged one. I want create an instance of this managed class in C#, pass it to...
17
by: John | last post by:
Hi I have a datadapter with the following SQL; SELECT ID, Company, Status, CompanyType FROM Companies WHERE (@Status IS NULL or @Status = Status) When I try to fill like...
4
by: tg.foobar | last post by:
i'd like to do the following, but i don't think it's possible. can you help me find a way to do this, or maybe a better way to write the code? I have a list of items that need to be modified...
5
by: Programatix | last post by:
Hi, Have anyone ever benchmark the TableAdapter performance compared to DataTable's Load method? I found out that the DataTable's Load method is about 10x faster than TableAdapter's Fill method....
3
by: Mike | last post by:
Dear Group, When I add a DataTable to a Typed Dataset, and a TableAdapter to the DataTable, I am able to create methods to send updates directly to the database (GenerateDBDirectMethods),...
3
by: jrogers.sw | last post by:
I am using an objectdatasource with a .Net 2.0 ASP page. The SQL for the tableadapter needs to use the IN operator as in WHERE job_id in (111, 222, 333, 444, 555) Job_id is a DBType Decimal and...
9
by: Chris | last post by:
Ok, so I have this sub I wrote, and I create a new instance of a UserControl: ctrlAPs tempctrl = new ctrlAPs(); Now, I would like to be able to use this sub I wrote for more than one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.