CODEWITHSUNDEEP
vb.netdatagridview
dub stylee
dub stylee

Reputation: 3342

SqlDataAdapter missing DeleteCommand

I am loading a VB.NET DataGridView using an SqlDataAdapter to fill a DataSet and then setting the DataSource of the DataGridView to the DataSet.

For some reason, the only command that is populated with the SqlCommandBuilder is the SelectCommand. I realize I am only specifying a SELECT query, but I thought the purpose of the SqlCommandBuilder is to generate the Update/Delete/Insert command for me.

The following is the applicable code:

Dim _dstErrorLog As New DataSet
Dim _adapter As SqlDataAdapter
Dim _builder As SqlCommandBuilder

Private Sub ErrorLog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Call LoadErrorLog()
End Sub

Private Sub LoadErrorLog()
    Dim strSql As String

    strSql = "SELECT Ident, LogDate, LogDesc FROM dbo.ErrorLog ORDER BY DescDateTime DESC"
    _adapter = New SqlDataAdapter(strSql, dataLayer.Connection)
    _builder = New SqlCommandBuilder(_adapter)
    _adapter.Fill(_dstErrorLog)

    grdErrorLog.DataSource = _dstErrorLog.Tables(0)
End Sub

Private Sub grdErrorLog_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles grdErrorLog.UserDeletingRow
    _adapter.Update(_dstErrorLog.Tables(0))
End Sub

When I delete a row, it disappears from the DataGridView but when I re-open the form, the data is all still there. Do I need to specify the DeleteCommand myself? And if so, then what is the purpose of the SqlCommandBuilder??

Upvotes: 1

Views: 128

Answers (1)

Steve
Steve

Reputation: 216293

Use the UserDeletedRow event, not the UserDeletingRow.
In the UserDeletingRow event the changes made to your DataGridView are still not committed to the DataSource binded, so calling the SqlDataAdapter.Update in that event doesn't result in any changes to the database because the table is still unchanged.

Private Sub grdErrorLog_UserDeletedRow(sender As Object, e As DataGridViewRowEventArgs) Handles grdErrorLog.UserDeletingRow
    Dim count = _adapter.Update(_dstErrorLog.Tables(0))
    Console.WriteLine("Rows deleted:" & Count)
End Sub

Infact in the UserDeletingRow the argument e is a DataGridViewRowCancelEventArgs meaning that you could still cancel the deletion setting e.Cancel = True

Upvotes: 1

Related Questions