• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Need to avoid the open DataReader associated with this command error

Friends,

I really like to re-use existing DB connections when ever possible; however, I've learned a side effect of doing so can lead to a problem where if there is a problem, the datareader can be left open, preventing the connection from being used on the next pass through.

I have the attached function as an example.

I want to avoid the error, "There is already an open DataReader associated with this Command which must be closed first."

I realize that I could add "MultipleActiveResultSets=True" to my connection string, but shouldn't I do something better with my code?

Looking for VB.Net code help.

Thanks in advance!

Best Regards,
Eric
Private Function TestTableExistance(ByVal conDB As SqlClient.SqlConnection)

        Dim TableExist As Boolean = False
        Dim dr As SqlClient.SqlDataReader

        Dim c As New SqlClient.SqlCommand("IF EXISTS (SELECT 1" & vbCrLf _
                                          & "FROM INFORMATION_SCHEMA.TABLES" & vbCrLf _
                                          & "WHERE TABLE_TYPE='BASE TABLE'" & vbCrLf _
                                          & "AND TABLE_NAME='Results')" & vbCrLf _
                                          & "SELECT TableExist='TRUE' " & vbCrLf _
                                          & "ELSE SELECT TableExist='FALSE'", conDB)
        Try
            Dim data As New DataSet

            data = New DataSet

            If conDB.State = ConnectionState.Closed Then
                conDB.Open()
            End If

            dr = c.ExecuteReader(CommandBehavior.SingleResult)
            If dr.HasRows Then
                'loop through the records
                While dr.Read()
                    TableExist = dr("TableExist")
                End While
            End If

            If TableExist = True Then
                My.Settings.DBNotAvailable = False
                My.Settings.GoodToSendXMPPMessage = True
            Else
                My.Settings.DBNotAvailable = True
            End If

            dr.Close()

        Catch ex As Exception
            Dim CauseOfError As String = ex.TargetSite.ToString
            EmergencyStop(CauseOfError)
        Finally
            c.Dispose()
        End Try

        Return TableExist
    End Function

Open in new window

0
indy500fan
Asked:
indy500fan
  • 3
  • 2
1 Solution
 
käµfm³d 👽Commented:
Move "dr.Close()" into the Finally block.
0
 
indy500fanAuthor Commented:
If I do that, I get this warning, Variable 'dr' is used before it has been assigned a value. A null reference exception could result at runtime.      
0
 
CodeCruiserCommented:
That does not matter. dr is being assigned a value inside the try block.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
käµfm³d 👽Commented:
To negate that warning, just change line 4 to

    Dim dr As SqlClient.SqlDataReader = Nothing

and dr.Close() to

    If dr IsNot Nothing Then
        dr.Close()
    End If
0
 
käµfm³d 👽Commented:
Oh, and what CodeCruiser said is accurate. I was just mentioning how to remove the warning (if you want to).
0
 
indy500fanAuthor Commented:
kaufmed,

Cool.  Thanks for the tip.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now