[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Invalid attempt to call Read when reader is closed.

Posted on 2011-10-07
8
Medium Priority
?
564 Views
Last Modified: 2012-05-12
I have a function that calls another function and expects a SQLDataReader to be returned.  When I put the SQL calls inside a Using Statement (a requirement) then the Reader closes before I can return it and it throws an error.

Is there anyway around this?  I have attached both functions problemprocess.txt
0
Comment
Question by:sherbug1015
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 40
ID: 36931096
End using closes the connection, so it is closed when you return the DataReader. Although it is a good practice, Using...End Using is not mandatory.

Do not declare the Connection with Using, use a Dim.

The Connection will be disposed of properly anyway when you Close de DataReader.
0
 
LVL 12

Expert Comment

by:rgn2121
ID: 36931103
Change to the following:
Private Function FindQuestionID(ByVal lshortdesc As String, ByVal lresponse As String) As SqlDataReader
            Dim cn As New SqlConnection(strConnection)
            sSQL = "select a.ProfileQuestionID,b.ProfileResponseid from tblProfileQuestion a join tblProfileResponse b on a.ProfileQuestionID = b.fkProfileQuestionID where a.shortdesc = '" & lshortdesc & "' and b.responsevalue = '" & lresponse & "'"
    
            Using cn
                Dim cmd As New SqlCommand(sSQL, cn)
                Try
                    If cn.State = ConnectionState.Closed Then cn.Open()
                    Return cmd.ExecuteReader()
                Catch ex As Exception
                    Throw New Exception("Error: " & ex.Message)
                    Return Nothing
                End Try
            End Using
    
                
    
    End Function

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36931288
I really dont like passing the readers around in functions as readers keep the connection open. Is there any problem in using a DataTable?
0
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!

 
LVL 40
ID: 36931365
@CodeCruiser.

Readers keep the connection open, but they are a lot faster and better on resources, so they are a better choice for a lot of situations. The current example is a case in point, since sherbug needs only 2 values in the same row. Creating a DataTable for 2 values would be overkill.

Used properly, such as here, where the DataReader is closed right after reading the line, this is the way to go. The Connection object is kept alive for a very short time, probably less than a second. And with the Connection pooling that goes around under the hood, the physical connection will still be opened for 5 minutes anyway.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36931443
Yes readers are useful in situations but I wont use a reader across functions unless necessary.

>since sherbug needs only 2 values in the same row. Creating a DataTable for 2 values would be overkill.
I would rather do a executescalar and return the two values as string or list of string.
0
 

Author Comment

by:sherbug1015
ID: 36933043
rgn2121 - Your solution still throws the same error.  

JamesBurger - I am required to place every sql call inside a Using statement, so I am forced to try and make this work.  I don't see where I am declaring the connection with Using.  I am using Dim.  Would you suggest using a DataSet instead?  
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 2000 total points
ID: 36933218
Using has been meant to be used for a local variable that has no use outside of the method. End Using dispose of the object before you get out of FindQuestionID, so it is not available when you get back to the caller, and the returned DataReader does not have a connection.

If you are required to go with Using, then you will have to declare your connection inside of the calling method and pass it as a parameter. Something such as the following (note that in most cases, you implement Using on the declaration of the variable).

Public Sub CallingMethod()

    Using cn As New SqlConnection(strConnection)
        yourDataReader = FindQuestionID( yourShortDesc, yourResponse, cn) '<-------------------
        'Work with your Reader here, the connection is still available and working
    End Using

End Sub

Private Function FindQuestionID(ByVal lshortdesc As String, ByVal lresponse As String, cn As SqlConnection) As SqlDataReader '<-------------------
  
            sSQL = "select a.ProfileQuestionID,b.ProfileResponseid from tblProfileQuestion a join tblProfileResponse b on a.ProfileQuestionID = b.fkProfileQuestionID where a.shortdesc = '" & lshortdesc & "' and b.responsevalue = '" & lresponse & "'"
    
                Dim cmd As New SqlCommand(sSQL, cn)
                Try
                    If cn.State = ConnectionState.Closed Then cn.Open()
                    Return cmd.ExecuteReader()
                Catch ex As Exception
                    Throw New Exception("Error: " & ex.Message)
                    Return Nothing
                End Try

    End Function

Open in new window

0
 

Author Closing Comment

by:sherbug1015
ID: 36933308
Thank you.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

831 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