Deletion from a Datagridview

[jim@firstbankms.com]

I am using VS2012 with a vb.net Windows application. I load a datagridview with a sql database.

I have a sub that will detect the del key if the datagridview row is highlighted and will execute an tableadapter update and the record will disipear from the DGV but not the SQL database. I have another sub fired by a button that when I hit the button, it will delete. The button and the del do the same update function..

Here is the del sub

Private Sub dgvUtility_KeyDown(sender As Object, e As KeyEventArgs) Handles dgvUtility.KeyDown
Dim selectedRowCount As Integer = _
dgvUtility.Rows.GetRowCount(DataGridViewElementStates.Selected)

If selectedRowCount > 0 Then
If e.KeyCode = Keys.Delete Then
If MessageBox.Show("Are you sure you want to delete this selected record?", "Delete this record", MessageBoxButtons.OKCancel) = Windows.Forms.DialogResult.OK Then
' This completes the deletion
myUtilityDetailTableAdapter.Update(TellerApp2000SQLDataSet.UtilityDetail)
End If
End If
Else
MessageBox.Show("You must select the row header (Entire row) to delete", "Did you selcet the row header?")
End If

End Sub

Firing this sub alone will not delete the record from SQL. Firing this sub then clicking the Update button sub

Private Sub btUpdateUtility_Click(sender As Object, e As EventArgs) Handles btUpdateUtility.Click
myUtilityDetailTableAdapter.Update(TellerApp2000SQLDataSet.UtilityDetail)
End Sub

will successfully delete the record. Both call the same myUtilityDetailTableAdapter.Update(TellerApp2000SQLDataSet.UtilityDetail)

My question is why doesn't the first sub delete the record?

 

[jmcilhinney]

Without testing, I'm guessing the issue is that the row hasn't been deleted from the DataTable yet when you call Update. The Update call on the table adapter serves only to save changes from the DataTable back to the database so, if there are no changes, then nothing will be saved.

What you should do is set the AllowUserToDeleteRows property of the grid to False so that there is no automatic deletion. Then you can perform the entire deletion yourself, which you would by deleting the row locally first and then saving that change to the database. Before the Update call, call RemoveCurrent on the relevant BindingSource and that will delete the row locally, which change you can then save back to the database.

Also, there is a significant flaw in your code. The first thing you test for is whether any rows are selected and then tell the user that they can't delete if none are. The problem with that is that you haven't yet tested whether the user actually pressed the Delete key. It should be obvious that any code related to deleting should only be executed if the user has actually requested a deletion. As such, testing for that needs to be the first thing you do because, if they haven't requested a deletion, there's nothing else for you to do. Your code should look like this:

If e.KeyCode = Keys.Delete Then
    Dim selectedRowCount As Integer = dgvUtility.SelectedRows.Count

    If selectedRowCount > 0 Then
        If MessageBox.Show("Are you sure you want to delete this selected record?", "Delete this record", MessageBoxButtons.OKCancel) = DialogResult.OK Then
            myUtilityDetailBindingSource.RemoveCurrent()
            myUtilityDetailTableAdapter.Update(TellerApp2000SQ LDataSet.UtilityDetail)
        End If
    Else
        MessageBox.Show("You must select the row header (Entire row) to delete", "Did you selcet the row header?")
    End If
End If