How to display database connection status in VB 2010

Question

I'm developing Point of sales system. and i want to display database connection status for the users. Im using MS Access 2013 database and Visual Studio 2010 (VB). I created module for this project as follows,

Imports System.Data.OleDb
Module ModConVar
    Public sql As String
    Public cmd As OleDbCommand
    Public dr As OleDbDataReader

    Public conn As OleDbConnection
    Public connStr As String = System.Environment.CurrentDirectory.ToString & "\NCS_POS_DB.accdb"

    Public Sub ConnDB()
        Try
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & connStr & "")
            conn.Open()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Module

And I have a label named lblDBStatus in main MDI form, I tried with followin code, but it dosent work.

If conn.State = ConnectionState.Open Then
    lblDBStatus.Text = "CONECTED"
End If

any suggestions please ??

Answer 1

You're displaying "CONNECTED" only when the connection state is open. Otherwise your label will not show anything

Try this and make sure that the connection is open:

If conn.State = ConnectionState.Open Then
    lblDBStatus.Text = "CONNECTED"
Else 
    lblDBStatus.Text = "DISCONNECTED"
End If

Answer 2

The OleDbConnection exposes a StateChanged event.

So you can track the state like this:

Public Sub ConnDB()

    Using connection As New OleDbConnection("...")
        AddHandler connection.StateChange, AddressOf Me.OnConnectionStateChange
        Try
            connection.Open()
            'Do stuff..
        Catch ex As Exception
            Throw ex
        Finally
            RemoveHandler connection.StateChange, AddressOf Me.OnConnectionStateChange
        End Try
    End Using

End Sub

Private Sub OnConnectionStateChange(sender As Object, e As StateChangeEventArgs)
    MessageBox.Show(e.CurrentState.ToString())
End Sub