Unable to delete multiple rows from datagridview based on selection by checkbox.

[prabhat7154]

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 = sdr;
            }
            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();
}

I have tried below code but it does not delete records from database

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

 

[prabhat7154]

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

 

[Skydiver]

Does your code even compile? How did you get lines 26-27 to compile correctly?

Is the id column in lwpos_categories a string or an integer in the database schema? If it's an integer, you shouldn't be putting quotes around the id on lines 14-15. To really deal with this issue, you should be using parameterized queries, instead of building queries by concatenating strings. That way you don't even have to remember whether you need the quotes around the value or not.

Also, if you setup database constraints between tables, you'll need to make sure that you delete from the child tables first before deleting from the parent tables. I'm suggesting his because from the code in your original post it looks like you have a join between tables. Some DBAs like to put in those constraints to enforce that kind of dependency, while others don't.