0

I have a DataGridView gvCategory bound to a DataTable MyDataSet.Category. When a user deletes a row or rows, I would like to pass the UserID of the person who deletes the row(s) along with the RowID (of course) to the stored procedure so I can keep a record of who deletes which row.

For DeleteCommand, I have this

Me._adapter.TableMappings.Add(tableMapping)
Me._adapter.DeleteCommand = New Global.System.Data.SqlClient.SqlCommand()
Me._adapter.DeleteCommand.Connection = Me.Connection
Me._adapter.DeleteCommand.CommandText = "Usp_DeleteCategory"
Me._adapter.DeleteCommand.CommandType = Global.System.Data.CommandType.StoredProcedure
Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@CategoryID", Global.System.Data.SqlDbType.Int, 0, Global.System.Data.ParameterDirection.Input, 0, 0, "CategoryID", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))
Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlClient.SqlParameter("@UserID", Global.System.Data.SqlDbType.VarChar, 50, Global.System.Data.ParameterDirection.Input, 0, 0, "UpdatedBy", Global.System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))

In the event user deleting records, I put a code to set the UpdatedBy to the current UserID

Private Sub gvCategory_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles gvCategory.UserDeletingRow
    For Each dr As DataGridViewRow In gvCategory.SelectedRows
        Dim i As Int32 = dr.Index
        MyDataSet.Category.Item(i)("UpdatedBy") = "UserID"
    Next
End Sub

When user clicks Save button, I call my SaveChange sub

Private Sub SaveChange()
    Try
        Dim dt As DataTable = MyDataSet.Category.GetChanges()

        If Not (dt Is Nothing) AndAlso dt.Rows.Count > 0 Then
            For Each dr As DataRow In dt.Rows

                Select Case dr.RowState
                    Case DataRowState.Added
                        dr.Item("CreatedBy") = "UserID"
                    Case DataRowState.Modified
                        dr.Item("UpdatedBy") = "UserID"
                End Select

            Next

            CategoryTableAdapter.Update(dt)
            CategoryTableAdapter.Fill(MyDataSet.Category, Nothing)

            MessageBox.Show("Update completes", "Saving changes", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End If

    Catch ex As Exception
        MessageBox.Show("Unable to save your changes." & ControlChars.CrLf & ex.Message, "Update Category Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try
End Sub

I have tried different suggestions here to change the "UpdatedBy" value before calling CategoryTableAdapter.Update(), but so far I have UpdatedBy value unchanged or DeletedRowInaccessibleException thrown. What are other methods I could try to accomplish this?

T L
  • 504
  • 2
  • 11
  • 27
  • 1
    Wouldn't be simpler to just call SqlCommand.ExecuteNonQuery (Assuming MSSQL) and call your stored procedure directly and skip all the overheads of the TableAdapter? – Hursey Aug 26 '22 at 00:07
  • If you want to modify a record then don't delete it. It's that simple. An ADO.NET `DataRow` has two copies of its data. If the `RowState` is `Unchanged`, both copies are the same. If the `RowState` is `Modified`, each copy is different. If the `RowState` is `Added`, the original version is empty and inaccessible. If the `RowState` is `Deleted`, the current version is empty and inaccessible. If you want to logically delete, rather than physically delete, then don't mark the `DataRow` deleted. Catch the logical delete action and modify the row instead. – user18387401 Aug 27 '22 at 05:41

0 Answers0