PRB: ADO Errors Collection Does Not Contain User-Defined Error Messages (254304)

MORE INFORMATION

Steps to Reproduce Behavior

1. Launch the SQL Server Query Analyzer and add a Table called Test to the Pubs database in SQL Server by using the following SQL statement:

   CREATE TABLE TEST (
   Empno char(5) PRIMARY KEY, Empname varchar(20))

2. Create an SP called INSTEST in the pubs database by running the TSQL block given here:

   create procedure INSTEST (@mempno char(5),@mempname varchar(20))
   as
   Insert into Test values (@mempno,@mempname)
   if (@@error != 0) 
   begin
      raiserror ('Invalid Insert',16,1)
   end

3. Execute the following Insert statement in the pubs database to insert a row into the TEST table:

     EXEC INSTEST 'E001','aaa'

4. Open a Standard EXE project in Visual Basic, and drop a CommandButton control on Form1.
5. Change the caption of the CommandButton to Execute INSTEST.
6. Place the following line of code in the Form's General Declarations section to declare an ADO Connection object:

    Dim cn As ADODB.Connection

7. Place the following lines of code in the Form_Load() event to establish an ADO Connection to your SQL Server pubs database by using the SQLOLEDB provider. Make sure to change the connection string properties so that they point to your SQL Server installation:
   
   Note You must change User ID=<user name> to the correct value before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

   Set cn = New ADODB.Connection
   cn.Open "Provider=SQLOLEDB;Data Source=YourSQLServer;Initial Catalog=pubs;User ID=<user name>"

8. Place the following segment of code in the Click event of the CommandButton:

   Private Sub Command1_Click()
       On Error GoTo errproc:
   
       cn.Execute "EXEC INSTEST 'E001','aaaa'"
       Exit Sub
   
   errproc:
       Dim merr As ADODB.Error
       For Each merr In cn.Errors
           MsgBox merr.Number & " - " & merr.Description
       Next
   End Sub

   The preceding code attempts to insert a row into the TEST table by using the INSTEST SP that would create a duplicate entry in the primary key column [Empno] if added. This causes the RAISERROR statement in the SP that returns the user-defined error message to be executed.
   
   
9. When you run the project and click on the Execute INSTESTCommandButton, you would expect to see the user-defined message Invalid Insert that is raised in the SP in the ADO errors collection. However, note that when the NOCOUNT option is turned OFF (the default setting), the ADO Errors collection does not contain the user-defined error message.
10. Modify the code in the Click event of the CommandButton to insert calls to the SET NOCOUNT ON and SET NOCOUNT OFF statements as shown here:

    Private Sub Command1_Click()
        On Error GoTo errproc:
    
        cn.Execute "SET NOCOUNT ON"
        cn.Execute "EXEC INSTEST 'E001','aaaa'"
        cn.Execute "SET NOCOUNT OFF"
    
        Exit Sub
    
    errproc:
        Dim merr As ADODB.Error
        For Each merr In cn.Errors
            MsgBox merr.Number & " - " & merr.Description
        Next
        cn.Execute "SET NOCOUNT OFF"
    End Sub

11. Note that the NOCOUNT option is being turned ON prior to executing the SP. It is being turned OFF immediately after executing the SP and in the error handler to ensure that the setting for this option is restored to its default value (assuming that it is OFF) irrespective of whether an error occurs while executing the INSTEST SP.
12. Run the project and click the Execute INSTEST button. Note that the user-defined message Invalid Insert is listed in the Connection object's ADO Errors collection.NOTE: If the NOCOUNT option has been turned ON in your database, then all user-defined error messages raised in SPs by using the RAISERROR statement are populated in the ADO Errors collection when you use the SQLOLEDB Provider.