Solved

Catching RaiseError messages in VB

Posted on 2001-07-27
18
1,284 Views
Last Modified: 2007-11-27
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
Comment
Question by:allenstoner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +3
18 Comments
 

Author Comment

by:allenstoner
ID: 6328001
I'm using VB6 sp5 with Ado 2.6 in case it matters.

Allen
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328017
Are we talking...

Err.Raise ??
0
 

Author Comment

by:allenstoner
ID: 6328035
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328059
Gotcha.  Interesting
0
 

Author Comment

by:allenstoner
ID: 6328060
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
 

Author Comment

by:allenstoner
ID: 6328078
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
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328311
Use Reload Question in the upper right instead of refresh  :)  It will stop the duplicate posts
0
 

Author Comment

by:allenstoner
ID: 6331803
 Yeah, I figured out a little too late that the refresh was adding my comment again and again.

Thanks
0
 
LVL 3

Expert Comment

by:ibro
ID: 6335779
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
 

Author Comment

by:allenstoner
ID: 6335845
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6335997
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6336745
Try this:
RAISERROR ('There cannot be two of these!' , 16 , 1) WITH SETERROR

CHeers

0
 

Author Comment

by:allenstoner
ID: 6337819
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6337983
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
 

Author Comment

by:allenstoner
ID: 6337999
How do I return a recordset as an output parameter?
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6345692
I meant the @Records or the erroer message.
0
 
LVL 9

Accepted Solution

by:
miron earned 50 total points
ID: 6459491
I saw rs.NextRecordset for retrieving Error record. Is it possible that there are some select statements, that failed prior to the RAISERROR?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6492836
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question