Trying to delete selected row from datagridview but it is deleting multiple rows

Question

It is a simple question, but since I am new to C#, I am not sure how to solve this. Basically, I have a datagridview that displays records from MySQL table. I have a delete button, which by clicking performs the sql query, which is working fine, and is also meant to then delete the selected row from datgridview. But what actually happening, is it deletes multiple rows even when only one row is selected. Here is the query:

    private void delete_btn_Click(object sender, EventArgs e)
    {
        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            string constring = @"server = localhost; user id = root; password = pass; persistsecurityinfo = false; database = mapping; allowuservariables = false";
            using (MySqlConnection con = new MySqlConnection(constring))
            {
                using (MySqlCommand cmd = new MySqlCommand("UPDATE deletion SET date_time = UTC_TIMESTAMP() where product_id =" + proid_txtbx.Text, con))
                {
                    cmd.Parameters.AddWithValue("@product_id", row.Cells["product_id"].Value);
                    cmd.Parameters.AddWithValue("@product_name", row.Cells["product_name"].Value);
                    cmd.Parameters.AddWithValue("@category_id", row.Cells["category_id"].Value);
                    cmd.Parameters.AddWithValue("@date_time", row.Cells["date_time"].Value);
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
                foreach(DataGridViewRow item in this.dataGridView1.SelectedRows)
                {
                   dataGridView1.Rows.RemoveAt(this.dataGridView1.SelectedRows[0].Index);
                }
            }
        }

Screenshots: Row 6 should be deleted: Other Rows are deleted when I click Delete button

Your command does not _even_ has parameters. You _probably_ get an exception when you run it. — Soner Gönül, Dec 18 '15 at 12:30
cmd.Parameters.AddWithValue("@product_id", row.Cells["product_id"].Value); — Chandni Vishen, Dec 18 '15 at 12:33

score 2 · Accepted Answer · edited Dec 18 '15 at 12:42

2

I think your problem is with your foreach loop. You are looping through all your rows with dataGridView1.Rows. Tr dataGridView1.SelectedRows instead:

foreach (DataGridViewRow row in dataGridView1.SelectedRows)
    if (!row.IsNewRow) dataGridView1.Rows.Remove(row);

edited Dec 18 '15 at 12:42

Mihai-Daniel Virna

984
9
19

answered Dec 18 '15 at 12:37

Anant Dabhi

10,864
3
31
49

1

Absolutely Correct. Thank you. – Chandni Vishen Dec 18 '15 at 12:55
It does not work. Even though one row is selected but two rows are deleted. Facing this issue right now – Syed Irfan Ahmad Feb 16 '23 at 07:22
Observation: the malfunction (deletion of two rows when one is selected) happens when KeyDown event (detecting Keys.Delete) of DataGridView is programmed. When the same code is put in the Clicked event of a button, it works perfectly fine. Strange... – Syed Irfan Ahmad Feb 16 '23 at 07:44

score 0 · Answer 2 · edited Dec 24 '15 at 23:44

0

How about creating new command, somethnig like...

DELETE FROM YourTable WHERE product_id = ?

After that you can get value of selected item index:

int product_id = Convert.ToInt32(DataGridView1.SelectedRows[0].Cells[0].Value)

And at the end run the command and pass it the product_id int you got from command. I think it should work without problems...

edited Dec 24 '15 at 23:44

Josh Crozier

233,099
56
391
304

answered Dec 18 '15 at 12:53

Iske

1,150
9
18

score 0 · Answer 3 · answered Dec 18 '15 at 12:55

You can use:

private void delete_btn_Click(object sender, EventArgs e)
{
   //Works even when whole row is selected.
   int rowIndex = datagridview.CurrentCell.RowIndex;

   //You can also then easily get column names / values on that selected row
   string product_id = datagridview.Rows[rowIndex].Cells["Column Name Here"].Value.ToString();

   //Do additional logic

   //Remove from datagridview.
   datagridview.Rows.RemoveAt(rowIndex);

}

Trying to delete selected row from datagridview but it is deleting multiple rows

3 Answers3