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(p aram.Connection String)

Dim mCom As SqlCommand = New SqlCommand("mys p", mConnection)
mCom.CommandTyp e = CommandType.Sto redProcedure
mConnection.Ope n()
dreader = mCom.ExecuteRea der
Return dreader
End Using
Catch ex As Exception
Throw
End Try
End Function
end class

2) code-behind:
---------------
Protected Sub pseudoSubmit_Cl ick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles pseudoSubmit.Cl ick
Dim dtreader As SqlDataReader = chat.mydtreader
While dtreader.Read()
......
End While
dtreader.Close( )
End Sub

[=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=]

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.ExecuteRea der (CommandBehavio r.CloseConnecti on)

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

Kerry Moorman
"Chris" wrote:

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(p aram.Connection String)

Dim mCom As SqlCommand = New SqlCommand("mys p", mConnection)
mCom.CommandTyp e = CommandType.Sto redProcedure
mConnection.Ope n()
dreader = mCom.ExecuteRea der
Return dreader
End Using
Catch ex As Exception
Throw
End Try
End Function
end class

2) code-behind:
---------------
Protected Sub pseudoSubmit_Cl ick(ByVal sender As Object, ByVal e As
System.EventArg s) Handles pseudoSubmit.Cl ick
Dim dtreader As SqlDataReader = chat.mydtreader
While dtreader.Read()
......
End While
dtreader.Close( )
End Sub

[Chris]

Thanks

"Kerry Moorman" <Ke**********@d iscussions.micr osoft.comschree f in bericht
news:6C******** *************** ***********@mic rosoft.com...

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.ExecuteRea der (CommandBehavio r.CloseConnecti on)

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

Kerry Moorman
"Chris" wrote:

>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.Connectio nString)

Dim mCom As SqlCommand = New SqlCommand("mys p",
mConnection)
mCom.CommandTyp e = CommandType.Sto redProcedure
mConnection.Ope n()
dreader = mCom.ExecuteRea der
Return dreader
End Using
Catch ex As Exception
Throw
End Try
End Function
end class

2) code-behind:
---------------
Protected Sub pseudoSubmit_Cl ick(ByVal sender As Object, ByVal e As
System.EventAr gs) Handles pseudoSubmit.Cl ick
Dim dtreader As SqlDataReader = chat.mydtreader
While dtreader.Read()
......
End While
dtreader.Close( )
End Sub