schaud schaud - 2 months ago 13
C# Question

Passing C# String as SQL parameter for SELECT WHERE IN

I am writing a function to take as many multiple selected items from a Listbox and pass the vaules to a SQL Query to INSERT values into a table after selecting the filtered values from another table. The code I have typed is below and it doesn't seem to work (the problem is with the way I'm passing the string to the query.

string lbSites = "";

protected void Button1_Click1(object sender, EventArgs e)
{
string cns = "server=abc;database=testDB;Trusted_Connection=True";
using (SqlConnection con = new SqlConnection(cns))
{
using (SqlCommand command = con.CreateCommand())
{
command.CommandText = "INSERT INTO Activity (Hostname,Site,Status,System_Dept,Business_Dept)"
+ "SELECT * FROM Inventory WHERE Site IN ("+lbSites+");"
;
con.Open();
command.Parameters.AddWithValue("@lbSites", lbSites);
command.ExecuteNonQuery();
con.Close();
}
}
}

protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (ListBox1.Items.Count > 0)
{
for (int i = 0; i < ListBox1.Items.Count; i++)
{
if (ListBox1.Items[i].Selected)
{
lbSites += "'" + ListBox1.Items[i].Value + "', ";
}
}
}
}

Answer

The value of lbSites is lost everytime you've posted back. Keep it in your ViewState.

Besides, you don't need command.Parameters.AddWithValue("@lbSites", lbSites); since there's no @lbSites parameter in your sql.