Microsoft KB Archive/308058

= How To Obtain Extended Error Information in the DataSet by Using Visual Basic .NET =

Article ID: 308058

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q308058



This article refers to the following Microsoft .NET Framework Class Library namespace:
 * System.Data.SqlClient

IN THIS TASK
SUMMARY
 * Requirements
 * Steps to Obtain Extended Error Information
 * Steps to Create the Sample

REFERENCES



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
 Start Visual Studio .NET, and create a new Visual Basic .NET Console Application.  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 = &quot;Server=your_server;User ID=your_userID;&quot;& _ &quot;Password=your_password;Initial Catalog=Northwind;&quot; Dim myDACust As New SqlDataAdapter(&quot;Select * From Customers&quot;, myCn) Dim myCmdBlder As New SqlCommandBuilder(myDACust)

Try myCn.Open myDACust.MissingSchemaAction = MissingSchemaAction.AddWithKey myDACust.Fill(myDataSet, &quot;Customers&quot;) myDataSet.Tables(&quot;Customers&quot;).Rows(0)(&quot;ContactName&quot;) = &quot;Jefferson&quot;

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, &quot;Customers&quot;) Console.WriteLine(&quot;Update was processed successfully!&quot;) 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 & &quot; &quot; & _ 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  Modify the parameters of the ConnectionString property of the SqlConnection object as appropriate to properly connect to your SQL Server. 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!

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

back to the top