Closing database connection.

[Brent]

I wrote a simple class to supply a datareader object (code at bottom of
post) back to a page. I access it on the APSX page like this:

Dim myDR as New getDataReader(strSQL)

If myDR.ResultFailed = True Then

MessageText.InnerHTML = "Internal error."

Else
dataList.DataSource = myDR.Result
dataList.DataBind()

End If

From time to time it get weird errors, which I tend to think stem from not
closing the database connection in the class code. Yet, if I explicitly
close the connection, I get an error that says "Invalid attempt to
FieldCount when reader is closed. " I've commented-out the
connection-closing code below, and this code actually works. Is there any
way I can make sure the connection gets closed in hopes of preventing the
other random errors?

Thanks for any help!

--Brent



*===================================
Public Class getDataReader

Public Result AS OLEDBDatareader
Public ResultFailed as Boolean = True

Public Sub New(strSQL AS String)

Try
Dim myConn as new OleDBConnection (connString)

myConn.Open()
Dim myCommand AS new OleDBCommand(strSQL, myConn)

Result = myCommand.ExecuteReader()
ResultFailed = False
'myConn.Close()
myConn = Nothing

Catch
ResultFailed = True
End Try

End Sub

End Class
===================================

 

[Peter O'Reilly]

You need to rework your logic. Only one DataReader (DR) at a time may be
used for the connection object. While accessing the DR, the connection
state must remain open in connection object. When you are finished using
the DR(s), and you call their respective Close method, you then call the
Close method of the connection object.
HTH.

 

[Brent]

Thanks, Peter...I guess that makes my class kinda useless...I've put the
code back onto the page itself instead.

--Brent