“There is already an open DataReader…” Reuse or Dispose DB Connections?

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.