Items are listed in listbox, I want to use foreach to delete the items from SQL

Question

private void SearchTable()
        {
            try
            {
                    using (SqlConnection con = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=Steripack;Integrated Security=True"))
                    {
                        foreach (var item in lstCompleted.Items)
                        {
                        string killtag = item.ToString();
                        con.Open();
                        using (SqlCommand command = new SqlCommand("DELETE FROM tblAsset1 WHERE tagID = " + killtag))
                        {
                            command.ExecuteNonQuery();
                            lstCompleted.Items.Add("Removed: " + killtag);
                        }
                        con.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                lblStatus.Text = "SQL Failed..." + ex;
            } 
        }

Answer 1

First, use sql-parameters instead of string concatenation. Otherwise you are open for sql-injection. You also have to assign the connection to the command.

However, there is another issue here: you are modifying the collection that you are enumerating in the foreach.

One way: use another collection where you store the items you'll add later:

using (SqlConnection con = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=Steripack;Integrated Security=True"))
{
    List<string> deletedMessages = new List<string>();
    foreach (object item in lstCompleted.Items)
    {
        int killtag = int.Parse(item.ToString());
        con.Open();
        using (SqlCommand command = new SqlCommand("DELETE FROM tblAsset1 WHERE tagID = @killtag", con))
        {
            command.Parameters.Add(new SqlParameter("@killtag", SqlDbType.Int).Value = killtag);
            command.ExecuteNonQuery();
            deletedMessages.Add("Removed: " + killtag);
        }
        //con.Close(); // unnecessary with using
    }
    foreach (string deletedMsg in deletedMessages)
        lstCompleted.Items.Add(deletedMsg);
}

Another approach is to store the items that you want to delete in another collection which you enumerate in the foreach:

List<string> toDelete = lstCompleted.Items.Cast<string>().ToList();
foreach (string item in toDelete)
{
    int killtag = int.Parse(item);
    con.Open();
    using (SqlCommand command = new SqlCommand("DELETE FROM tblAsset1 WHERE tagID = @killtag", con))
    {
        command.Parameters.Add(new SqlParameter("@killtag", SqlDbType.Int).Value = killtag);
        command.ExecuteNonQuery();
        lstCompleted.Items.Add("Removed: " + killtag);
    }
}

Note that the .ToList() in the first line is essential since that creates a different, unassociated list which doesn't refer to the ObjectCollection in Listbox.Items.