How To Retrieve Values from DataRows That Are Marked "Deleted" by Using Visual Basic .NET (310370)

SUMMARY

You can use ADO.NET DataTable objects to delete rows and mark them as "Deleted." This article demonstrates how to retrieve the values of a row whose DataRowState enumeration is set to DataRowState.Deleted.

back to the top

Description of the Technique

You can use the DataRowVersion.Original enumeration value to retrieve the values of a row that has the DataRowState.Deleted enumeration value. You must use DataRowVersion.Original because the values of a deleted row are not available for the current state (DataRowVersion.Current).

If you try to reference current column values of a DataRow object that has a RowState property value of DataRowState.Deleted, you receive the following error message: back to the top

Steps to Build the Sample

The sample code in this section demonstrates how to reproduce the preceding error. This sample also demonstrates how to use DataRowVersion.Original to obtain the column values of the deleted row.

1. Create a new Visual Basic Console Application project.
2. Delete the default code, and add the following code to the Module1 code module:

   Imports System.Data.SqlClient
   Imports Console = System.Console
   
   Module Module1
   
       Sub Main()
   
           Dim response As String
           Dim myDataSet As New DataSet()
           Dim myDataRow As DataRow
           Dim newDataSet As DataSet
           Dim rowsInError As DataRow()
           Dim newTable As DataTable
           Dim newCol As DataColumn
           Dim i As Integer
   
           Dim myCn As New SqlConnection()
           myCn.ConnectionString = "Server=mySQLServer;User ID=sa;Password=mypassword;" & _
                                   "Initial Catalog=Northwind;"
           Dim myDACust As New SqlDataAdapter("Select * From Customers", myCn)
           Dim myCmdBlder As New SqlCommandBuilder(myDACust)
   
           myCn.Open()
           myDACust.MissingSchemaAction = MissingSchemaAction.AddWithKey
           myDACust.Fill(myDataSet, "Customers")
           myDataSet.Tables("Customers").Rows(0).Delete()
   
           Dim delRow As DataRow
   
           If myDataSet.HasChanges(DataRowState.Deleted) Then
               newDataSet = myDataSet.GetChanges(DataRowState.Deleted)
               If (Not newDataSet.HasErrors) Then
                   delRow = newDataSet.Tables("Customers").Rows(0)
   
   		' The following line generates the exception:
   		Console.WriteLine(delRow(0))
   		
   		' To resolve this problem, comment the previous line,
   		' and uncomment the following line.
   		' Console.WriteLine(delRow(0, DataRowVersion.Original)) 
    
               End If
   
           End If
           If myCn.State = ConnectionState.Open Then
               myCn.Close()
           End If
           myCn = Nothing
   
           Console.WriteLine("Update was processed successfully!")
           Console.ReadLine()
   
       End Sub
   
   End Module
   					

3. Modify the parameters of the ConnectionString property of the SqlConnection object as appropriate for your environment.
4. In the Microsoft Visual Studio .NET Integrated Development Environment (IDE), on the Debug menu, click Run Without Debugging to run the code. A console window displays the "Update was processed successfully!" message if Visual Studio does not encounter any errors.
5. Press any key to dismiss the Console window and stop the application.

back to the top