Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error using DataReader in Project - Already Open DataReader

Posted on 2011-03-18
7
Medium Priority
?
477 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 64

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 64

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 64

Expert Comment

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

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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