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 ??

What do you mean by "doesn't work"? When do you run the second code block? What is the value of Conn then and its expected state? — Markus, Mar 01 '14 at 06:39
It's not working.. shows an error :( i think "conn.State = ConnectionState.Open" is wrong syntax. — Geeth, Mar 06 '14 at 02:28
Why are you using a 13-years-out-of-date version of Visual Studio? — Dai, May 05 '23 at 03:05

score 0 · Answer 1 · answered Mar 01 '14 at 07:36

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

score 0 · Answer 2 · answered Mar 01 '14 at 07:39

0

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

answered Mar 01 '14 at 07:39

Bjørn-Roger Kringsjå

9,849
6
36
64

You open without closing. Does the using block handle the closing gracefully? – HackSlash Sep 26 '18 at 22:05
1

@HackSlash Yes, it does: https://stackoverflow.com/questions/12034208/does-oledbconnection-dispose-close-the-connection – Bjørn-Roger Kringsjå Sep 27 '18 at 04:31

How to display database connection status in VB 2010

2 Answers2