The ADO.NET DataTable object contains a lot of useful information that you might not be aware of. For each row in the DataTable you can determine the row’s state (is it original, new, deleted, modified etc.) and you can have access to the "before" values of modified rows as well as the values from rows that have been deleted. These examples show some of the things you can do to test and work with this additional information.
First we will create a test DataTable and add some data to it. Note that all rows will have the rowstate of "Added" since they did not come from an external data store by means of the ADO "Fill" command:Dim dt As New DataTable
dt.Columns.Add(New DataColumn("Name", GetType(String)))
nr = dt.NewRow
nr.Item("ID") = 1
nr.Item("Name") = "Bob"
nr = dt.NewRow
nr.Item("ID") = 2
nr.Item("Name") = "Sue"
nr = dt.NewRow
nr.Item("ID") = 3
nr.Item("Name") = "Dave"
Debug.WriteLine("All rows, rowstate is added")
Debug.WriteLine("ID: " & dr.Item("ID").ToString & _
", Name: " & dr.Item("Name").ToString & _
", Rowstate: " & dr.RowState.ToString)
Now we will delete a row. Since the row we delete had the row state of "Added", deleting it simply removes it from the collection. There is no way to access the values from this deleted row:' Delete a row
Debug.WriteLine("Only two rows in collection. No way to access the deleted row") For Each dr As DataRow In dt.Rows
In order to change the rowstate of the added rows to "Unchanged", you use the AcceptChanges method. Now see what happens to the RowState property of the remaining rows:' Cause all rows to be set to unchanged
Debug.WriteLine("Now see the unchanged and modified rowstate") For Each dr As DataRow In dt.Rows
Now when we delete a row, it remains in the collection. If you attempt to access the values of the deleted row, you will generate a run-time error. In order to avoid this, you can check the row’s RowState property, and skip rows that are marked as Deleted:' Delete another row
Debug.WriteLine("Now deleted row is included with the correct rowstate") ' Must check the rowstate.
Finally, what if you need to be able to see the values of the deleted row (perhaps you are logging the "before" state of a row to an audit table)? The way to do this is to create a filtered DataView from the DataTable. One of the arguments to the DataView constructor is the DataViewRowState of the rows you wish to access. Here is an example of how to do this:' In order to access the values from the deleted row
Debug.WriteLine("Retrieve the values of deleted rows") Dim dv As New DataView(dt, "", "", DataViewRowState.Deleted) For Each drv As DataRowView In dv
Thank you! Thank you! I just finished reading this document, which was part of a link in the recent Buzz newsletter. I have printed it for others to read, especially those skeptical on the powers of Access and its capabilities.