?
Solved

Error using DataReader in Project - Already Open DataReader

Posted on 2011-03-18
7
Medium Priority
?
471 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
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 
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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…
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…
Suggested Courses

764 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