HOW TO: Get Extended Error Information for the DataSet in Visuall C++ .NET (308908)

SUMMARY

The DataAdapter object throws generic exceptions when problems occur.

Use this step-by-step guide to check for and report errors for each row and column in each table in a DataSet. You can use this information in update scenarios where it is important to check for 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 XP Professional, Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft 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

Get Extended Error Information

To get extended error information in a typical update scenario, follow these steps:

1. Use the HasChanges method to detect any additions, modifications, or deletions in any new rows in the DataSet.
   
   -or-
   
   Create a subset of the original DataSet and then use the GetChanges() method to check for any errors.
   
   NOTE: The subset should contain only new, modified, or deleted rows.
2. Use the HasErrors property to verify if there are any errors in any of the tables in the DataSet.
3. If no errors are found in the DataSet, continue with the update operation.
4. If errors are found in the DataSet, use the GetErrors() method to identify the rows that contain the errors. In the sample shown below, all tables in the DataSet are checked for errors. However, you can check one or more specific tables in the DataSet.
5. After you identify the rows that contain errors, use the GetColumnError() method to determine the columns in fault for each row with errors.

back to the top

Build the Sample Code

The following sample code uses the Customers table in the Northwind database that is included with Microsoft SQL Server.

1. In Visual Studio .NET, create a new Managed C++ Application.
2. Replace the default code in your application's source file (your_name.cpp) with the following code:

   #include "stdafx.h"
   
   #using <mscorlib.dll>
   #using <System.dll>
   #using <System.Data.dll>
   #using <System.Xml.dll>
   
   using namespace System;
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   
   // This is the entry point for this application.
   #ifdef _UNICODE
   int wmain(void)
   #else
   int main(void)
   #endif
   {
     DataSet *myDataSet = new DataSet();
     DataSet *newDataSet; 
     DataRow *rowsInError[];
   
     SqlConnection *myCn = new SqlConnection();
     myCn->ConnectionString = "Server=your_sql_server;User ID=your_user_id;Password=your_password;Initial Catalog=Northwind;";
     SqlDataAdapter *myDACust = new SqlDataAdapter("Select * From Customers", myCn);
     SqlCommandBuilder *myCmdBlder = new SqlCommandBuilder(myDACust);
   
     try
     {
       DataRow *myRow;
       DataTable *myTable;
       myCn->Open();
       //get schema information for the Update
       myDACust->MissingSchemaAction = MissingSchemaAction::AddWithKey;
       myDACust->Fill(myDataSet, "Customers");
   
       myTable = myDataSet->Tables->Item["Customers"];
       myRow = myTable->Rows->Item[0];
       String *strValue = "Jefferson";
       //Change the ContactName to Jefferson.
       myRow->set_Item("ContactName", strValue );
       
       if (myDataSet->HasChanges())
       {
           //Create a smaller DataSet containing only modified rows.
   	newDataSet = myDataSet->GetChanges();
           
           if(!newDataSet->HasErrors)
   	   {
   	     // If no errors, update the Customers table. 
   	     myDACust->Update(myDataSet, "Customers");	
                Console::WriteLine("Update was processed successfully");
   	   }
   	else 
   	{
   	   DataTable *newTable;						 
     	   // Check each table's HasErrors property.
   	   for (int i=0; i < newDataSet->Tables->Count-1; i++)
              // newDataSet->Tables ;
   	   {
   	      newTable = newDataSet->Tables->Item[i];
   
   	      // If HasErrors is true, reconcile errors.
   	      if(newTable->HasErrors) 
   	      {
   		// Use GetError() to get an array of all rows with errors.
   	 	rowsInError = newTable->GetErrors();
   
   		// Print the error of each column in each row.
   		for(int i = 0; i < rowsInError->Length; i++)
   		{
   		   DataColumn *newCol;
   		   for (int j=0; j< newTable->Columns->Count-1;j++) 
                        //while (newCol newTable->Columns)
   		   {
   		     newCol = newTable->Columns->Item[j];	
   	             Console::WriteLine(" {0} column has the following error: {1} ", 
   	 	    newCol->ColumnName, rowsInError[i]->GetColumnError(newCol));
   		    }
   		    // Clear the row errors.
   		    rowsInError[i]->ClearErrors();
   		 }
   	      }
   	   } 
           }
        } 
     }
     catch(Exception *e)
     {
       Console::WriteLine(e->Message->ToString());
      }
     __finally
     {
       // Close Connection to SQL
       myCn->Close();
     }
   
     Console::ReadLine();	
     return 0;
   }
   					

3. Modify the parameters of the ConnectionString property of the SqlConnection object as appropriate to connect to your SQL Server properly.
4. On the Debug menu in Visual Studio .NET Integrated Development Edition (IDE), click Run Without Debugging to execute the code and open a Console window.
   
   If no errors occurred during the update, the Console window displays the following message:

   Update was processed successfully.

   If errors occurred during the update, the Console window displays the errors and tells you in which columns the errors occurred.
5. Press ENTER to close the Console window and stop the application.

back to the top