Click here to Skip to main content
15,887,676 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
   Unable to delete multiple rows from DataGridView based on selection by CheckBox.
 
private void loadPage()
{
    int intSkip = 0, i = 0;
    intSkip = (this.currentPage * this.pageSize);
    string query = @"SELECT A.code AS 'Category Code', B.name AS 'Name', A.name AS 
   'Parent Category Name' FROM lwpos_categories A INNER JOIN lwpos_categories B ON 
    A.id=B.parent_id";
 
    string cs = ConfigurationManager.ConnectionStrings["aladin"].ConnectionString;
    SqlConnection con = new SqlConnection(cs);
    SqlCommand cmd = new SqlCommand(query, con);
    cmd.CommandType = CommandType.Text;
    con.Open();
    SqlDataReader sdr = cmd.ExecuteReader();
 
    DataSet ds = new DataSet();
    DataTable dt = ds.Tables.Add("lwpos_categories");
    // Add the table columns.
    for (i = 0; i < sdr.FieldCount; i++)
    {
         dt.Columns.Add(sdr.GetName(i), sdr.GetFieldType(i));
    }
    int intIdx = 0;
    while (sdr.Read())
    {
        if (intIdx >= intSkip)
        {
            DataRow row = dt.NewRow();
            // Assign DataReader values to DataRow.
           for (i = 0; i < sdr.FieldCount; i++)
           {
              row[i] = sdr[i];
           }
         dt.Rows.Add(row);
        }
        if ((intIdx - intSkip) >= (this.pageSize - 1))
        {
          break;
        }
        intIdx++;
   }
   sdr.Close();
   CategoryGV.DataSource = null;
   CategoryGV.ColumnCount = 8;
   CategoryGV.AutoGenerateColumns = false;

   CategoryGV.Columns[0].HeaderText = "Select";
   //CategoryGV.Columns[0].DataPropertyName = "chkBox";
   CategoryGV.Columns[0].Name = "chkBox";
   CategoryGV.Columns[0].Visible = true;
   CategoryGV.Columns[0].ReadOnly = false;

   CategoryGV.Columns[1].HeaderText = "Id";
   CategoryGV.Columns[1].DataPropertyName = "id";
   CategoryGV.Columns[1].Name = "id";
   CategoryGV.Columns[1].Visible = false;
   CategoryGV.Columns[1].ReadOnly = true;

   CategoryGV.Columns[2].HeaderText = "Category Code";
   CategoryGV.Columns[2].DataPropertyName = "Category Code";
   CategoryGV.Columns[2].Name = "catCode";
   CategoryGV.Columns[2].ReadOnly = true;
   
   CategoryGV.Columns[3].HeaderText = "Category Name";
   CategoryGV.Columns[3].DataPropertyName = "name";
   CategoryGV.Columns[3].Name = "catName";
   CategoryGV.Columns[3].ReadOnly =true;

   CategoryGV.Columns[4].HeaderText = "Category Image";
   CategoryGV.Columns[4].DataPropertyName = "image";
   CategoryGV.Columns[4].Name = "catImage";
   CategoryGV.Columns[4].Visible = false;
   CategoryGV.Columns[4].ReadOnly = true;

   CategoryGV.Columns[5].HeaderText = "Parent Category";
   CategoryGV.Columns[5].DataPropertyName = "Parent Category Name";
   CategoryGV.Columns[5].Name = "parentCat";
   CategoryGV.Columns[5].ReadOnly =true;

   CategoryGV.Columns[6].HeaderText = "Slug";
   CategoryGV.Columns[6].DataPropertyName = "slug";
   CategoryGV.Columns[6].Name = "catSlug";
   CategoryGV.Columns[6].Visible = false;
   CategoryGV.Columns[6].ReadOnly= true;

   CategoryGV.Columns[7].HeaderText = "Updated at";
   CategoryGV.Columns[7].DataPropertyName = "updated_at";
   CategoryGV.Columns[7].Name = "updatedat";
   CategoryGV.Columns[7].Visible = false;
   CategoryGV.Columns[7].ReadOnly =true;
   
   // Populate Data Grid
   this.CategoryGV.DataSource = ds.Tables["lwpos_categories"].DefaultView;
   
   // Show Status
   this.txtPageStatus.Text = "Showing Page: " + (this.currentPage + 1).ToString() + " 
   of " + this.pageCount.ToString();
   
   cmd.Dispose();
   ds.Dispose();
   con.Close();
}
 ================================================================================


What I have tried:

private void btnDeleteCategory_Click(object sender, EventArgs e)
{
    foreach (DataGridViewRow row in CategoryGV.Rows)
    {
       if (!string.IsNullOrEmpty(row.Cells[0].ToString()) && 
           !string.IsNullOrEmpty(row.Cells[0].Value.ToString()))
      {
         if (bool.Parse(row.Cells[0].Value.ToString())
         {
           string cs = 
               ConfigurationManager.ConnectionStrings["aladin"].ConnectionString;
          SqlConnection con = new SqlConnection(cs);

          SqlCommand cmd = new SqlCommand("DELETE FROM lwpos_categories WHERE id= '" + 
                                      Convert.ToInt32(row.Cells[1].Value) + "' ",con);
          con.Open();
          cmd.ExecuteNonQuery();
          con.Close();
          if(cmd.ExecuteNonQuery() >0)
          {
             MessageBox.Show("Deleted Successfully", "Successfull operation", 
                              MessageBoxButtons.OK, MessageBoxIcon.Information);
          }
          else
         {
            MessageBox.Show("Something went wrong.Rows not deleted.", "Un-Successfull 
            operation", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
         }
     }
   }
Posted
Updated 2-Dec-19 0:53am
v2
Comments
Richard MacCutchan 2-Dec-19 6:44am    
Why are you posting a success message without checking whether the delete command has succeeded? You also need to delete the rows from the dataset and refresh the grid.
Richard Deeming 3-Dec-19 8:30am    
SqlCommand cmd = new SqlCommand("DELETE FROM lwpos_categories WHERE id= '" + Convert.ToInt32(row.Cells[1].Value) + "' ",con);

Don't do it like that.

Whilst you're probably safe in this instance, since you've converted the value to an integer, using string concatenation to build a SQL query can and will lead to SQL Injection[^] vulnerabilities.

ALWAYS use a parameterized query.

SqlCommand cmd = new SqlCommand("DELETE FROM lwpos_categories WHERE id = @id", con);
cmd.Parameters.AddWithValue("@id", row.Cells[1].Value);

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900