passing an array as a parameter to a sqlDataAdapter

I have a dataadapter in a data class with a sql statement like this.
Select * from Customers where CustomerID = @ CustomerID

In my Code I am passing the parameter to the dataadapter like this.
OrdersData.sqlDaInvoiceByID.SelectCommand.Parameters["@CustomerID"].Value = paramValue;

I have an array with multiple customerIDs in it.  is there anyway to pass this array as the Value.  I tried just setting paramValue = myArray put it said input sting not in correct format.

Any ideas?
lyptusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jlachCommented:
I do not know how to do that, but i do know that you can create your sql at run time, and just execute the command... like

string ids = "0";
foreach(string str in Collection.Item) {
   ids += "," + str;
}
string sql = "select * from Customers where CustomerID in (" + ids + ")";

Then execute that sql..
0
lyptusAuthor Commented:
The command is in a class which I don't have access to.
0
jlachCommented:
Then no, i dont think there is way to do this.. i dont use the whole adapter thing for paramaters and such.. do you have access to inherit anything from the class?
0
aponcealbuerneCommented:
Hi

If the parameter is for example some string like 'a,b,c,d' and you want to use that values in the stored procedure then.
I think you have to choices.
a) To transform the parameter in the stored procedure by using substrings, or pathindex function in order to extract the elements of the array. In this way you could use some separators like the character '~' or any other separator character.

b) Other way to do this is by using directly the parameter, something like this.

Procedure x ...

@param1 varchar(100)

begin
 ... something ...

  eval('Select Field From Table Where Id in (''' + @param1 + ''' ) ')

.. something...

end

Hope helps.
0
doryllisCommented:
Looking at the whole thing, if you do not have access to the sql command to change it from an equal sign to a LIKE or IN, then it won't help to do just the list of params into a string.  

If you can't change the query, one alternative is to call the sql query for each item in your array and gather those results together.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.