471,344 Members | 1,532 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,344 software developers and data experts.

ExecuteReader() not firing exceptions from stored procedures, also HasRows() vs Read()

Hi All,

We're using the 1.1. Framework against SQL Server 2000, and are having a
strange issue where we don't get errors back from the stored procedure, i.e.
the exception never gets thrown.

The scenario is this:
1.) Set up SqlConnection and SqlCommand
2.) Set SqlDataReader equal to the return value from
SqlCommand.ExecuteReader
3.) Check SqlDataReader.HasRows() to see if there is any data in the stream

If we do these things, but do not call SqlDataReader.Read(), we never get
any exceptions from the stored procedure.
Our understanding was that you want to check SqlDataReader.HasRows() before
doing anything, in case there was no data returned.
This apparently does not work.
The mysterious part is why the exception does not fire on step #2
(SqlCommand.ExecuteReader() ).
We use SqlDataReader objects to stream stored procedure results back when we
have a smallrowsets.

Normally, our code looks like so:

'...set up connection and command...
rdr = cmd.ExecuteReader

While rdr.Read
'Process row...
Loop

'et cetera...

In this case, the exception fires normally.
However, one of our developers tried:

If rdr.HasRows Then
While rdr.Read
'Process row..
Loop
End If

This results in the total suppression of SQL Server errors that occurred in
the stored procedure.

Why does this occur?
Of what use is HasRows() if it doesn't throw the exceptions that are
apparently waiting in the SqlDataReader stream?

Are there any other "scenarios" we should be aware of which would suppress
the SQL Exceptions? This is scary.

Here is the necessary repro information:
/*SQL Server 2000*/

USE Northwind
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[ADOTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ADOTest]
GO

IF EXISTS (SELECT * from dbo.sysobjects WHERE id =
object_id(N'[dbo].[selADOTest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[selADOTest]
GO

CREATE TABLE
ADOTest
(
DateVal varchar(19) NOT NULL
)
GO

INSERT INTO ADOTest (DateVal) VALUES ('2005-02-2811:55.000')
GO

CREATE PROCEDURE dbo.selADOTest
AS

SET NOCOUNT ON

SELECT CONVERT(datetime, DateVal) AS DateVal FROM ADOTest
GO

GRANT EXECUTE ON selADOTest TO public
GO

'VB.Net: (Paste code into the click event of a button on a Windows Form):

Dim conn As System.Data.SqlClient.SqlConnection, cmd As
System.Data.SqlClient.SqlCommand, rdr As System.Data.SqlClient.SqlDataReader

Dim hasRows As Boolean

Try

conn = New
System.Data.SqlClient.SqlConnection("SERVER=DEV;DA TABASE=Northwind;Integrated
Security=SSPI;")

conn.Open()

cmd = conn.CreateCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "dbo.selADOTest"

rdr = cmd.ExecuteReader

If rdr.HasRows Then

While rdr.Read

Debug.WriteLine(rdr("DateVal"))

End While

End If

MsgBox("Successfully executed stored procedure.")

Catch sqlEx As System.Data.SqlClient.SqlException

MsgBox("SQLException: " & sqlEx.Message)

Catch appEx As ApplicationException

MsgBox("ApplicationException: " & appEx.Message)

Finally

If Not IsNothing(rdr) Then

rdr.Close()

rdr = Nothing

End If

If Not IsNothing(cmd) Then

cmd = Nothing

End If

If Not IsNothing(conn) Then

If conn.State = ConnectionState.Open Then

conn.Close()

End If

conn = Nothing

End If

End Try

'Try it with the "If rdr.HasRows() Then.." and also without.
'You'll see what I mean.
'The exception will not fire with the HasRows() check in place.


Thanks,
James Hokes
Jul 21 '05 #1
0 2286

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
2 posts views Thread by Maks Romih | last post: by
5 posts views Thread by Givisiez | last post: by
reply views Thread by davek604 | last post: by
reply views Thread by Ronak mishra | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.