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

This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data.SqlClient
  • System.Console

IN THIS TASK

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.

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:
An unhandled exception of type 'System.Data.DeletedRowInaccessibleException'
occurred in system.data.dll
Additional information: Deleted row information can't be accessed through the row.

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.

References

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
172375 How To Undo Updates for a Single Row with CancelBatch
For more information, see the "Row States and Row Versions" topic and other topics in the Microsoft .NET Framework documentation.

Propriedades

ID do Artigo: 310370 - Última Revisão: 23 de mar de 2009 - Revisão: 1

Comentários