Searching via IN clause

[ubkra]

I have a textbox which a user will enter states into, CA, NV etc. I want to replace the space between states and add a common between them so I can do a where IN clause.

s = States.Text.Replace(" ", ", ")
 
SELECT * FROM Users WHERE State IN (@States)
command.Parameters.AddWithValue("@States", s)

I do it in SQL management studio and it works, in vb.net it gives no errors and no results where I should.

What's is wrong?

[jmcilhinney]

I assume from your code that this is in VB.NET but you really should have stated that.

You can't use a single parameter to insert multiple values. You should add one parameter for each value. Follow the CodeBank link in my signature and check out my thread on Using Parameters With An IN Clause to learn how. To get the individual values you should Split the user input on the spaces.

[sapator]

What is the exact code you use on management studio to put nvarchar-varchar states in one @variable using "IN" and is working for you?
Oh, JMC beat me to it.

[sapator]

Actually u can sort of use a single parameter to insert multiple values but is tricky. Using TVC or in 2005 SQL versions and below "http://www.sommarskog.se/arrays-in-sql-2005.html" or you can use at trick with "Like"

[Serge]

Or you can just execute a dynamic sql passing your variable with "IN" values.