CODEWITHSUNDEEP
c#sql-server-2008listboxwhere-in
schaud
schaud

Reputation: 89

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 + "', ";
            }
        }
    }
}

Upvotes: 4

Views: 6212

Answers (3)

Mohamad Mahmoud Darwish
Mohamad Mahmoud Darwish

Reputation: 4175

try this 
SELECT M.REG_NO, T.TYPE_ID 
    FROM MAIN AS M 
        INNER JOIN CLASSIFICATION AS C 
            ON M.REG_NO = C.REG_NO
        INNER JOIN TYPE AS T 
            ON T.TYPE_ID = C.TYPE_ID
    WHERE (@Types) like .LIKE '%,' +T.TYPE_ID+ ',%'

Upvotes: 0

user111013
user111013

Reputation:

You should not directly pass values like this to SQL, as it leaves you open to a SQL Injection Attack.

Instead, you can figure out how many parameters you need, and then insert those parameter names into your query.

This approach will work for up to approximately 2,000 values (assuming SQL Server). If you need to pass more values, you will either need to break up the queries into sub-sets, or use parameter-value tables.

Example (not tested, so may have some bugs): 

// Get your selected items:
var items = ListBox1.Items.Where(i=>i.Selected).Select(i=>i.Value).ToArray(); 

// Create a series of parameters @param0, @param1, @param2..N for each value.
string paramNames = string.Join(", ", Enumerable.Range(0,items.Count()).Select(e=>"@param"+e)); 

// Build the command text and insert the parameter names. 
string commandText = "INSERT INTO Activity (Hostname,Site,Status,System_Dept,Business_Dept)" 
                + "SELECT * FROM Inventory WHERE Site IN ("+ paramNames +")";

command.CommandText = commandText; 

// Now add your parameter values:  this binds @param0..N to the values selected. 

for(int param=0;param<items.Count();param++)
{
   command.Parameters.AddWithValue("@param" + param, items[param]); 
}

Upvotes: 3

Jeffrey Zhao
Jeffrey Zhao

Reputation: 5023

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.

Upvotes: 0

Related Questions