Catching RaiseError messages in VB
Posted on 2001-07-27
Given this stored procedure in MS SQL 2000 server:
CREATE PROCEDURE SG_Admin.usp_GetSG
@Records int OUTPUT
set nocount on
Select * from SG where SG_ID = @SG_ID
Select @Records = Count(*) from SG where SG_ID = @SG_ID
If @Records > 1
RAISERROR ('There cannot be two of these!' , 14 , 1)
How can I get the text of the error that is being raised? I'm going through the errors collection of the connection object but it doesn't appear in it. I know it getting there because I'm getting the return value of -1. Here's the VB code I'm using to put the errors into a portion of an XML document.
For Each error_item In con.errors
strResult = strResult & "<Error Num='" & error_item.NativeError & "'>"
strResult = strResult & error_item.Description
strResult = strResult & "</Error>"
If I move my RaiseError to the first line of the stored procedure the VB code sees it but that isn't to useful for a production system. I did get other errors for example if I put an invalid table name in one of the selects.