SQL Parameter problem using WHERE ...IN

  • Thread starter Thread starter Alex Shirley
  • Start date Start date
A

Alex Shirley

Hi

I've got a SQL Server/ASP.Net(VB) problem.

This statement works when I run it on SQL Server as a standard query:
SELECT blagh FROM blagh WHERE blagh.blaghID IN (1,2,3,4,5)

If I change the SQL statement to read:
SELECT blagh FROM blagh WHERE blagh.blaghID IN (@blaghID)

And set in my ASP.Net code this parameter:
SqlMyStatement.Parameters("@blaghID").Value = "1"

This will work fine as well.

But, if I set up this parameter instead:
SqlMyStatement.Parameters("@blaghID").Value = "1,2,3,4,5"

…I get this error message in my ASP.Net Application:
"input string was not in a correct format"

Any idea what I'm doing wrong?

Thanks!!

Alex
 
I would think it's the datatype of @blaghID -- in your first example, you've
given it a numeric value (even though you've put quotes around it) so it is
able to convert it to a number. But in your second one, you're sending a
string, which it can't convert to a number.

Try changing your SP to expect a string value instead.

Beverley
 
Alex,

I am not sure if Beverly's suggestion will work. If it does, fine.
Otherwise, here are a couple of other options:

If the number of items is variable, then either change it to a subquery:

SELECT blagh FROM blagh WHERE blagh.blaghID IN (SELECT blagID FROM Sometable
WHERE SomeCriteria)

....or, create the worst-case number of parameters and then set the ones you
don't need to impossible values such as negative numbers.

SELECT blagh FROM blagh WHERE blagh.blaghID IN
(@blaghID1,@blaghID2,@blaghID3,@blaghIDN)

Of course if the number of items in the list is not variable then the last
solution would work fine.

--Bob
 
Thanks Beverley/Bob

Beverley, unfortunately changing the input parameter to a text sqldbtype
does not work. Thanks all the same.

Bob, the number of values I am putting into the parameter could vary
wildly, it could be anything from one to hundred values (or more) for
this parameter, so unfortunately I can’t see if any of your solutions
will have any practicality in this situation. I'm sure this solution can
work in some other scenarios though.

Anybody know of a neater solution, surely there has to be a definitive
solution for this? If not this would be a major setback.

Many thanks

Alex
 
Thanks William, looks like that's what I need to investigate.

Pity though that MS hasn't supplied something a little simpler for these
sort of operations. Something to feature in ASP 2.0/VS2005 maybe?

Cheers

Alex
 
Back
Top