Returning errors from stored procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Inge Buller - 15 Mar 2004 15:11 GMT

Hi!

I?m mostly a SQLServer guy, and I?m only using an Access adp to build a
REALLY quick and dirty "prototype GUI".

My app is based on stored procedures and I need to get to the RETURN code
and RAISERROR messags.
If I rightclick on a SP and run it, errormessages appear in a messagebox,
but not when I use VB code.
How could I do that?

Some code:

in SP "MySP":
RAISERROR('Error test',16,1)

Access VB

DoCmd.OpenStoredProcedure "MySP", acViewPreview, acReadOnly

Thanx in advance,
Inge

Reply to this Message

MGFoster - 16 Mar 2004 01:02 GMT

> Hi!
>
[quoted text clipped - 15 lines]
>
> DoCmd.OpenStoredProcedure "MySP", acViewPreview, acReadOnly

In DAO, if I ran an SP and an error occurred, the error(s) would be
returned in the Errors object.

In ADO you can use the Error object. See the Access VBA Help article
"Error Object." It has an example how to trap & display an error.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message

Inge Buller - 17 Mar 2004 09:09 GMT

Problem is, I?m not using ANY explicit ADO code at all. The whole idea is to
build the demo app in a couple of minutes...
So I?m doing:
DoCmd.OpenStoredProcedure "MySP", acViewPreview, acReadOnly
which is ALMOST the same as right-clicking on "MySP".

I have tried stuff like:
MsgBox CurrentProject.Connection.Errors(0).NativeError
but I never get to the errors from my SP:s.

> > Hi!
> >
[quoted text clipped - 37 lines]
> =LBla
> -----END PGP SIGNATURE-----

Reply to this Message

BJ Freeman - 24 Apr 2004 16:09 GMT

when you call the sp and it is returning a code
use :
set err = CurrentProject.connection.Execute "mysp"

from mysp

DECLARE
@Rows INT
,@Err INT
,@ExitStatus INT
,@Msg VARCHAR(255)
,@ErrReturn INT

----------------------------------------------------------------------------
------
-- Initializations
----------------------------------------------------------------------------
------

SELECT
@ErrReturn = 1001 -- Error ID
,@ExitStatus = 0
,@FALSE = 0
,@TRUE = 1
----------------------------------------------
do something
---------------------------------------------
SELECT @Err = @@Error, @Rows = @@RowCount

IF @Err <> 0
BEGIN
SELECT @Msg = ' An Error occurred while selecting records from
WQMenuHierarchy.'
GOTO ExitOnError
END

GOTO ExitNow
----------------------------------------------------------------------------
---------
-- Exit on error
----------------------------------------------------------------------------
---------
ExitOnError:
SELECT @ExitStatus = @ErrReturn
GOTO ExitNow
----------------------------------------------------------------------------
---------
-- Exit
----------------------------------------------------------------------------
---------
ExitNow:
IF @ExitStatus = 0
BEGIN
IF @Rows = 0
RETURN (@ErrReturn)
ELSE
RETURN (@ExitStatus)
END
ELSE
BEGIN
RAISERROR(@Msg,17,1)
END

> Hi!
>
[quoted text clipped - 18 lines]
> Thanx in advance,
> Inge

Reply to this Message