How to Retrieve Info from RAISERROR Function in SQL Server DB (120763)

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
				

Note that this is different from performing a 'MsgBox Error$(Err)' command, which returns only the "ODBC Call Failed" message.

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.
   
   Note You must change Username= <username> and PWD =<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

      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;Username=<username>;PWD=<strong password>;"
         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)