Need to avoid the open DataReader associated with this command error

Posted on 2010-01-12
Medium Priority
Last Modified: 2012-06-21

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,
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)
            Dim data As New DataSet

            data = New DataSet

            If conDB.State = ConnectionState.Closed Then
            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
                My.Settings.DBNotAvailable = True
            End If


        Catch ex As Exception
            Dim CauseOfError As String = ex.TargetSite.ToString
        End Try

        Return TableExist
    End Function

Open in new window

Question by:indy500fan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26300962
Move "dr.Close()" into the Finally block.

Author Comment

ID: 26303451
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.      
LVL 83

Expert Comment

ID: 26303497
That does not matter. dr is being assigned a value inside the try block.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

LVL 75

Accepted Solution

käµfm³d   👽 earned 2000 total points
ID: 26303591
To negate that warning, just change line 4 to

    Dim dr As SqlClient.SqlDataReader = Nothing

and dr.Close() to

    If dr IsNot Nothing Then
    End If
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26303600
Oh, and what CodeCruiser said is accurate. I was just mentioning how to remove the warning (if you want to).

Author Comment

ID: 26303613

Cool.  Thanks for the tip.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question