HasRows() vs. Read() and ERROR suppression

[James Hokes]

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.


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.


Thanks,
James Hokes

 

[Marina]

The exception would have fired on the ExecuteReader line. This would have
occurred before the If statement or the loop, so I don't see how the code
change would have any effect.

 

[Scott M.]

What type of SQL exception are you running into?

Have you tried:

Try
rdr = cmd.ExecuteReader

If rdr.HasRows Then
While rdr.Read
'Process row..
Loop
End If
Catch ex as Exception
'examine exception
End Try

 

[James Hokes]

Marina,

The exception would have fired on the ExecuteReader line. This would have
occurred before the If statement or the loop, so I don't see how the code
change would have any effect.

Sorry, no...that's what you would assume, but you would be incorrect in this
instance.

This is the crux of the issue; the exception does _not_ fire, unless
rdr.Read() is executed. It waltzes right past the cmd.ExecuteReader, and
will only fire if you call rdr.Read. If you pre-emptively call rdr.HasRows
to see whether or not there are rows, the exception will not fire. If,
however, you step to the rdr.Read() line, you'll get the exception every
time.

Any other ideas? I'll be preparing a repro script now.

Thanks,
James Hokes

 

[Marina]

I wasn't able to find any other reports of this, but that doesn't mean it
can't be a bug.

At least you already know the workaround...

 

[James Hokes]

Marina,

All,

Here's what you need to reproduce the issue (this demonstrates the type
conversion error I was expecting from the procedure call):

/*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;DATABASE=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.

Many thanks,
James Hokes

 

[James Hokes]

Scott,

Specifically, it is a type conversion error, Level 16, State 1.

Here's the info:
Server: Msg 241, Level 16, State 1, Procedure selADOTest, Line 7
Syntax error converting datetime from character string.

However, as I stated in the post, the error does not fire if you
pre-emptively check .HasRows() and avoid calling .Read().
I would have thought the exception would be thrown upon .ExecuteReader() on
the Command object, but it does not. Apparently, for some reason you MUST
call .Read() on the SqlDataReader, or you don't get the exception.

Baffling.

I posted a repro script (both T-SQL and VB.Net) in the other part of the
thread where I'm conversing with Marina.

Thanks,
James Hokes

 

[Scott M.]

That's because ExectueReader doesn't return any data, just an object that
can look at the data.

The Read method (when called for the first time) advances you to the first
record found by executing the commandText. If you don't call Read, you are
never attempting to process any data.

 

[James Hokes]

Scott,

I am aware of that fact at this point.

What I am really wondering, though, is what is the utility of HasRows()?
It seems worthless at first glance, but that doesn't sit right with me.


Thanks,
James Hokes

 

[Scott M.]

HasRows will tell you if there is data to look at but it will not attempt to
actually fetch that data.

 

[James Hokes]

Scott,

That sounds good; in fact, it's exactly what we thought...

The big, giant, screaming "HOWEVER" is the fact that it won't tell you if
there were errors.

So, once again, I'm forced to classify it as useless. What's the point of
saying:

Code: "Are there Rows?"
DataReader: "No, don't worry about it. There's no data."
Code: "Ok, I'll just blithely continue onward."
DataReader: "Ok, no problem. Bye."

Seems to me that HasRows() is broken. It is of no use to check for rows if
it doesn't also check for exceptions.
Checking HasRows() without checking Read() makes no sense. However, since
you can just do this:

While .Read()
'Do actions..
Loop

I'll be eliminating the use of HasRows() 'til I hear differently.

Thanks,
James Hokes

 

[Scott M.]

James,

You just need to change what your expectations of HasRows is...

Why should HasRows tell you if you have an exception as you try to retrieve
the 4th field from the 9th row and assign it to a type that does not match
what type it really is? That's not what HasRows or for that matter Read are
for.

You WILL get an exception at ExecuteReader only if your Command is
mal-formed, not if there is no data returned or the data isn't what you
expected it would/should be.

So, as always, the best course of action is to use Try...Catch as you work
with the data and deal with any exceptions there.

 

[James Hokes]

Scott,

I really appreciate your efforts.

However, I don't think you and I are getting our wires matched up properly.

Here's what I posted several messages up the chain (My 1st response to you):

*****************************************************
Specifically, it is a type conversion error, Level 16, State 1.

Here's the info:
Server: Msg 241, Level 16, State 1, Procedure selADOTest, Line 7
Syntax error converting datetime from character string.
*****************************************************

This is verbatim from the SQL Server, as output by Query Analyzer.
Nowhere did I indicate that it was an error in my use of what the reader
gave me.
Nor am I so stupid not to realize that I cannot expect HasRows() to handle
all of my application exceptions automatically.
Nor am I so stupid as to think that if I actually "put the 4th field from
the 9th row and assign it to a type that does not match what type it really
is" and got an exception, that I would have expected HasRows() to mop up for
me? WTF?

I also told you (in the aforementioned post), that I posted a repro script
to the other part of this thread.

BOTTOM LINE:
THE STORED PROCEDURE WILL NOT RUN.
IT THROWS A "SQL" ERROR, DETAILED ABOVE.
HENCE, THE .ExecuteReader LINE SHOULD FAIL.
IT DOES NOT; NEITHER DOES THE CALL TO HasRows().
Only if we execute .Read() does the SQLException fire.

This has nothing to do with invalid expectations, or placing "the 4th field
from the 9th row and assign it to a type that does not match what type it
really is".
It has everything to do with these facts:

a.) The stored procedure fails spectacularly with an error Level 16, State
1.
b.) The .ExecuteReader method call runs silently, and does not throw the
SqlException ***AT ALL***
c.) The call to .HasRows() on the SqlDataReader simply returns False,
without firing the exceptions either.
d.) Only when you call .Read() do you get the exception fired.

Hence, I can only reason that HasRows() is broken. Why call HasRows() only
to have to call .Read() anyway in case of errors?

I apologize if I seem somewhat short with you, but I have re-explained this
several times now.

Thanks,
James Hokes

 

[Scott M.]

You don't seem somewhat short, you seem somewhat rude.

 

[James Hokes]

That's because my "rude" bit gets turned on after I have to repeat myself
more than TWICE.

Thanks for wasting your time on an unrelated issue.

James Hokes