Parameterize Query using IN

  • Thread starter Thread starter Jonathan Lurie
  • Start date Start date
J

Jonathan Lurie

I have a query.
da.SelectCommand.CommandText="Select * from Inventory where
InventoryType=@Type and Status in (@Status)"

da.SelectCommand.Parameters.Add("@Type", SqlDbType.Int)
da.SelectCommand.Parameters.Add("@Status", SqlDbType.VarChar)

I know how to set value for the Inventory Type parameter.
da.SelectCommand.Parameters("@Type").Value = 1

But when I set value for the status parameter it does no return the correct
data.
da.SelectCommand.Parameters("@Status").Value = "I, A"

What am I doing wrong. Please advise.


John
 
Hi Jonathan,

It isn't possible in that way at all.
If you have values under control (no sql injection possibility) then you
might create sql statement dinamically.
Or you could create a temporary table in database, fill it with those values
and do an inner join instead of IN thing.
Or create some kind of stored proc that splits the comma delimited string.
 
Or create a table-value function that splits the delimited text (which is
the most common solution).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


Miha Markic said:
Hi Jonathan,

It isn't possible in that way at all.
If you have values under control (no sql injection possibility) then you
might create sql statement dinamically.
Or you could create a temporary table in database, fill it with those
values and do an inner join instead of IN thing.
Or create some kind of stored proc that splits the comma delimited string.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Jonathan Lurie said:
I have a query.
da.SelectCommand.CommandText="Select * from Inventory where
InventoryType=@Type and Status in (@Status)"

da.SelectCommand.Parameters.Add("@Type", SqlDbType.Int)
da.SelectCommand.Parameters.Add("@Status", SqlDbType.VarChar)

I know how to set value for the Inventory Type parameter.
da.SelectCommand.Parameters("@Type").Value = 1

But when I set value for the status parameter it does no return the
correct data.
da.SelectCommand.Parameters("@Status").Value = "I, A"

What am I doing wrong. Please advise.


John
Click to expand...
Click to expand...
 
Back
Top