Unable to delete multiple rows based on selection byCheckboX

Question

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["DBCS"].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";//DataPropertyName refers name of column of table

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";//DataPropertyName refers ALIAS NAME

CategoryGV.Columns[2].Name = "catCode";

CategoryGV.Columns[2].ReadOnly = true;

CategoryGV.Columns[3].HeaderText = "Category Name";

CategoryGV.Columns[3].DataPropertyName = "name";//DataPropertyName refers name of column of table

CategoryGV.Columns[3].Name = "catName";

CategoryGV.Columns[3].ReadOnly =true;

CategoryGV.Columns[4].HeaderText = "Category Image";

CategoryGV.Columns[4].DataPropertyName = "image";//DataPropertyName refers name of column of table

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";//DataPropertyName refers ALIAS NAME

CategoryGV.Columns[5].Name = "parentCat";

CategoryGV.Columns[5].ReadOnly =true;

CategoryGV.Columns[6].HeaderText = "Slug";

CategoryGV.Columns[6].DataPropertyName = "slug";//DataPropertyName refers name of column of table

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";//DataPropertyName refers name of column of table

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();

}

 ================================================================================

 

 

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["DBCS"].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();

}

}

}

MessageBox.Show("Deleted all selected rows Successfully", "Successfull operation", MessageBoxButtons.OK, MessageBoxIcon.Information);

}