How To Obtain Extended Error Information in the DataSet by Using Visual Basic .NET (308058)

SUMMARY

The DataAdapter class throws generic exceptions when problems occur. This article demonstrates how to obtain a descriptive error message for each row and column that is in fault in any table in a given DataSet class. This is very useful in update scenarios when it is important to check if there are errors in any row or column.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:

• Visual Studio .NET
• ADO.NET fundamentals and syntax

back to the top

Steps to Obtain Extended Error Information

In a typical update scenario, follow these steps to obtain descriptive error information:

1. Use the HasChanges method to check if any changes have been made to the DataSet. This is helpful to detect if any rows are added, modified, or deleted.
2. Optionally, create a smaller DataSet (a subset of the original DataSet) that contains only the new, modified, or deleted rows that the GetChanges method returned. Use this smaller DataSet primarily to detect any possible errors during the update operation.
3. Use the HasErrors property to check if any of the tables in the DataSet contain any errors.
4. If no errors are detected in the DataSet, you can safely proceed with the Update operation.
5. If errors are detected in the DataSet, use the GetErrors method to obtain the rows that contain the errors. In the sample to follow, errors are checked for all tables in the DataSet. Optionally, you can also check one or more specific tables in the DataSet for errors.
6. After you determine which rows contain the errors, use the GetColumnError method to determine the columns that are in fault for each row with errors.

back to the top

Steps to Create the Sample

1. Start Visual Studio .NET, and create a new Visual Basic .NET Console Application.
2. Replace the default code in the Module1 code module with the following code:

   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=your_server;User ID=your_userID;"& _
                                   "Password=your_password;Initial Catalog=Northwind;"
           Dim myDACust As New SqlDataAdapter("Select * From Customers", myCn)
           Dim myCmdBlder As New SqlCommandBuilder(myDACust)
   
           Try
               myCn.Open()
               myDACust.MissingSchemaAction = MissingSchemaAction.AddWithKey
               myDACust.Fill(myDataSet, "Customers")
               myDataSet.Tables("Customers").Rows(0)("ContactName") = "Jefferson"
   
               If myDataSet.HasChanges(DataRowState.Modified Or _
               DataRowState.Added Or DataRowState.Deleted) Then
                   newDataSet = myDataSet.GetChanges(DataRowState.Modified Or _
                   DataRowState.Added Or DataRowState.Deleted)
                   If (Not newDataSet.HasErrors) Then
                       myDACust.Update(myDataSet, "Customers")
                       Console.WriteLine("Update was processed successfully!")
                   Else
                       For Each newTable In newDataSet.Tables
                           If newTable.HasErrors Then
                               rowsInError = newTable.GetErrors
                               For i = 0 To rowsInError.Length
                                   For Each newCol In newTable.Columns
                                       Console.WriteLine(newCol.ColumnName & " " & _
   rowsInError(i).GetColumnError(newCol))
                                   Next
                                   rowsInError(i).ClearErrors()
                               Next
                           End If
                       Next
                   End If
   
               End If
           Catch ex As Exception
               Console.WriteLine(ex.Message)
               Exit Try
           Finally
               If myCn.State = ConnectionState.Open Then
                   myCn.Close()
               End If
               myCn = Nothing
           End Try
   
           Console.ReadLine()
   
       End Sub
   
   End Module
   					

3. Modify the parameters of the ConnectionString property of the SqlConnection object as appropriate to properly connect to your SQL Server.
4. In the Visual Studio .NET Integrated Development Environment (IDE), click Run Without Debugging on the Debug menu to run the preceding code. If no errors occur during the update, a console window should open and display the following text:

   Update was processed successfully!
   						

5. Press any key to dismiss the console window and stop the application.

back to the top

REFERENCES

For more information about the methods and properties that are used in this article, refer to the .NET Framework Software Development Kit (SDK) documentation.

back to the top