.net - "There is already an open DataReader..." Reuse or Dispose DB Connections?

Question

Please Help.... When I select data from Mysql table its showing "There is already an open DataReader associated with this Connection which must be closed first. vb.net"

Private Sub cmbJobCategoryVisa_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbJobCategoryVisa.SelectedIndexChanged
    ''"
    Dim MyCommand As New MySqlCommand("SELECT jobcategorycode FROM jobcategory WHERE jobcategory='" & Me.cmbJobCategoryVisa.SelectedItem & "'", MyConnection)
    Dim MyReader As MySqlDataReader = MyCommand.ExecuteReader
    While MyReader.Read
        If MyReader.HasRows = True Then
            Me.txtJobCategoryCodeVisa.Text = MyReader("jobcategorycode")
        End If
    End While
    MyReader.Close()
    MyCommand.Dispose()
End Sub

'''at the time of the below code execution,,, the imaged error is showing

    Private Sub txtEmpNo_Validating(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles txtEmpNo.Validating
    Dim MyCommand5 As New MySqlCommand("SELECT * FROM employeesmaster WHERE empno='" & Me.txtEmpNo.Text & "'", MyConnection)
    Dim MyDataReader5 As MySqlDataReader = MyCommand5.ExecuteReader
    If MyDataReader5.HasRows = True Then
        While MyDataReader5.Read
            Me.txtEmpName.Text = MyDataReader5("name")
            Me.cmbNationality.Text = MyDataReader5("nationality")
            Me.cmbJobCategoryVisa.Text = MyDataReader5("jobcategoryvisa")
            If Not IsDBNull(MyDataReader5("image")) Then
                Dim ImageData As Byte() = DirectCast(MyDataReader5("image"), Byte())
                Dim MemoryStream As New IO.MemoryStream(ImageData)
                Me.pbxEmpImage.Image = Image.FromStream(MemoryStream)
            Else
                Me.pbxEmpImage.Image = Nothing
            End If
        End While
    Else
    End If
    MyDataReader5.Close()
    MyCommand5.Dispose()
End Sub

Answer 1

It is apparent that you are using a single, global connection, and apparently leaving it open. As has been mentioned, you should not reuse or store your connection. Connections are cheap to create and .NET is optimized for creating them as needed.

There are a number of things in your code which are not being closed and disposed. which should be. Disposing not only prevents your app from leaking resources, but this kind of error cant happen using newly created DB objects for each task.

Connections
Since there are gyrations involved in creating them, you can write a function to create (and maybe open) a new Connection and avoid having to paste the connection string everywhere. Here is a general example using OleDB:

Public Function GetConnection(Optional usr As String = "admin",
                       Optional pw As String = "") As OleDbConnection
    Dim conStr As String
    conStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id={1};Password={2};",
                      dbFile, usr, pw)

    Return New OleDbConnection(constr)
End Function

Using blocks
Use it in a Using block so it is disposed of:

Using con As OleDb.OleDbConnection = GetConnection()
    Using cmd As New OleDbCommand(sql.Value, con)

        con.Open()
        Using rdr As OleDbDataReader = cmd.ExecuteReader()
           ' do stuff

        End Using      ' close and dispose of reader
    End Using          ' close and dispose of command
End Using              ' close, dispose of the Connection objects

Each Using statement creates a new target object, and disposes it at the end of the block.

In general, anything which has a Dispose method can and should be used in a Using block to assure it is disposed of. This would include the MemoryStream and Image used in your code.

Using blocks can be "stacked" to specify more than one object and reduce indentation (note the comma after the end of the first line):

Using con As OleDb.OleDbConnection = GetConnection(),
    cmd As New OleDbCommand(sql.Value, con)
    con.Open()
    ...
End Using       ' close and dispose of Connection and Command

For more information see:

• Using Statement
• Connection Pooling
• How to: Dispose of a System Resource

---

can u pls convert this code to Mysql connection... my connection string is...

For basic MySQL connection:

' module level declaration 
Private MySQLDBase as String = "officeone"

Function GetConnection(Optional usr As String = "root",
                       Optional pw As String = "123456") As MySqlConnection
    Dim conStr As String
    conStr = String.Format("Server=localhost;Port=3306;Database={0};Uid={1}; Pwd={2};", 
         MySQLDBase, usr, pw)

    Return New MySqlConnection(constr)
End Function

Personally for MySql, I use a class and a ConnectionStringBuilder in the method. There are many, many cool options I use but which differs from project to project like the DB and default app login. The above uses all the defaults.