Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1288
  • Last Modified:

Catching RaiseError messages in VB

Given this stored procedure in MS SQL 2000 server:

CREATE PROCEDURE SG_Admin.usp_GetSG
@SG_ID int,
@Records int OUTPUT
 AS
set nocount on
Select * from SG where SG_ID = @SG_ID

Select @Records = Count(*) from SG where SG_ID = @SG_ID
If @Records > 1
begin
  RAISERROR ('There cannot be two of these!' , 14 , 1)
  return -1
end
return 0
GO

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>"
  Next

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.

Thanks,
Allen
0
allenstoner
Asked:
allenstoner
  • 8
  • 3
  • 3
  • +3
1 Solution
 
allenstonerAuthor Commented:
I'm using VB6 sp5 with Ado 2.6 in case it matters.

Allen
0
 
Dave_GreeneCommented:
Are we talking...

Err.Raise ??
0
 
allenstonerAuthor Commented:
I don't think so.  It has more to do with the Errors collection of an ADO connection.  The RaiseError is a t-sql command.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Dave_GreeneCommented:
Gotcha.  Interesting
0
 
allenstonerAuthor Commented:
I don't think so.  It has more to do with the Errors collection of an ADO connection.  The RaiseError is a t-sql command.
0
 
allenstonerAuthor Commented:
I don't think so.  It has more to do with the Errors collection of an ADO connection.  The RaiseError is a t-sql command.
0
 
Dave_GreeneCommented:
Use Reload Question in the upper right instead of refresh  :)  It will stop the duplicate posts
0
 
allenstonerAuthor Commented:
 Yeah, I figured out a little too late that the refresh was adding my comment again and again.

Thanks
0
 
ibroCommented:
try to replace
RAISERROR ('There cannot be two of these!' , 14 , 1)
with
RAISERROR ('There cannot be two of these!' , 16 , 1)

16 level of severity is most commonly used in raiseerror.
Maybe VB interprest severity of 14 just as warning.
 hope this helps
0
 
allenstonerAuthor Commented:
Doesn't make a difference.  I even tried 24 which is to be a fatal error although it may not be allowed to be generated by user processes.  VB does see the 14 IF it's the first line in the stored procedure.

Allen
0
 
nigelrivettCommented:
Does the VB go to the error handler.
I notice that you produce a recordset before raising the error - have you tried dealing with that first or disconnecting the recordset - the error info may be waiting to be passed after that.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Try this:
RAISERROR ('There cannot be two of these!' , 16 , 1) WITH SETERROR

CHeers

0
 
allenstonerAuthor Commented:
Here is the last part of the VB code.  I build the command object above this then this code processes what is returned.  So the recordset is being closed before I'm accessing the error collection of the connection.  I also tried the 'With SetError' but that didn't seem to do anything either.  Has anyone else seen this problem or am I the only one?

Thanks

  On Error GoTo BuildResult
  cmd.ActiveConnection = con
  Set rs = cmd.Execute
 
  '  Save the recordset to a stream for returning
  rs.Save stmRs, adPersistXML
  intRecords = rs.RecordCount
  rs.Close             '  Close and delete the recordset
  Set rs = Nothing
   
  '  Put output parameters include return value in an xml string for returning
BuildResult:
  strResult = "<SQLResults Records='" & intRecords & "' Errors='" & con.Errors.Count & "'>"
  strResult = strResult & "<Returns>"
  For Each Param In cmd.Parameters
    If (Param.Direction = adParamOutput) Or _
       (Param.Direction = adParamInputOutput) Or _
       (Param.Direction = adParamReturnValue) Then
      strResult = strResult & "<" & Param.Name & ">"
      strResult = strResult & Param.Value
      strResult = strResult & "</" & Param.Name & ">"
    End If
  Next
  strResult = strResult & "</Returns>"
  '  If there were errors put them in the xml file for returning
  strResult = strResult & "<Errors>"
  For Each error_item In con.Errors
    strResult = strResult & "<Error Num='" & error_item.NativeError & "'>"
    strResult = strResult & error_item.Description
    strResult = strResult & "</Error>"
  Next
  strResult = strResult & "</Errors>"
  strResult = strResult & "</SQLResults>"
  GoTo Clean_Up

'  Build an error response
errorHandler:
  strResult = "<SQLResults Records='0' Errors='1'>"
  strResult = strResult & "<Returns/>"
  strResult = strResult & "<Errors>"
  strResult = strResult & "<Error Num='" & Err.Number & "'>"
  strResult = strResult & Err.Description
  strResult = strResult & "</Error>"
  strResult = strResult & "</Errors>"
  strResult = strResult & "</SQLResults>"
 
Clean_Up:
  On Error Resume Next
  If Not (con Is Nothing) Then
    con.Close
  End If
  Set con = Nothing
  GetData = strResult                 '  Send strResult back as function result
  On Error GoTo 0
End Function
0
 
nigelrivettCommented:
Have heard of other people having this problem.
As it is OK when you raise the error without the recordset then I guess it is the recordset that is causing the problem.
Why not return this as an output parameter.
0
 
allenstonerAuthor Commented:
How do I return a recordset as an output parameter?
0
 
nigelrivettCommented:
I meant the @Records or the erroer message.
0
 
mironCommented:
I saw rs.NextRecordset for retrieving Error record. Is it possible that there are some select statements, that failed prior to the RAISERROR?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hi allenstoner.
I can confirm that the .nextrecordset will solve your problem.
As you have in fact 2 select statements prior to your RAISERROR, you need to use the .nextrecordset to catch the error...

BTW, you might replace the
Select @Records = Count(*) from SG where SG_ID = @SG_ID
by
SET @Records = @@rowcount

Cheers
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now