Building SQL IN statement from listbox control Return Nothing

Question

I have a ListBox with multiple select values. I am trying to fetch data from the Employees table. Depending on values from this ListBox I tried to use in statement. I checked the query and it's true but still didn't fetch any data without any errors.

Here is the code:

StringBuilder sb = new StringBuilder("SELECT * FROM Emp_Attend LEFT JOIN Employees on Emp_Attend.Emp_ID = Employees.EmpID WHERE ");
    
StringBuilder sb = new StringBuilder("SELECT * FROM Emp_Attend LEFT JOIN Employees on Emp_Attend.Emp_ID = Employees.EmpID WHERE ");

using (SqlCommand cmdSQL = new SqlCommand())
{
    sb.Append("Emp_Attend.Emp_Name IN (");
    string InPartQuery = string.Join(",", ListBox1.Items
                                      .Cast<ListItem>()
                                      .Where(t => t.Selected)
                                      .Select(r => "'" + r.Text + "'"));
    sb.Append(InPartQuery);
    sb.Append(")");
    cmdSQL.CommandText = sb.ToString();
    cmdSQL.Connection = sqlcon;
    SqlDataAdapter da = new SqlDataAdapter(cmdSQL);
    DataTable dtbl = new DataTable();
    da.Fill(dtbl);
    sqlcon.Close();
    gvEmployees.DataSource = dtbl;
    gvEmployees.DataBind();

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection - check out [Little Bobby Tables](http://bobby-tables.com/) — marc_s, Nov 29 '20 at 07:45
i couldn't see your connection.open(); part. You can check your code compare with this answer. https://stackoverflow.com/a/6073545/8352989 — Kadir Kalkan, Nov 29 '20 at 08:53

score 0 · Answer 1 · answered Nov 29 '20 at 08:19

You can get all the selected items and store them in a variable then use it in your command like below:

List<string> ids = new List<string>();
        foreach (var item in listBox1.SelectedItems)
        {
            ids.Add(item.ToString());
        }
        string command = $"SELECT * FROM Emp_Attend LEFT JOIN Employees on Emp_Attend.Emp_ID = Employees.EmpID WHERE Emp_Attend.Emp_Name in ({string.Join(",",ids)})";

but I recommend you to make it parametric in order to avoid SQL Injection

score 0 · Accepted Answer · answered Nov 29 '20 at 15:10

The best idea is to use parameters rather than string concatenation. The following code can be found in my GitHub repository were the following class project provides methods to create parameters and the frontend project. The following may appear overkill while using parameters is keeping your data in parameters safe/hidden from prying eyes. All code is available on GitHub via links in the post below. In the screenshot below the first two examples are not parameterized while the third (button Hard Coded) is completely parameterized.

The following class method will generate a SQL WHERE IN without parameters but handles formatting e.g. apostrophes in string and leaves numerics alone.

In the following screenshot the two group boxes demo the above.

For parameterized there is BuildWhereInClause(string partialClause, string paramPrefix, IEnumerable parameters). Where in the following code sample BuildWhereInClause creates the SQL while AddParamsToCommand creates the parameters for the SQL statement which generated

SELECT C.CustomerIdentifier , C.CompanyName , C.ContactName , C.ContactTypeIdentifier , FORMAT(C.ModifiedDate, 'MM-dd-yyyy', 'en-US') AS ModifiedDate, CT.ContactTitle FROM dbo.Customers AS C INNER JOIN dbo.ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier WHERE CT.ContactTitle IN (@CTContactTitle0,@CTContactTitle1) ORDER BY C.CompanyName

Building SQL IN statement from listbox control Return Nothing

2 Answers2