Bob Valentine
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().
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
also try to use "using" statement...
using con As New System.Data.OleDb.OleDbConnection
con.open()
...
End Using
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
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?
open before using / use it / close immediately / dispose all objects...
is it happening on same code/line or in any other open statement?
ASKER
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.
If the database is on a different machine (over a network) then you might have network connection problems.
ASKER
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?
Call SaveRecords()
What is done in the SaveRecords function?
ASKER
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
ASKER
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.OleDbEx ception' occurred in System.Data.dll"
Any help in solving this intermittent connection problem will be greatly appreciated.
"An unhandled exception of type 'System.Data.OleDb.OleDbEx
Any help in solving this intermittent connection problem will be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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?
ASKER
No, my app is not using threads anywhere.
Can you test on another PC to see if it gives this intermittent problem there?
ASKER
Huseyin and Andy, Thank you so much for your suggestions. I will try all of them today and let you know the status.
ASKER
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
ASKER
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.
Not necessary and can actually make performance worse.
ASKER
Thanks, Andy. I will try taking it out and see what happens.
Open in new window