Link to home
Start Free TrialLog in
Avatar of Bob Valentine
Bob ValentineFlag for United States of America

asked on

Intermittent OleDbConnection Error

I am using vb.net and Visual Studio 2015 and have many subroutines that start by making a connection to the same Access database. But after the connection to the database has been opened and closed several times by the different subroutines, the code intermittently will throw an exception when trying to open the connection again: "con.open()". The exception says “Unspecified Exception.”

I have tried to force Garbage Collection (GC.collection) to see if that would clear up the memory allocated to keeping track of connections, but that didn't help. And I always dispose of all connections after opening them in a subroutine: con.Dispose().

How do I get the con.Open() statement to execute properly and open the connection? Can someone suggest a code example in vb.net that will resolve this issue? I have spent many hours trying to find a solution. Hopefully, someone in the vb.net community has encountered this issue and found a solution that they can share.

Here is a sample of the code from one of my subroutines that intermittently throws an exception when it tries to open the connection: cn.Open().

Sub SaveRecordsBeforeClosing()
    Dim dbProvider As String
    Dim dbSource As String
    Dim con As New System.Data.OleDb.OleDbConnection
    Dim cmd1 As New System.Data.OleDb.OleDbCommand
    Dim DataDirectory As String = Application.StartupPath & "\AssetDatabase.accdb"

    Try
        dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source=" & DataDirectory
        con.ConnectionString = dbProvider & dbSource
        cmd1.Connection = con
        con.Open()        '<------This is where the exception is thrown!
 
        'Save Record Changes
        Call SaveRecords()
        'Delete tblAssetData-Company Table
        Dim CompanyName As String
        CompanyName = Me.lblCompanyName.Text
        Dim str As String = "DROP TABLE [tblAssetData-" & CompanyName & "]"
        Dim cmd As OleDbCommand = New OleDbCommand(str, con)
        cmd.ExecuteNonQuery()
    Catch ex As Exception
        MessageBox.Show("Save Records Before Closing: " & ex.Message)
 
    Finally
        cmd1.Dispose()
        con.Dispose()
    End Try
End Sub

Open in new window

Avatar of HainKurt
HainKurt
Flag of Canada image

add close commands here

Finally
        cmd1.close()
        con.close()
        cmd1.Dispose()
        con.Dispose()
End Try

Open in new window

also try to use "using" statement...

using con As New System.Data.OleDb.OleDbConnection
  con.open()
...
End Using

Open in new window

Avatar of Bob Valentine

ASKER

Hi Huseyin ,

Thank you for your prompt response. I tried both of your suggestions, but I am still experiencing an intermittent error after the connection to the database has been opened and closed several times by other subroutines. This is baffling to me.

Is there some way to clear all open connections and release the memory or whatever that is being used to keep track of the connections before trying to open the connection again? Or, is there some way to increase the memory that is used to track the open connections?

BobV365
check all your code for open/close connection issues...

open before using / use it / close immediately / dispose all objects...

is it happening on same code/line or in any other open statement?
Thanks. I have checked for this, but I will again check all of my code to make sure that all connections are opened, used, and then closed. It always happens on the same subroutine. But it doesn't happen consistently. It is intermittent.
Avatar of AndyAinscow
If the database is on a different machine (over a network) then you might have network connection problems.
Hi Andy Ainscow, my application is on my local machine, not on a network.
'Save Record Changes
        Call SaveRecords()

What is done in the SaveRecords function?
Hi Andy, here is my code for the SaveRecords function.

    'Save Records
    Public Sub SaveRecords()
        Try
            Me.Validate()
            Me.TblAssetDataBindingSource.EndEdit()
            Me.TableAdapterManager.UpdateAll(Me.AssetDatabaseDataSet)
        Catch
            Try
                Me.TblAssetDataTableAdapter.Fill(Me.AssetDatabaseDataSet.tblAssetData)
            Catch e As Exception
                'Do Nothing
            End Try
        End Try

        Try
            Me.TblGLAccumTableAdapter.Fill(Me.AssetDatabaseDataSet.tblGLAccum)
            Me.TblGLExpTableAdapter.Fill(Me.AssetDatabaseDataSet.tblGLExp)
            Me.TblLocationsTableAdapter.Fill(Me.AssetDatabaseDataSet.tblLocations)
            Me.TblDeptTableAdapter.Fill(Me.AssetDatabaseDataSet.tblDept)
            Me.TblGroupsTableAdapter.Fill(Me.AssetDatabaseDataSet.tblGroups)
        Catch ex As Exception

        End Try

    End Sub

Open in new window

The intermittent error happens on the con.open statement. Here is a better description of the error message:

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll"

Any help in solving this intermittent connection problem will be greatly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
what happens if you use new versions of oledb drivers?

Microsoft Access Database Engine 2016 Redistributable
https://www.microsoft.com/en-us/download/details.aspx?id=54920
Is your app using other threads (backgroundworker, async) anywhere especially when accessing the database?
No, my app is not using threads anywhere.
Can you test on another PC to see if it gives this intermittent problem there?
Huseyin and Andy, Thank you so much for your suggestions. I will try all of them today and let you know the status.
Huseyin, thank you. Your suggestion for including "Persist Security Info=False;" to all connection strings seems to have solved the issue. I also added GC.Collection() after closing and disposing of all connections. I tested the code today, and I haven't had any connection exception errors. I have included a portion of my revised code below in case it may help others.

            Dim dbProvider As String
            Dim dbSource As String
            Dim con As New System.Data.OleDb.OleDbConnection
            Dim cmd1 As New System.Data.OleDb.OleDbCommand
            dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;"
            dbSource = "Data Source=|DataDirectory|\AssetDatabase.accdb"
            con.ConnectionString = dbProvider & dbSource
            cmd1.Connection = con
            con.Open()

            Dim CmdTxt As String
            CmdTxt = "SELECT RoundingDefault FROM tblMasterData WHERE CompanyName = '" & Company & "'"
            cmd1.CommandText = CmdTxt
            varRoundingDefault = cmd1.ExecuteScalar()

            cmd1.Dispose()
            con.Close()
            con.Dispose()
            GC.Collect()
        End Try

Open in new window

Thank you Huseyin for all your help. It is greatly appreciated.
>>I also added GC.Collection() after closing and disposing of all connections.

Not necessary and can actually make performance worse.
Thanks, Andy. I will try taking it out and see what happens.