How to Retrieve Info from RAISERROR Function in SQL Server DB

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

This article explains how to return the error message generated by the RAISERROR function in Microsoft SQL Server. You can use the RAISERROR function in a SQL stored procedure or trigger on a SQL Server database to set a global error number and return a user-defined error message.

MORE INFORMATION

When Visual Basic encounters a return value from the RAISERROR function, all it knows is that the "ODBC Call Failed" and Err is set to 3146. The entire ODBC error message is included in the Error$ function. To return the entire error message generated by the RAISERROR function, use the Error$ function in your error trap routine. For example:

   If Err = 3146 then
      MsgBox Error$
   End If

Unfortunately, the only way to return the error number set by RAISERROR is to include the number in the error message and parse the return string. However, the Microsoft Access 2.0/Visual Basic 3.0 Compatibility Layer appends the error number to the end of the error message automatically.

Step-by-Step Example

The following demonstrates an example stored procedure that generates a RAISERROR message.

1. In the SQL Administration Facility (SAF), run the following code to create the stored procedure:

   create procedure my_error @custname varchar(18) = null as if @custname is null

         begin
         raiserror 50001 'You Must Enter a Field Name! (#50001)'
   

   end else

         begin
         select * from TempTbl
         where TempTbl.Last_Name = @custname
   

   end

2. In Visual Basic, start a new project (ALT F, N). Form1 is created by default.

3. Place a command button (Command1) on the form and enter the following code in the Click() event:

      Sub Command1_Click ()
         Const DB_SQLPassThrough = 64  ' Set the passthrough constant.
   
         Dim db As database            ' Dimension the local variables.
         Dim ds As dynaset
         Dim conn As String
         Dim sql As String
   
         On Error GoTo Trap            ' Set up the error trap.
   
         ' Append the SQL Server database:
         conn = "odbc;dsn=texas;database=playpen;uid=sa;pwd=;"
         Set db = OpenDatabase("", False, False, conn)
   
         ' Run the stored procedure:
         sql = "my_error"
         Set ds = db.CreateDynaset(sql, DB_SQLPassThrough)
   
         ' Print the returned record (never get here because of the error).
         For i = 0 To ds.Fields.Count - 1
            Print ds(i)
         Next i
      Exit Sub
   
      Trap:
         If Err = 3146 Then   ' ODBC call failed.
             MsgBox Error$
         Else
             MsgBox "Error:" & Err & "-" & Error$
         End If
         On Error GoTo 0
         Exit Sub
      End Sub
   
   

4. Press the F5 key to run the program. Click the command button, and you should get error 3146 with the complete error message including the ODBC information and the following user-defined error message:

   You Must Enter a Field Name! (#50001)