CODEWITHSUNDEEP
asp.netsql-servert-sql
e_sezgin
e_sezgin

Reputation: 63

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

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"

Upvotes: 0

Views: 1360

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460138

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

Upvotes: 1

Related Questions