Microsoft KB Archive/811482

= INF: Handling SQL Server Error Messages When a Stored Procedure is Used in the SqlDataAdapter UpdateCommand Method =

Article ID: 811482

Article Last Modified on 5/8/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft ADO.NET 1.1

-



SUMMARY
When the UpdateCommand method of the SqlDataAdapter object uses a SQL Server

stored procedure that raises an error after it returns a result set, the ADO.NET client

application may not trap the error raised by SQL Server. However, you can use the

SqlDataReader object to trap such error messages. This article describes how to

use the SqlDatareader object to trap the error raised by the stored procedure.



MORE INFORMATION
The SqlDataAdapter object serves as a bridge between a DataSet object and a

SQL Server database. SqlDataAdapter is an intermediary object that populates

a DataSet object with data that is retrieved from a SQL Server database. By using the

UpdateCommand method of SqlDataAdapter, you can update the database to reflect

the changes (such as inserts, updates, and deletes) made to the DataSet object.

You can use a Transact-SQL command or a stored procedure in the UpdateCommand

method. If a stored procedure is used in the UpdateCommand method of SqlDataAdapter,

the client may not receive the error messages that are raised by the stored procedure

after the result set is returned.

Note: Manipulating the SET NOCOUNT or the severity level of the RAISERROR message in

the stored procedure does not change this behavior.

You can use SqlDataReader as an alternative solution to this problem. The SqlDataReader

provides more flexibility in handling store procedures that return multiple result sets. The sample code, in the section that follows, demonstrates how to use SqlDataReader to update a data source and trap the error message that is raised by the stored procedure.

Create a Sample SQL Server Table and Stored Procedure

To create a SQL Server table and stored procedure that you can use in the Visual C# .NET

code samples, follow these steps:  Open SQL Query Analyzer, and then connect to the database where you want to create the sample table. The code samples in this article use the Northwind database that is included with Microsoft SQL Server.  To create a sample table that is named CustTest, and to insert a record into the table, run the following Transact-SQL statements: Create Table CustTest (   CustID int primary key,    CustName varchar(20) )

Insert into CustTest values (1,'John') Insert into CustTest values (2,'Joe')   Create a stored procedure named updateCustTest. You can use this stored procdure to reflect the modified rows in the DataSet back to the source database. The updateCustTest stored procedure returns a recordset, updates the database and raises an error by using RAISERROR. To create the updateCustTest stored procedure, run the following Transact-SQL statements: Create procedure updateCustTest(@Custid varchar(20), @custName varchar(20))as Begin select custid, custName from custTest where custid= @custid update custTest set custName = @custName where custId = @custid RAISERROR('Test error raised by Raiserror',16,1) End 

Visual C# .NET Client Application

This section describes the steps to follow to reproduce the behavior and the steps to follow to resolve the behavior.  To create a new Visual C# .NET console application, follow these steps:  Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project.</li> Click Visual C# Project under Project Types, and then click Console Application under Templates.</li></ol> </li>  Replace the default contents of Class1 with the following code: using System; using System.Data; using System.Data.SqlTypes; using System.Data.SqlClient; using System.Data.Common;

namespace SQLDataAdapterConsole {   class Class1 {       public static void Main {           SqlConnection cn = new SqlConnection; SqlDataAdapter sqlDa; //Set the connection string of the SqlConnection object to connect //to the SQL Server database where you created the sample table. cn.ConnectionString = &quot;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=northwind;Data Source=ServerName&quot;; cn.Open;

//Initialize the SqlDataAdapter object by specifying a Select command //that retrieves data from the sample table. sqlDa = new SqlDataAdapter(&quot;select * from custTest&quot;, cn); //This function uses Uptade method of DataAdapter to update the modified DataSet data in the source database //Notice that the error raised by the Update Command Stored Procedure cannot be trapped UpdateUsingDataAdapter(sqlDa); //This function uses DataReader to update the modified data in the source database //Notice that the error raised by the Update Command Stored Procedure can be trapped UpdateUsingDataReader(sqlDa);

cn.Close; Console.ReadLine; }

// This function uses Update method of SqlDataAdapter for Updating the data public static void UpdateUsingDataAdapter(SqlDataAdapter da) {           Console.WriteLine(&quot;Updating Database using Update Command of SqlDataAdapter&quot;); SqlCommand updateCmd; DataSet CustomersDataSet = new DataSet; //Create update command for SqlDataAdapter updateCmd = new SqlCommand(&quot;exec updateCustTest @pCustId, @pCustName&quot;, da.SelectCommand.Connection); //Add parameters for the stored procedure updateCmd.Parameters.Add(new SqlParameter(&quot;@pCustName&quot;, SqlDbType.VarChar)); updateCmd.Parameters[&quot;@pCustName&quot;].SourceVersion = DataRowVersion.Current; updateCmd.Parameters[&quot;@pCustName&quot;].SourceColumn = &quot;CustName&quot;;

updateCmd.Parameters.Add(new SqlParameter(&quot;@pCustId&quot;, SqlDbType.VarChar)); updateCmd.Parameters[&quot;@pCustId&quot;].SourceVersion = DataRowVersion.Original; updateCmd.Parameters[&quot;@pCustId&quot;].SourceColumn = &quot;CustId&quot;;

//Initialize the UpdateCommand of SqlDataAdapter da.UpdateCommand = updateCmd;

//Populate the DataSet by running the Fill method of the SqlDataAdapter. da.Fill(CustomersDataSet,&quot;custTest&quot;); //Write out the value in the CustName field before updating the data using the DataSet. Console.WriteLine(&quot;Customer Name before Update : &quot; + CustomersDataSet.Tables[&quot;CustTest&quot;].Rows[0][&quot;CustName&quot;]);

//Modify the value of the CustName field. CustomersDataSet.Tables[&quot;CustTest&quot;].Rows[0][&quot;CustName&quot;] = &quot;Bill&quot;;

// Get the dataSet that is modified. DataSet modifiedDS = CustomersDataSet.GetChanges(DataRowState.Modified); try {               da.Update(modifiedDS,&quot;CustTest&quot;); Console.WriteLine(&quot;Customer name is updated to 'Bill'.&quot;); Console.WriteLine(&quot;The Stored Procedure error is not trapped.&quot;); }           catch {               Console.WriteLine(&quot;Stored Procedure error trapped.&quot;); }       }

//This function uses DataReader for updating the public static void UpdateUsingDataReader(SqlDataAdapter da) {           Console.WriteLine(&quot;\n\nUpdating Database using DataReader to trap the Stored Procedure error.&quot;);

DataSet CustomersDataSet = new DataSet; SqlDataReader mySqlDataReader; //Populate the DataSet by running the Fill method of the SqlDataAdapter. da.Fill(CustomersDataSet,&quot;custTest&quot;);

//Modify the value of the CustName field. CustomersDataSet.Tables[&quot;CustTest&quot;].Rows[0][&quot;CustName&quot;] = &quot;Michel&quot;; // Get the dataSet that is modified. DataSet modifiedDS = CustomersDataSet.GetChanges(DataRowState.Modified); SqlCommand updateCmd; foreach (DataRow dr in modifiedDS.Tables[0].Rows) {               // Set the update command for modified data in the DataSet. updateCmd = new SqlCommand(&quot;updateCustTest '&quot; + dr[0] +&quot;', '&quot; + dr[1] + &quot;'&quot;, da.SelectCommand.Connection); try {                   // Execute the update command. mySqlDataReader = updateCmd.ExecuteReader; while(!mySqlDataReader.IsClosed) {                       while (mySqlDataReader.Read) {                           Console.WriteLine(&quot;Customer name returned by the procedure :&quot; + mySqlDataReader.GetValue(1)); }                       // Get the results set returned by the update command. mySqlDataReader.NextResult; Console.WriteLine(&quot;\n&quot;); }                   mySqlDataReader.Close; Console.WriteLine(&quot;Customer Name updated successfully&quot;); }               catch(SqlException e)                { // Get the error collection and write the error on the console. Console.WriteLine(&quot;Error Number is : &quot; + e.Number); Console.WriteLine(&quot;Error Message is : &quot; + e.Message);

}           }        }    } } </li> Modify the connection string as appropriate for your environment.</li> Save, and then run the application. Watch the message as the program executes.</li> Press any key to dismiss the console window and stop the application.</li></ol>

<div class="references_section">