Solved

Catching RaiseError messages in VB

Posted on 2001-07-27
18
1,276 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
  • 8
  • 3
  • 3
  • +3
18 Comments
 

Author Comment

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

Allen
0
 
LVL 8

Expert Comment

by:Dave_Greene
Comment Utility
Are we talking...

Err.Raise ??
0
 

Author Comment

by:allenstoner
Comment Utility
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
Comment Utility
Gotcha.  Interesting
0
 

Author Comment

by:allenstoner
Comment Utility
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
Comment Utility
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
Comment Utility
Use Reload Question in the upper right instead of refresh  :)  It will stop the duplicate posts
0
 

Author Comment

by:allenstoner
Comment Utility
 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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:allenstoner
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

CHeers

0
 

Author Comment

by:allenstoner
Comment Utility
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
Comment Utility
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
Comment Utility
How do I return a recordset as an output parameter?
0
 
LVL 18

Expert Comment

by:nigelrivett
Comment Utility
I meant the @Records or the erroer message.
0
 
LVL 9

Accepted Solution

by:
miron earned 50 total points
Comment Utility
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 142

Expert Comment

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now