OLEDB: Opening the connection does not work in the second time

Question

Hello 2 everyone ^_^...

I have a VB code that connects to a sever database and used Virtual Machine as the client (sorry i only have one computer :P).

on the 1st click the select query works, but on the second time it does not work anymore... Here is my code:

From the module

Imports System.Data.OleDb

Module oledb

    Public conn As OleDbConnection = New OleDbConnection()
    Public comm As OleDbCommand
    Public reader As OleDbDataReader
    Public query As String

    Sub connection()
        Try
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\vboxsvr\Documents\sampleDB.mdb;User Id=Admin;Password="
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Module

From the form

Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call connection()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            conn.Open()
            query = "select * from sample_tbl"
            comm = New OleDbCommand(query, conn)
            reader = comm.ExecuteReader

            While reader.Read
                ListBox1.Items.Add("Number: " + reader("number").ToString + "Letter: " + reader("letter").ToString)
            End While
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            conn.Dispose()
        End Try
    End Sub
End Class

The error is

the connectionstring property has not been initialized

The line of error is on the form code

conn.open

Thank you for any help :-)

Answer 1

You are disposing the conn object every time you click button. you should do something like the following. Comment for changes in code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        conn.Open()
        query = "select * from sample_tbl"
        comm = New OleDbCommand(query, conn)
        reader = comm.ExecuteReader

        While reader.Read
            ListBox1.Items.Add("Number: " + reader("number").ToString + "Letter: " + reader("letter").ToString)
        End While
    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        'Close the connection
        'Better to close the connection in Finally block
        conn.Close()
        'but don't dispose the conn object
        'conn.Dispose()
    End Try
End Sub

Answer 2

From MSDN:

We recommend that you always close or dispose of a connection when you are finished using it in order to return the connection to the pool.

This means that db connections should be created, opened and closed for each use. This is somewhat less critical for Access, but doing so would have prevented the problem encountered:

Module oledb

    Public conn As OleDbConnection = New OleDbConnection()
    Public comm As OleDbCommand
    Public reader As OleDbDataReader

Creating one global OleDbCommand object is also asking for trouble. Unlike the connection object where all the settings and properties are usually the same, a DBCommand object is specific to a query. Reusing them can result in things from the last use carrying over. The code posted is not likely to encounter this because it does create a new one each time:

comm = New OleDbCommand(query, conn)

However, the old one was not closed or disposed of. In this question the code was unable to create new DbCommand objects after a while because System resources were depleted.

In the end, the only thing saved by using a global command object is simply: Dim. Still other problems can result from reusing DataReader objects.

Connections

It can be tedious to create a new connection each time you need one, if for no other reason than having to copy the connection string strewn all over your code. The answer is not a global connection, but a helper method:

Public Function GetConnection(Optional usr As String = "admin",
                       Optional pw As String = "") As OleDb.OleDbConnection

    Return New OleDb.OleDbConnection( _
        String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id={1};Password={2};",
                      dbFile, usr, pw))
End Function

Note: you could also use the ConnectionStringBuilder here, I think the OleDb version is decidedly less easy to use (compared to the MySQL version for instance).

Using blocks

Using blocks should be used for most of these DB provider objects. For example:

Using dbCon As OleDbConnection = GetConnection()
    ...
End Using

Using creates a new block scope - the target variable is declared and created at the start of the block and exists only in that block. The object is closed and disposed at the end so that everything is cleaned up.

Since an OleDbCommand object should also be disposed of after use, these can also employ a Using block. Trying to reuse a global OleDbDataReader can result in an InvalidOperationException:

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

Since you are not closing and disposing of the DataReader, it may be only a matter of time. These can also employ Using blocks. Your code revised:

Dim SQL = "select * from sample_tbl"
Using dbCon As OleDbConnection = GetConnection()
    Using cmd As New OleDbCommand(SQL, dbCon)
        dbCon.Open()
        Using rdr As OleDbDataReader = cmd.ExecuteReader
            If rdr.HasRows Then
                While rdr.Read
                    ListBox1.Items.Add("Number: " & rdr("number").ToString 
                                       & "Letter: " + rdr("letter").ToString)
                End While
            End If
        End Using
    End Using
End Using

Everything that needs to be closed and disposed of, will be. Everything that should be created anew, is. Note that you can reduce the indentation/number of Using blocks, by stacking or including more than one object:

Using dbCon As OleDbConnection = GetConnection(),
    cmd As New OleDbCommand(SQL, dbCon)
    ' ...
End Using     ' close and dispose of both the dbCon and cmd objects