HOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in ADO.NET

Article translations Article translations
Article ID: 321903 - View products that this article applies to.
This article was previously published under Q321903
Expand all | Collapse all

On This Page

Summary

This article describes how to display both SQL Server errors and warnings in ADO.NET. If you raise an error that has a severity level of 10 or less, which is a warning, no exception is raised. This warning is not populated into the SqlErrorCollection. Instead you must hook into the InfoMessage event of the Connection object and handle the message there.

Errors in SQL stored procedures with a severity level of 11 through 20 throw an error in SQL that is caught in the SqlErrorCollection. Also note that some errors that have a severity level of 11 or greater abort the whole stored procedure.


Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft Visual Studio .NET installed on a compatible Microsoft Windows operating system
  • A SQL server with the sample Northwind database installed
This article assumes that you are familiar with the following topics:
  • Visual Basic .NET
  • ADO.NET data access

Create the Project and Add the Stored Procedure in SQL Server

The following sample uses a combination of Sev 10 and Sev 11 errors to demonstrate how you can use ADO.NET to display error messages with different severity levels. This sample also demonstrates how you can use IF...ELSE logic in combination with RAISERROR to provide error handling in your SQL Stored procedures.

The sample performs a simple search on the Customers table in the SQL Server Northwind database. If one of the CustomerIDs is not found, a SEV 10 exception is raised. If both of the CustomerIDs are not found, a SEV 11 exception is raised, and the stored procedure aborts. Note that you must add a handler to hook into the InfoMessage event of the Connection object to display errors that have a severity level of 10 or less.
  1. Follow these steps to create a new Visual Basic Windows Application project:
    1. Start Visual Studio .NET.
    2. On the File menu, point to New, and then click Project.
    3. In the New Project dialog box, click Visual Basic Projects under Project Types, click Windows Application under Templates, and then click OK.
  2. In Server Explorer, configure a new data connection to the Northwind sample database on an available SQL Server.
  3. Expand the Northwind database node, right-click stored procedures, and then click the new stored procedure.
  4. In the code window of your stored procedure, paste the following stored procedure code:
    CREATE proc GetCustomers 
    @CustomerID1 nvarchar (5),
    @CustomerID2 nvarchar (5)
    AS
    DECLARE @err_message nvarchar(255)
    
    --if no customers found return SEV 11 error to abort stored procedure
    IF NOT EXISTS (Select CustomerID from Customers where CustomerID = @CustomerID1 or CustomerID =  @CustomerID2) 
    	BEGIN	
    	SET @err_message = @CustomerID1 + ' and ' + @CustomerID2 + ' Not found raise sev 11'
    	RAISERROR (@err_message, 11,1)
    	END
    
    --if 1st customerid not found throw SEV 10 exception
    IF EXISTS (Select CustomerID from Customers where CustomerID = @CustomerID1)
    	BEGIN
    	select * from Customers where CustomerID = @CustomerID1
    	END
    ELSE
    	BEGIN
    	SET @err_message = @CustomerID1 + ' not found raise sev 10'
                 RAISERROR (@err_message,10, 1) 	
    	END
    
    --if 2nd customerid not found throw SEV 10 exception
    IF EXISTS (select * from Customers where CustomerID = @CustomerID2)
    	BEGIN
    	select * from Customers where CustomerID = @CustomerID2
    	END
    ELSE
    	BEGIN
    	SET @err_message = @CustomerID2 +  ' not found raise sev 10'
        	RAISERROR (@err_message,10, 1) 
    	END
  5. Close the stored procedure code window, and then click Yes to save the changes.

Add Code to Display Errors and Warnings

  1. In the code window of Form1, or your Visual Basic .NET project, add a reference to System.Data.SQLClient by adding the following line to the top of your Form1.vb file:
    Imports System.Data.SqlClient
    					
  2. Drag 2 TextBox controls (TextBox1 and TextBox2) from the toolbox to Form1.
  3. Press F4 to change the Text property of TextBox1 to ALFKI.
  4. Press F4 to change the Text property of TextBox2 to ANATR.
  5. Drag a Button control (Button1) from the toolbox to Form1, and then press F4 to change the Text property to Search.
  6. Double-click Button1, and then add the following code to the Button1_Click event handler:

    Note You must change the User ID <username> value and the password =<strong password> value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.
            Try
                Dim cn As New SqlConnection("Server=servername;Database=Northwind;User Id=<username>;Password=<strong password>")
                AddHandler cn.InfoMessage, AddressOf cn_InfoMessage
    
                cn.Open()
                Dim cmd As New SqlCommand("GetCustomers")
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = cn
    
                cmd.Parameters.Add(New SqlParameter("@CustomerID1", SqlDbType.NVarChar, 5))
                cmd.Parameters.Add(New SqlParameter("@CustomerID2", SqlDbType.NVarChar, 5))
    
                cmd.Parameters("@CustomerID1").Value = TextBox1.Text
                cmd.Parameters("@CustomerID2").Value = TextBox2.Text
    
                Dim myReader As SqlDataReader = cmd.ExecuteReader()
                Dim RecordCount As Integer = 0
    
                'You must check the nextresult method because there is a possiblity that the
                'stored procedure may return multiple resultsets; the NextResult method
                'positions the DataReader at the next result in the resultset if you 
                'have multiple results, otherwise it will return false.
                Do
                Do while myreader.read()
                Debug.WriteLine("Found customer ID : " & myReader(0))
                Loop
                Loop While myreader.NextResult()
    
                myReader.Close()
    
            Catch SqlEx As SqlException
                Dim myError As SqlError
                            Debug.WriteLine("Errors Count:" & SqlEx.Errors.Count)
                For Each myError In SqlEx.Errors
                    Debug.WriteLine(myError.Number & " - " & myError.Message)
                Next
            End Try
    					
  7. Modify the connection string to point to the Northwind database on your SQL server.
  8. To display errors that have a severity level of 10 or less, add the following sub to handle the InfoMessage event:
        Public Sub cn_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
            Debug.WriteLine("info message event: " & e.Message)
        End Sub
    					
  9. Press CTRL+F5 to run the program. Click Button1 and notice in the Output window that you do not see CustomerIDs ALFKI and ANATR. To make sure that the Output window is visible, press CTRL+ALT+O.

Generate and Handle a SEV 10 Warning

Change the value in TextBox1 from ALFKI to AAAAA, and then click Button1 to try to select a value that does not exist in the Customers database. The custom error code in the stored procedure traps this scenario and raises a SEV 10 exception. You see that a SEV 10 exception is raised and displayed by using the InfoMessage event of the Connection object. Error messages that have a severity level of 10 or less are treated as informational messages, and are not returned as errors or in query results.

Generate and Handle a SEV 11 Exception

In TextBox1 change the value from ALFKI to AAAAA, and in TextBox2, change the value from ANATR to AAAAA, and then click Button1. This does not find both values in the Customers database, and the stored procedure raises a SEV 11 exception. You now see the SEV 11 error in the SQLErrorCollection with your custom error message.

References

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
308049 HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET
309490 HOW TO: Handle Multiple Results by Using the DataReader in Visual Basic .NET
316549 HOW TO: Debug Stored Procedures in Visual Studio .NET
321902 HOW TO: Call Stored Procedures with Optional Values in ADO.NET
308051 PRB: Output Parameters Are Not Returned When You Run an ADO.NET Command in Visual Basic .NET

Properties

Article ID: 321903 - Last Review: October 15, 2012 - Revision: 7.0
Applies to
  • Microsoft Visual Studio .NET 2002 Professional Edition
  • Microsoft Visual Studio .NET 2003 Professional Edition
  • Microsoft ADO.NET 1.1
Keywords: 
kbhowtomaster kbsqlclient kbsystemdata KB321903

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com