Re: SqlCommand.ExecuteReader returning nothing?

I have never heard of this happening. It is more likely that the 'reader'
object is nothing because there was an error trying to execute the query or
open the connection, and so that variable never got set correctly. Not
because the ExecuteReader call completed with no error, but somehow failed
to return a valid object.

"Dan A" <quagmire666@gmail.com> wrote in message
news:29867E59-1803-41CE-BD10-AD0C36221D4D@microsoft.com...
> In a web application we have, we are doing a simple query (requesting 3
> fields from a inner joing b/t 2 tables). This code is executed
> constantly,
> but once a week, the sqlcommand.ExecuteDataReader returns nothing. We
> return
> that datareader and an exception occurs stating that the datareader is
> nothing.
> It is not that it happens only once and then works fine. It will keep
> doing
> this until we replace the dll on our server with a fresh copy of the same
> dll. It doesn't cause an exception in our DAL, b/c it doesn't log the
> issue.
> Can there be an internal exception that is fired in the SqlCommand object?
>
> Below is the code that returns the datareader
>
> Dim cmd As New SqlCommand
> Dim cn As New SqlConnection
> Dim reader As SqlDataReader = Nothing
>
> cn = New SqlConnection(mConnStr)
> Try
> cmd = New SqlCommand(commandText, cn)
> cn.Open()
> reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
> If reader Is Nothing Then
> cn.Dispose()
> End If
> Catch ex As Exception
> cn.Dispose()
> Util.WriteErrLog(ex.ToString, "SqlHelper", "ExecuteDataReader",
> commandText)
> End Try
> Return reader
> --
> Dan A
>

Perhaps the error logger is malfunctioning under some circumstances, and
this in turn is just throwing another error?

Like I said, it is really hard to imagine ExecuteReader returning Nothing
and yet not throwing any errors at all. I have never ever seen anyone else
with this issue, nor experienced it myself.

"Dan A" <quagmire666@gmail.com> wrote in message
news:E72943AA-C6AE-4F12-9993-135FBBC11D7D@microsoft.com...
> But if there was an issue with trying to execute the query or creating a
> connection, wouldn't an exception have been caught by the try catch block
> that is shown in the previously shown code? It is not doing that.
> --
> Dan A
>
>
>
> "Marina Levit [MVP]" wrote:
>
>> I have never heard of this happening. It is more likely that the
>> 'reader'
>> object is nothing because there was an error trying to execute the query
>> or
>> open the connection, and so that variable never got set correctly. Not
>> because the ExecuteReader call completed with no error, but somehow
>> failed
>> to return a valid object.
>>
>> "Dan A" <quagmire666@gmail.com> wrote in message
>> news:29867E59-1803-41CE-BD10-AD0C36221D4D@microsoft.com...
>> > In a web application we have, we are doing a simple query (requesting 3
>> > fields from a inner joing b/t 2 tables). This code is executed
>> > constantly,
>> > but once a week, the sqlcommand.ExecuteDataReader returns nothing. We
>> > return
>> > that datareader and an exception occurs stating that the datareader is
>> > nothing.
>> > It is not that it happens only once and then works fine. It will keep
>> > doing
>> > this until we replace the dll on our server with a fresh copy of the
>> > same
>> > dll. It doesn't cause an exception in our DAL, b/c it doesn't log the
>> > issue.
>> > Can there be an internal exception that is fired in the SqlCommand
>> > object?
>> >
>> > Below is the code that returns the datareader
>> >
>> > Dim cmd As New SqlCommand
>> > Dim cn As New SqlConnection
>> > Dim reader As SqlDataReader = Nothing
>> >
>> > cn = New SqlConnection(mConnStr)
>> > Try
>> > cmd = New SqlCommand(commandText, cn)
>> > cn.Open()
>> > reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>> > If reader Is Nothing Then
>> > cn.Dispose()
>> > End If
>> > Catch ex As Exception
>> > cn.Dispose()
>> > Util.WriteErrLog(ex.ToString, "SqlHelper",
>> > "ExecuteDataReader",
>> > commandText)
>> > End Try
>> > Return reader
>> > --
>> > Dan A
>> >
>>
>>
>>

There are several scenarios where the DataReader will return
nothing-especially with SQL Server. "Nothing" means there is no rowset in
the current resultset. This can occur when you execute a SELECT like:

SELECT atName FROM Lawyers WHERE Type=Honest and Price=Cheap

In other cases, if you execute a batch that returns an extra resultset that
has no rowset the DataReader would return nothing. In this case you have to
step to the next resultset in the stream.

Again, see chapters 2, 6 and 11 for more information.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Marina Levit [MVP]" <someone@nospam.com> wrote in message
news:uBsNyboKHHA.1252@TK2MSFTNGP02.phx.gbl...
>I have never heard of this happening. It is more likely that the 'reader'
>object is nothing because there was an error trying to execute the query or
>open the connection, and so that variable never got set correctly. Not
>because the ExecuteReader call completed with no error, but somehow failed
>to return a valid object.
>
> "Dan A" <quagmire666@gmail.com> wrote in message
> news:29867E59-1803-41CE-BD10-AD0C36221D4D@microsoft.com...
>> In a web application we have, we are doing a simple query (requesting 3
>> fields from a inner joing b/t 2 tables). This code is executed
>> constantly,
>> but once a week, the sqlcommand.ExecuteDataReader returns nothing. We
>> return
>> that datareader and an exception occurs stating that the datareader is
>> nothing.
>> It is not that it happens only once and then works fine. It will keep
>> doing
>> this until we replace the dll on our server with a fresh copy of the same
>> dll. It doesn't cause an exception in our DAL, b/c it doesn't log the
>> issue.
>> Can there be an internal exception that is fired in the SqlCommand
>> object?
>>
>> Below is the code that returns the datareader
>>
>> Dim cmd As New SqlCommand
>> Dim cn As New SqlConnection
>> Dim reader As SqlDataReader = Nothing
>>
>> cn = New SqlConnection(mConnStr)
>> Try
>> cmd = New SqlCommand(commandText, cn)
>> cn.Open()
>> reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
>> If reader Is Nothing Then
>> cn.Dispose()
>> End If
>> Catch ex As Exception
>> cn.Dispose()
>> Util.WriteErrLog(ex.ToString, "SqlHelper",
>> "ExecuteDataReader",
>> commandText)
>> End Try
>> Return reader
>> --
>> Dan A
>>
>
>