0

I have this code in vb.net (saved in pastebin): http://pastebin.com/4hvMyMPb

on line 131 (reader = myCommand.ExecuteReader) i am getting an error saying 

There is already an open DataReader associated with this Connection which must be closed first.

but there is nothing else above this line of code that i can that would cause a problem.

i have even tried changing the conn and reader to conn6 and reader6 but i get exactly the same error message

charlie
  • 415
  • 4
  • 35
  • 83
  • 2
    Get familiar with the `Using`-statement and use it with all objects that implement `IDisposable` like the connection, the command and the datareader. Don't be afraid that it causes performance issues if you create a new connection for every task, actually you are using the same physical connection that is returned from the connection-pool. – Tim Schmelter Oct 06 '15 at 13:30
  • See: http://stackoverflow.com/q/28213871/3697824 or any of those under "Related" learning from past identical questions is not hard, try it – user3697824 Oct 06 '15 at 13:33

2 Answers2

1

Put reader.Close() before closing the connection.

rjps12
  • 587
  • 6
  • 15
0

A connection by default can only have one open DataReader at a time. When you're done with any DataReader you must call Close on it. If you share the connection throughout your program and you don't do this you'll run into this issue.

You can implement MultipleActiveResultSets through the connection string BUT this should only be done when you intended to do it, not to fix hanging readers that aren't in use anymore.

https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx

A better way to be to use a pattern like this that will cleanup everything for you:

        ' Using Statements will handle closing and disposing
    Using conn As New SqlConnection("server=YourServer;database=YourDatabase;trusted_connection=yes;")
        conn.Open()

        Using cmd As SqlCommand = conn.CreateCommand
            cmd.CommandText = "select * from yourtable"

            ' The reader doesn't implement IDisposeable BUT this will close the reader 
            ' even in the case of exception
            Using dr As SqlDataReader = cmd.ExecuteReader

                While dr.Read
                    ' Do what you need to do with your DataReader
                    Dim buf As String = dr("myField")

                End While

            End Using

        End Using

        ' The using should call close I always close via Dispose out of habit
        conn.Close()
    End Using
b.pell
  • 3,873
  • 2
  • 28
  • 39