keydown delete in dataGridView

Hello! I have a dataGridView that displays a data table pulled from mySQL.
I am trying to delete a user-selected row when the user hits the DELETE key.
What is happening is, the delete works (both in the DGV and the actual data table), but it deletes TWO rows (selected & the one above) from the dataGridView (but not from the actual table). When I refresh the DGV, the row above the selected row that wasn't supposed to get deleted re-appears. I want JUST the selected row to be deleted from the DGV.

Could someone tell me how I can fix my code? Thank you!

Code:

---

    Private Sub dgv_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView1.KeyDown
        If e.KeyCode = Keys.Delete Then
            setConnection()
            deleteData()
        End If
    End Sub

Private Sub deleteData()

        Try

            Dim i As Integer
            Dim tableName As String = TableList.Text

            Dim mResult
            mResult = MsgBox("Do you really want to delete the selected records?", vbYesNo + vbQuestion, "Removal confirmation")
            If mResult = vbNo Then
                Exit Sub
            End If

            command = New MySqlCommand
            command.Connection = conn

            If tableName = "persons" Then

                For i = DataGridView1.SelectedRows.Count - 1 To 0 Step -1
                    command.CommandText = "delete from `" & tableName & "` where `Book ID`=" & DataGridView1.SelectedRows(i).Cells("Book ID").Value & ";"
                    command.ExecuteNonQuery()
                    DataGridView1.Rows.Remove(DataGridView1.SelectedRows(i))
                Next

            ElseIf tableName = "randomdata" Then

                For i = DataGridView1.SelectedRows.Count - 1 To 0 Step -1
                    command.CommandText = "delete from `" & tableName & "` where dataid=" & DataGridView1.SelectedRows(i).Cells("dataid").Value & ";"
                    command.ExecuteNonQuery()
                    DataGridView1.Rows.Remove(DataGridView1.SelectedRows(i))
                Next

            End If

            fetchData(tableName)

        Catch ex As Exception
            MsgBox("Could not delete record. " & ex.Message)
        Finally
            conn.Close()
        End Try

    End Sub

---

You have made something that is very simple rather complex. You should be using a MySqlDataAdapter to Fill a DataTable. Bind that DataTable to a BindingSource and bind that to the grid. When you want to delete the selected record yo simply call RemoveCurrent on the BindingSource, which deletes the record in the DataTable. You then use the same MySqlDataAdapter Update the database from the DataTable.

Do I still need a different SQL statement for each table in the database? or is there a way to do this without multiple SQL statements?

Here is what I changed my code to:

Code:

---

Dim tableName As String = TableList.Text

        dt = BindingSource.DataSource

        BindingSource.RemoveCurrent()

        command = New MySqlCommand
        adapter = New MySqlDataAdapter

        command.Connection = conn
        command.CommandText = ' ******** don't know what to put here *********

        adapter.SelectCommand = command
        reader = command.ExecuteReader

        dt.Load(reader)

        reader.Close()

---

Still not quite right I'm afraid. Follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data for an example. One data adapater = one DataTable.

Is this the example you are talking about?

Copying & pasting your code:

Code:

---

    Private connection As New SqlConnection("connection string here")
    Private adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", _
                                          connection)
    Private table As New DataTable
   
    Private Sub InitialiseDataAdapter()
        Dim delete As New SqlCommand("DELETE FROM StockItem WHERE ID = @ID", Me.connection)
        Dim insert As New SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)", Me.connection)
        Dim update As New SqlCommand("UPDATE StockItem SET Name = @Name, Quantity = @Quantity, Unit = @Unit WHERE ID = @ID", Me.connection)
   
        delete.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
   
        insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
        insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
        insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
   
        update.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name")
        update.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity")
        update.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit")
        update.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
   
        Me.adapter.DeleteCommand = delete
        Me.adapter.InsertCommand = insert
        Me.adapter.UpdateCommand = update
   
        Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
    End Sub
   
    Private Sub GetData()
        'Retrieve the data.
        Me.adapter.Fill(Me.table)
   
        'The table can be used here to display and edit the data.
        'That will most likely involve data-binding but that is not a data access issue.
    End Sub
   
    Private Sub SaveData()
        'Save the changes.
        Me.adapter.Update(Me.table)
    End Sub

---

That's the one. If you have multiple related tables then you'd use a DataSet to contain the DataTables with the appropriate DataRelation(s) between them.