473,239 Members | 1,795 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 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 2471

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
5
by: Rhino | last post by:
I am trying to determine the behaviour of stored procedures in DB2 V8.2.x in Windows/Unix/Linux and how I can control that behaviour. Some documentation in the manuals is confusing the issue...
0
by: James Hokes | last post by:
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. ...
2
by: Maks Romih | last post by:
I'm not experienced in db2, but I have to write some rather complex processing with stored procedures for db2 on as400. The database consists of some twenty tables and is prepared in advance so...
4
by: phil | last post by:
Hi, With the code below, i get the error: ExecuteReader: Connection property has not been initialized. Description: An unhandled exception occurred during the execution of the current web...
5
by: Givisiez | last post by:
Hi I'm programming a windows application. I store values on a SQL- database. Now I've a problem reading the values stored. I want to read a whole column from a table. I tried my SQL Command in...
0
by: davek604 | last post by:
Hello The code below is part of a program which gets usernames from a MySQL database. If the username isn't in the database I want to call another form to add the user + some other details. The form...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.