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
