Asp.Net How to pass multiple value to a parameter which is using with 'IN' clause in the query

Question

I created a web form and In the web form, there is a multiselect dropdownlist. I am getting value from multiselect dropdownlist and passing it to hidden field. Then i am adding hidden field's value to SqlCommand as a parameter. As it is understood i have a query and the parameter is using with 'IN' clause in the query.

When i select only one option, it is working smoothly and i can get the dataset.but when it is selected multiple.it is returning no result.

Query:

   select .... from tblReservation
   Where  type IN (@type)

Code:

   command.Parameters.Add(new SqlParameter("@type", HiddenField.Value));

When one option is selected HiddenField.Value="flight"

When multiple options is selected HiddenField.Value="flight,Hotel,rentacar"

Isn't there anyway without changing the query. – e_sezgin Oct 28 '14 at 13:10 — e_sezgin, Oct 28 '14 at 13:10

score 1 · Accepted Answer · edited May 23 '17 at 12:20

SQL Server sees your IN clause as:

IN ('flight,Hotel,rentacar')

What you want is

IN ('flight','Hotel','rentacar')

So you need one parameter for every type.

You can use this approach:

string[] types = HiddenField.Value.Split(','); // { "flight","Hotel","rentacar" };
string cmdText = "SELECT * FROM tblReservation WHERE type IN ({0})";

string[] paramNames = types.Select(
    (s, i) => "@type" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], types[i]);
    }
}

Reference: Parameterize an SQL IN clause

Asp.Net How to pass multiple value to a parameter which is using with 'IN' clause in the query

1 Answers1