VB.NET There is already an open DataReader

OleDB Error

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

OleDB Error : There is already an open DataReader associated with this Command

🎵Problem

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

⁉️Cause

Having more than 1 dataReader result set working at the same time .

🔬Solution

In my best practice solution, I worked with SQL Server2000 and VB.NET 2005. The VB.NET Example Project can be downloaded at the end of this post and it contains (SQL Server 2000 Database and VB.NET Project)

👨‍🏫The example shows how to

Navigate (First Record - Next Record) using ADO.NET2.0 Online Mode using SQLClient DataReader.
SQL Database Name : Market
SQL Database Table : Market.Info
SQL Server Name : evry1falls
In order to use the example correctly 'best practice', you'll have to Import the SQL Database 'Market' into your SQL Server 2000 Enterprise Manager)
Photo1 : Project Final Design

VB .NET SQL Server project

Photo2 : Table Structure

VB .NET SQL Server Table

VB .NET SQL Server project Source Code

Photo4 : Codes

VB .NET SQL Server project source code

Photo5 : Codes

VB .NET SQL Server project source code

Now, if you run the project and tried to navigate using (First) or (Next) you will get the Exception :

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

So, the solution would be :

Trap 'Try..Catch' the Exception that shows in the (SelectedIndexChanged) Event of the (ComboBox) like this :

And the same for the (Next) Button ..

Now you can Navigate and search using ComboBox Control safely without exceptions ..

Note

Source code will give you exception and you will have to re-write the solution by yourself as shown above .
The Folder (Bin/Debug) Includes (Back-Up) use it to restore the (Market Database) in your Sql Server 2000
Use the namespace (evry1falls) as new server Registration .

Download Source Code :

Download The Source Code (Mediafire)

Using MS Access 2003/2007/2010

In case of using Microsoft Access Database, OleDb.OleDbDataReader with OleDb.OleDbCommand. The work around or the solution would be:
Always create a new Instance of the OleDbCommand, I.e

Dim Cpur1 As New OleDb.OleDbCommand
        sqlStr = Trim("")
        sqlStr = "Select * from Purchase"
        With Cpur1
            .Connection = CN
            .CommandType = CommandType.Text
            .CommandText = sqlStr
        End With
        RD = Cpur1.ExecuteReader()

♥ Here are some online Visual Basic lessons and courses:

Sitemap for this BLOG

Evry1falls

Mastering VB.NET - ADO.NET: Expert Tutorials, Code Solutions & Database Insights

Looking for MS Access Developer❓❓

Application developer