SQL reader cannot read all the checkListBox checkedItems

Question

I am working on a windows form application using c#. I have a problem with trying to match the checked data from my checkListBox to my sql data checkbox column. What I am trying to do is a binded the checkListBox to one of my column in my database table let's call it employee_detail which can fetch the data from database correctly. However, when I am trying to export the checked items from that checklistbox, I can only read the first checked item from the checklistbox. If I have 3 items and I checked all 3 items, my excel only show the 1st checked items. I have no idea why. For example, I have Amy, Peter and Jimmy in my checkListBox. When I select Amy, it will export the name Amy to my excel table. However, if I select all 3 (Amy, Peter and Jimmy), it only export Amy to my excel even though I have 3 checked. I have no idea why it doesn't work.

What I tried first time. This is a complete disaster. The HasRow in my datareader cannot fetch any column from my db even I can execute the same query in my sql console and find the column I am looking for. This loop skip whatever it is inside the while loop since HasRow = false

Here is my first attempted

foreach(var items in checkListBox1.CheckedItems){

    string query = "select * from my_table WHERE employeeName = '"+items+"'"

    SqlCommand myCommand = new SqlCommand(query, myConn);
    SqlDataReader dr = myCommand.ExecuteReader();

    while(dr.Read()){
        //read the column    
    }
}

My second attempt is using the parameters, this seems to work, but the problems (what I suggested above) is my excel files only showed the first checked items (I believe it only loop through the while loop once), when my db table has more then one record

Here is my second attempted

        foreach (var item in employeeCheckListBox.CheckedItems)
        {

            if (items.Length == 0)
            {
                items = item.ToString();
            }
            else
            {
                items = items + "," + item;
            }


            string query = @"select * from [employeeId_detail] as td LEFT JOIN[order_detail] as cd ON td.employeeId = cd.employeeId  Where employeeName in (@name)";

            myCommand = new SqlCommand(query, myConn);

            myCommand.Parameters.Add("@name", SqlDbType.NVarChar);

            myCommand.Parameters["@name"].Value = items;

            SqlDataReader dr = myCommand.ExecuteReader();

            while (dr.Read())
            {

                i++;
                {
                    string f_Name = dr.GetString(0);
                    string l_Name = dr.GetString(1);
                    string full_Name = dr.GetString(2);
                    string e_email = dr.GetString(3);

                    xlEmployeeDetail.Cells[i, 1] = f_Name;
                    xlEmployeeDetail.Cells[i, 2] = l_Name;
                    xlEmployeeDetail.Cells[i, 3] = full_Name;
                    xlEmployeeDetail.Cells[i, 4] = e_email;

                }
            }
            dr.Close();
        }

Answer 1

Parameterizing is definitely the better way to go, like in your second example.

The problem is, I'm fairly certain your query ends up looking like this, where you're passing it a single name with a couple commas in it, not a list of separate names:

select *
from [employeeId_detail] as td
LEFT JOIN[order_detail] as cd ON td.employeeId = cd.employeeId
Where employeeName in ('joe, bob, sue')

Instead, here's something a bit more dynamic, creating a parameter for each checked item.

string parameterList = "";
int parameterCounter = 0;

// Add a parameter placeholder for each "checked" item
for (var i=0; i<checkListBox1.CheckedItems.Count; i++)
    parameterList += (i==0) ? "@p" + i : ", @p" + i;

string query = @"select *
                 from [employeeId_detail] as td
                 LEFT JOIN[order_detail] as cd ON td.employeeId = cd.employeeId
                 Where employeeName in (" + parameterList + ")";

myCommand = new SqlCommand(query, myConn);

// Now give each parameter a value from the collection of checked items
for (var i = 0; i < checkListBox1.CheckedItems.Count; i++)
    myCommand.Parameters.AddWithValue("@p" + i, checkListBox1.CheckedItems[i].ToString());

SqlDataReader dr = myCommand.ExecuteReader();

Answer 2

Reference to our chat: Problem is not in the code block that you wrote. The problem lies within the characters that you are using for the fetching the content out of DB.

As we tested, this block of code works fine with the English characters but fails to Chinese characters which is all together a separate problem.

However if you still would go for the Chinese character then probably you would need to do settings at the DB level, and your same code will work fine.