Invalid attempt to Read when reader is closed

[Chris]

Hi,

i wrote this code for fetching data from a table using a stored procedure.
The connection definition and opening are put in a class.
The actual read of data occurs in code-behind.
My problem with this code:

Invalid attempt to Read when reader is closed at line: While dtreader.Read()

Thanks for help
Chris

1) the class:
-----------
Public Class myclass
Public Shared Function mydtreader() As SqlDataReader
Dim dreader As SqlDataReader

Try
Using mConnection As New SqlConnection(param.ConnectionString)

Dim mCom As SqlCommand = New SqlCommand("mysp", mConnection)
mCom.CommandType = CommandType.StoredProcedure
mConnection.Open()
dreader = mCom.ExecuteReader
Return dreader
End Using
Catch ex As Exception
Throw
End Try
End Function
end class

2) code-behind:
---------------
Protected Sub pseudoSubmit_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles pseudoSubmit.Click
Dim dtreader As SqlDataReader = chat.mydtreader
While dtreader.Read()
......
End While
dtreader.Close()
End Sub

 

[Guest]

Chris,

Your Using statement closes the connection. But a datareader needs the
connection to be open so it can retrieve rows and deliver them to you in your
While dtreader.Read() loop.

You will need to keep the connection open until you are finished with the
datareader. You might want to use:

dreader = mCom.ExecuteReader (CommandBehavior.CloseConnection)

so that when you eventually close the datareader the connection will get
closed.

Kerry Moorman

 

[Chris]

Thanks

Chris,

Your Using statement closes the connection. But a datareader needs the
connection to be open so it can retrieve rows and deliver them to you in
your
While dtreader.Read() loop.

You will need to keep the connection open until you are finished with the
datareader. You might want to use:

dreader = mCom.ExecuteReader (CommandBehavior.CloseConnection)

so that when you eventually close the datareader the connection will get
closed.

Kerry Moorman