Data reader already open

Question

Have already looked questions similar to mine but none of them works for me this is my code

dbconn = New SqlConnection
    dbconn.ConnectionString = ("Data Source=JENELIE\SQLEXPRESS;Initial Catalog=feeding_monitoring_system;User ID=sa;Password=Jenelie19; MultipleActiveResultSets = true")
    Dim reader As SqlDataReader
    Dim sda As New SqlDataAdapter
    Dim ds As New DataSet()
    Try
        dbconn.Open()
        Dim sql As String
        sql = "select Count (Gender) as NumberofStudent, Gender from Student_Info Group by Gender"
        dbcomm = New SqlCommand(sql, dbconn)
        reader = dbcomm.ExecuteReader
        sda.SelectCommand = dbcomm
        sda.Fill(ds, "Student_Info")
    Catch ex As SqlException
        MessageBox.Show(ex.Message)
    Finally
        dbconn.Dispose()
    End Try

Then at sda.Fill(ds, "Student_Info") an error happens

Possible duplicate of [There is already an open DataReader associated with this Command which must be closed first](https://stackoverflow.com/questions/6062192/there-is-already-an-open-datareader-associated-with-this-command-which-must-be-c) — MatSnow, Sep 22 '17 at 14:08
The exception means what it says - you just opened a Reader 2 lines before. Dont create what you dont need and ***all*** your DBProvider objects should be used inside `Using` blocks — Ňɏssa Pøngjǣrdenlarp, Sep 22 '17 at 14:09
*Don't* declare variables outside the scope they are used and *don't* use `New ` with them. That creates a new object that's discarded as soon as a new one is assigned to the variable. For example, writing `Dim dbcomm = New SqlCommand(sql, dbconn)` is enough to create a new command *inside* the try block. So is `Dim reader = dbcomm.ExecuteReader`. — Panagiotis Kanavos, Sep 22 '17 at 14:20
Furthermore, you need to make certain that connection, reader objects are properly disposed by using a `Using` block — Panagiotis Kanavos, Sep 22 '17 at 14:21

score 4 · Accepted Answer · answered Sep 22 '17 at 14:20

You dont use that reader at all, so i don't understand your code. You want to fill the DataSet with the DataAdapter, then this is needed (always use Using):

Dim ds As New DataSet()

Using dbconn As New SqlConnection("Data Source=JENELIE\SQLEXPRESS;Initial Catalog=feeding_monitoring_system;User ID=sa;Password=Jenelie19;MultipleActiveResultSets = true")
    Dim sda = New SqlDataAdapter("select Count (Gender) as NumberofStudent, Gender from Student_Info Group by Gender", dbconn)
    Try
        sda.Fill(ds, "Student_Info") ' you dont need to open/close the connection
    Catch ex As SqlException
       MessageBox.Show(ex.Message)
    End Try
End Using

BlueMonkMN · Answer 2 · 2017-09-22T14:23:44.933

I would try making sure that all disposable objects are properly disposed within this function. I recommend the Using statement to help ensure that any disposable object gets properly disposed as it goes out of scope. I believe that SqlConnection, SqlDataReader, SqlDataAdapter and DataSet are all disposable.

Edit: Although I think Tim's answer is more targeted at your problem (the SqlDataReader is unused and unnecessary), you should make sure to clean-up all your disposable objects, too. If you do use an SqlDataReader, you'll want to dispose of it before doing anything else, unless you're just trying to prove that you can have multiple result sets open at once, in which case, the lack of cleanup in multiple accesses to the same connection might be responsible (if one of them doesn't include MultipleActiveResultSets).

using statement disposes and the act of disposing closes the connection — Aaron. S, Oct 05 '17 at 16:49
@Aaron.S There is no `using` statement in the original code. — BlueMonkMN, Oct 05 '17 at 21:11

score 1 · Answer 3 · answered Sep 22 '17 at 14:27

1

First off, in that situation, you do not need to use reader, you just need SQLDataAdapter.

Second, you should use Conn.Close() to close your SQL connection, rather than Conn.Dispose(). The error means that some where in your code, you opened the connection before hand, but never closed it.

answered Sep 22 '17 at 14:27

AustinS90

153
6

1

`Conn.Close()` is [not absolutely neccesary](https://stackoverflow.com/questions/1195829/do-i-have-to-close-a-sqlconnection-before-it-gets-disposed) – MatSnow Sep 22 '17 at 14:32
2

In most cases `Close()` and `Dispose()` do the exact same thing. – Visual Vincent Sep 22 '17 at 14:52
@MatSnow please clarify your comment. Close is not necessary if a using statement is used, correct? if no using statement is used then Close is necessary – Aaron. S Oct 05 '17 at 16:48
@AustinS90 your example is correct because he did NOT wrap his code into a using. a using will dispose and that closes too – Aaron. S Oct 05 '17 at 16:54
@Aaron.S Yes, just wanted to say, Conn.Dispose() will close the connection too. – MatSnow Oct 05 '17 at 20:00
1

@MatSnow you are correct but Dispose also disposes the object too which might not be desired. if you want to reuse the object then you would need to new it again if Dispose were called instead of just Close – Aaron. S Oct 06 '17 at 20:17

Data reader already open

3 Answers3