Results 1 to 6 of 6

Thread: keydown delete in dataGridView

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: keydown delete in dataGridView

    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.

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: keydown delete in dataGridView

    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()
    Last edited by echoUser; Oct 29th, 2012 at 03:06 AM.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: keydown delete in dataGridView

    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.

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2012
    Posts
    42

    Re: keydown delete in dataGridView

    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

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: keydown delete in dataGridView

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width