Returning errors from stored procedures

  • Thread starter Thread starter Inge Buller
  • Start date Start date
I

Inge Buller

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
 
Inge said:
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
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBQFZEA4echKqOuFEgEQJWPACfdCyjlyOgLVl4kCOxjfl721K9dTcAl0cK
DkV6Kz2WcMDGG8dJsCAARjQ=
=LBla
-----END PGP SIGNATURE-----
 
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.
 
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
 
Back
Top