There is already an open datareader associated with this command which must be closed first VB.NET SQL SERVER

Question

1.00/5 (1 vote)

See more:

Here is my code...

VB.NET

      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TreeView1.Nodes.Clear()
        FillTree(101, "ASSETS", Nothing)
    End Sub

    Public Sub FillTree(ByVal Key As Integer, ByVal Txt As String, ByVal N As TreeNode)

        Dim NN As TreeNode
        If N Is Nothing Then
            NN = TreeView1.Nodes.Add(Key, Txt)
        Else
            NN = N.Nodes.Add(Key, Txt)
        End If

        cmd = New SqlCommand("Select SNo, ChartName FROM COA where SNo='" & Key & "'", con)

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Do While dr.Read()
            FillTree(dr("SNo"), dr("ChartName"), NN)
        Loop

        dr.Close()
        cmd.Dispose()

        Try
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

End Class

What I have tried:

Tries using Data reader close at different places but still getting this error. I am still new to this. Please guide.

Posted 27-Sep-21 0:55am

Shafiq ur Rehman 2021

Updated 27-Sep-21 23:53pm

Dave Kreskowiak

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-09-27T01:16:00

The problem is that you are using the same connection object for every query - which is considered a bad practice anyway - and somewhere in your code you have opened a DataReader but not closed it. While you have an open DataReader, you can't do anything else with the connection.

Throw away your single connection object, and create them when you need them, inside a Using block so that the system closes and disposes of it when it goes out of scope. Put Command, DataAdapter, and DataReader objects into Using blocks as well as that is good practice.

Your problem will go away and not come back - unlike your current code, where it's very likely to occur again, and again, and again - because you have one connection shared across many queries and just one of those will bugger up the rest ...

Dave Kreskowiak · Answer 2 · 2021-09-27T01:18:00

You can only have one DataReader running at a time on a connection to the database. Since your FillTree function calls itself, you're trying to execute more than one DataReader.

You have to scrap this code and rewrite it with that concept in mind.

You might want to try filling a DataTable instead of using ExecuteReader. You have to close the connection to the database BEFORE you call FillTable again.

Richard Deeming · Answer 3 · 2021-09-27T03:37:00

In addition to the error, which the other solutions have already explained, your code has another problem.

The Key parameter passed to the function is used to select the records from you table where the SNo column is equal to the Key parameter.

The function is then called recursively, passing the SNo column from the resultset as the Key parameter. But since you already know that the SNo column is equal to the Key parameter, you've just introduced infinite recursion.

Unless there are no matching records in your database, you will end up with a StackOverflow exception.

I suspect you either meant to SELECT a different column, or filter on a different column. Selecting a column which is filtered to be equal to a value you already know serves no purpose.

Also, don't use string concatenation to build your query; use a parameterized query. String concatenation can and will lead to SQL Injection[^] vulnerabilities.

And there's no point having an empty Try..Catch block at the end of the method.

Jörgen Andersson · Answer 4 · 2021-09-27T23:53:00

Like OriginalGriff already pointed out, Using is your friend here.
Always close your objects, Using does it for you.

VB

Using Connection As SqlConnection = GetConnection()
    If Connection.State <> ConnectionState.Open Then Connection.Open()
    Using Command = New SqlCommand("Select SNo, ChartName FROM COA where SNo='" & Key & "'", Connection)
        Using Reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Do While dr.Read()
                FillTree(dr("SNo"), dr("ChartName"), NN)
            Loop              
        End Using
    End Using
End Using

There is already an open datareader associated with this command which must be closed first VB.NET SQL SERVER

4 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Add your solution here

Preview 0