Error using DataReader in Project - Already Open DataReader

I have the following function in a data access class in my project. As can be seen, it accepts returns a data reader object (if passed a value of TRUE). The problem I am having is that subsequent (after the first) calls generate an "Object reference not set to an instance of an object." error. The offending line is "Return _dbCommand.ExecuteReader(_ReaderCommandBehaviors)" and the it throws an ApplicationException with the message: "There is already an open DataReader associated with this Command which must be closed first."

How can I prevent this from happening?

    Public Function Execute(Optional ByVal ReturnsRows As Boolean = True) As SqlDataReader
        If ReturnsRows Then
            Try
                Return _dbCommand.ExecuteReader(_ReaderCommandBehaviors)
            Catch ex As SqlException
                _SqlErrorNumber = ex.Number
                _SqlErrorMessage = ex.Message
                _SqlErrorProcedure = ex.Procedure
                Return Nothing
            Catch ex As Exception
                Return Nothing
            Finally
                _ReaderCommandBehaviors = CommandBehavior.Default
            End Try
        Else
            Try
                _dbCommand.ExecuteNonQuery()
                Return Nothing
            Catch ex As SqlException
                _SqlErrorNumber = ex.Number
                _SqlErrorMessage = ex.Message
                _SqlErrorProcedure = ex.Procedure
                Return Nothing
            Catch ex As Exception
                Return Nothing
            End Try
        End If
    End Function

Open in new window

LVL 15
dbbishopAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi dbbishop;

In the code you posted this is the only section of code that is getting back a data reader. Please read the comments I place in the code snippet below.

Try
    With DataAccessor
        .SetProcedure("spRejectResearchLockAndRetrieveRecord2")
        .AddParam("@Company", ParameterDirection.Input, SqlDbType.Int, _CompanyToSelect)
        .AddParam("@UserID", ParameterDirection.Input, SqlDbType.VarChar, _UserID, 20)
        
        '=================================
        ' You recieve a data reader here
        rdrSQL = .Execute(True)
        '=================================
        ' You check some return code for - 1
        If .ReturnCode = -1 Then
            ' You should check here if you have a valid data reader
            ' if so you should close it first before exitomg the 
            ' Function GetNextReject
            Return -1
        End If
        '=================================
        
    End With
    If rdrSQL.HasRows Then
        rdrSQL.Read()
        _RejectID = rdrSQL("RejectID")
        _DishAcctNbr = rdrSQL("DishAcctNbr")
        _BTN = rdrSQL("BTN")
        _HeaderDate = rdrSQL("RejectDate")
        _DueDate = rdrSQL("DueDate")
    End If
    rdrSQL.Close()
    rdrSQL = Nothing
    Return _RejectID
Catch ex As Exception
    Throw New ApplicationException(ex.Message, ex)
End Try

Open in new window


Fernando
0
 
dbbishopAuthor Commented:
As an aside, I need to prevent the error in this method if at all possible. This is a generic method called from many sources. The class creates a new connection and command object when instantiated, and so any object that instantiates the DataAccessor class, expects a connection to already be establisghed and a command object present. Parameters are set and read through properties, and methods allow processing data, thus I cannot destroy the underlying objects. Making changes outside this method would require major rework of the entire project.

This has not been a problem until I made a slight modification to one form and changed the parameters that are used in an underlying stored procedure. All other functionality appears to still be intact.
0
 
Fernando SotoRetiredCommented:
Hi dbbishop;

It is difficult to say from just the code posted. The error is saying that there is already one data reader open with the instance of the _dbCommand and only one is allowed. So to see what is happening we need to see the code from where you create the data command object till the posted function.

Is this a multi-threaded application?

Fernando
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you basically have 4 options:
* don't use data readers (but datasets, for example)
* ensure that you have indeed max 1 datareader open at any time (aka when you have to process a data reader, and in the loop might require a second one, you need to read the reader completely into an array [=> dataset, see above ], and then loop on that array etc ... might require too much code to solve the issue
* use a connection string option: http://sridharbabuk.blogspot.com/2009/07/how-to-use-multiple-datareaders-in.html
* if possible, change the datareaders' base SQL to read both data "at once" instead of looping ...

choose your poison :)
0
 
dbbishopAuthor Commented:
I have attached code of the three classes involved, the reject form, the reject object and the data accessor.

The first run through, there are no problems. When I try to execute the same procedure a second time is wwhen I get the exception.  The first time through, the data reader is returned to the reject object, and properties are set in it from the reader. The data reader is then closed and set to nothing. The data reader only returns a single row.

I really need to solve this without having to make changes that would affect the entire project. I have many instances where the project returns a data reader, its contents are read into a grid or dropdown box, the reader obecj is closed, and then the exact same scenario is repeated to return additional data (e.g. another dropdown box). All database access is done through the data accessor class. The data accessor expects a connection string when it is created. The connection or command objects are not exposed outside the class, everything is done through properties and methods.

 test.txt
0
 
dbbishopAuthor Commented:
Fernando,

Actually, I had an epithany on Saturday afternoon while driving home from the gym and this is exactly what came to mind. Added it and it works. Great minds think alike!!
0
 
Fernando SotoRetiredCommented:
Well I am glad I am in great company. Have a great day.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.