Deleting a row in a database from datagridview C#

Question

I'm trying to remove an entry from both the datagridview and the database. I got it so it removes the row from the data grid view but it fails to delete it also from the database. Here is my code I have in place:

  private void listBtn_Click(object sender, EventArgs e)
  {
        MySqlDataAdapter mysqldatadapter = new MySqlDataAdapter("select id, username from members ORDER BY id", new MySqlConnection(conn.connectionString));

        mysqldatadapter.Fill(ds);

        dataGridView.DataSource = ds.Tables[0];

        listBtn.Enabled = false;
  }


  private void btnDelete_Click(object sender, EventArgs e)
  {
        using (MySqlConnection dbConn = new MySqlConnection(conn.connectionString))
        {
            dbConn.Open();

            for (int i = 0; i < dataGridView.Rows.Count; i++)
            {
                DataGridViewRow row = dataGridView.Rows[i];

                if (row.Selected == true)
                {
                    dataGridView.Rows.RemoveAt(i);

                    MySqlCommand cmd = dbConn.CreateCommand();
                    cmd.CommandText = "DELETE FROM members WHERE id = " + i;

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (MySqlException ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }

            dbConn.Close();
        }
  }

I'm at a loss concerning this, any help would be appreciated.

Thanks!

Update - Is there a way to grab the value of individual cells to pass to the mysql command? For instance, grabbing the id?

Hard to tell, but likely your values for `i` don't match the ID column in the `members` table. — Dave, Mar 20 '17 at 22:56
that's what I was thinking. Is there a way to get the cell's value? — user2101411, Mar 20 '17 at 22:57

Dave · Answer 1 · 2017-03-21T00:36:30.683

0

Your values for i probably don't match the id fields in the member table.

You probably need to change this line:

cmd.CommandText = "DELETE FROM members WHERE id = " + i;

to

cmd.CommandText = "DELETE FROM members WHERE id = " + row.cells[id.Index].ToString();

edited Mar 21 '17 at 00:36

answered Mar 20 '17 at 22:59

Dave

10,748
3
43
54

When I change that, I get an error saying "Cannot apply indexing with [] to an expression of type 'DataGridViewRow'. – user2101411 Mar 20 '17 at 23:01
@bashis thanks I think you're right. Been a while since I've used DataGridViews – Dave Mar 20 '17 at 23:10
1

@dave turns out it's a bit harder than that: http://stackoverflow.com/questions/13436553/how-to-get-cell-value-of-datagridview-by-column-name/ – bashis Mar 20 '17 at 23:10
@bashis argh. what a pain. – Dave Mar 20 '17 at 23:11
I've updated the answer according to what should work as mentioned here: http://stackoverflow.com/a/20474611/361762 – Dave Mar 21 '17 at 00:35

Deleting a row in a database from datagridview C#

1 Answers1