Pass multiple values to a dataset parameter

[Mighty]

I have set up a dataset in Visual Studio with one parameter in the Select statement. My dataset stores a list of parts which match the lot number passed into the parameter. The where clause in my dataset is as follows:

where [Lot Number] IN (?)

The reason I have the "in" statement is that I may pass in multple lot numbers to the query. The problem I have is that the dataset will load data if I only pass one lot number. However, if I pass more than one lot number (e.g. "lot1,lot2,lot3") it doesn't return any records. I am assuming that is because it treats the whole thing as one string and doesn't find a match.

If I change the query to the following:

where [Lot Number] IN (?,?,?,?,?)

and then pass in 5 lot numbers as separate parameters it works fine. My problem is that I am not going to know until runtime how many lot numbers I need to pass to the query.

Is there any way around that. I am using the DataSet to bind to a datareader control.

Mighty

 

[Andrzejek]

where [Lot Number] IN ("lot1", "lot2", "lot3")

Have fun.

---- Andy

 

[Mighty]

That is not going to fix it either. My problem is that I don't know how many lots I will need to pass to the query

Mighty

 

[Andrzejek]

How do you collect your lots? Do you read it from a list? Do you keep it in a String? Variant? Array?



Have fun.

---- Andy

 

[Mighty]

I run a query to get a list of the lots which I store as a string and then try and pass that string to the dataset as the parameter.

Mighty

 

[Andrzejek]

I run a query to get a list of the lots

Nice, you are half way there

If your query "to get a list of the lots" looks something like:

[blue]Select Lots From SomeTable Where ....[/blue]

This way you already have a list of lots.
You can just simple use it in your IN statement:

where [Lot Number] IN ([blue]Select Lots From SomeTable Where ....[/blue])

Wouldn't it make you happy?

Have fun.

---- Andy

 

[Mighty]

Tried that but the query takes an age to run - even though it takes mere seconds when I do it seperately. Probably just need to take a closer look at it.

Mighty