SQL Server Error Trapping in ASP/ASP.NET

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Havagan (Havagan_at_discussions.microsoft.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 12:51:06 -0800

I've run into an odd bit of behavior when dealing with SQL Server 2000 and
ASP/ASP.NET that I wanted to discuss.

The issue I encounter is that in a stored procedure, once a SELECT statement
succeeds and returns a recordset, all resulting errors are ignored by
ASP/ASP.NET in certain cases (see details below). Wouldn't you expect the
ADO/ADO.NET command to fail if the procedure it calls fails?

For example, I created a simple stored procedure that is meant to fail after
successfully returning a recordset.

--------------------------------------------------------------------------
CREATE PROCEDURE spt_TestSqlErrors

@ErrMsg VARCHAR(255) = '' OUTPUT

AS

--SUCCEEDS returns a recordset
SELECT 'Test' AS FirstName, 'User' AS LastName

--SUCCEEDS but does not return a recordset
DECLARE @SelectResults VARCHAR(255)
SELECT @SelectResults = FirstName FROM tblUsers WHERE UserID = 12345

--SUCCEEDS but updates 0 records
UPDATE tblPerson SET ID=0 WHERE 1=0

--FAILS divide by zero
SELECT 12/0 AS DivisionError

--SUCCEEDS and raises an error
SET @ErrMsg = 'Error Message.'
RAISERROR(@ErrMsg, 16, 1) WITH LOG

GO
--------------------------------------------------------------------------

With ASP.NET

Command.ExecuteNonQuery - will return an error (Divide By Zero)
Command.ExecuteDataReader - will not return an error (Divide By Zero)
Adapter.Fill - will return an error (Divide By Zero)

With ASP

Command.ExecuteNonQuery - err object does not register an error
Recordset.Open - err object does not register an error

Use whatever hypothetical situation you like to explain why a stored
procedure would return multiple recordsets and would raise an error if the
second recordset didn't exist. Wouldn't you expect the DivideByZero or
Raiserror to roll up to ASP and ASP.NET in all situations and not be ignored
by the ExecuteDataReader or by ASP completely?

What do you think?
Paul

Relevant Pages

  • RE: SQL Server Error Trapping in ASP/ASP.NET
    ... You should still encounter the ... > successfully returning a recordset. ... > Command.ExecuteNonQuery - will return an error (Divide By Zero) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Need help with sql and form
    ... SumOfPalletCount isn't referenced correctly. ... returned in the recordset, it should be referenced as rs, since the ... want to get the total from Sumand divide it from ... Set qdf = Nothing ...
    (microsoft.public.access.forms)
  • Re: Send Data to URL Without Browser
    ... >I've never opened a recordset via a URL, but I assume it does not open the ... >then creating the recordset at the asp level (our SQL Server is not on the ... >browser, do you think I can merely use this method to pass the query string ... condition is checked just prior to the code that opens the ...
    (microsoft.public.vb.general.discussion)
  • Re: Validate logins with ASP, MS Access and Cookies error
    ... If you plan to do much more ASP, I would advise looking for tutorials ... return another column from the database that stores the users 1st name ... 'create connection and recordset objects ... Set cnStr = Server.CreateObject ...
    (microsoft.public.inetserver.asp.db)
  • Re: Validate logins with ASP, MS Access and Cookies error
    ... If you plan to do much more ASP, I would advise looking for tutorials ... return another column from the database that stores the users 1st ... 'create connection and recordset objects ... Set cnStr = Server.CreateObject ...
    (microsoft.public.inetserver.asp.db)