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
Solved

Error using DataReader in Project - Already Open DataReader

Posted on 2011-03-18
7
456 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:dbbishop
  • 3
  • 3
7 Comments
 
LVL 15

Author Comment

by:dbbishop
ID: 35168757
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 35168911
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35168917
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 15

Author Comment

by:dbbishop
ID: 35171982
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
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 35173982
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
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 35183557
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 35184574
Well I am glad I am in great company. Have a great day.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

860 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