Solved

Error using DataReader in Project - Already Open DataReader

Posted on 2011-03-18
7
463 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
[X]
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
  • 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

695 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