[VB.NET 4.0] System.Data.OleDb.OleDbException- system resources exceeded

[13mic13]

Hello,

I have an aplication that checks in specific time if database file was changed and if the changes were made shows datatable in dgv

All code is given in Timer Tick. Unfortunately on each 1000 ticks I receive error - System.Data.OleDb.OleDbException (0x800045005) system resources exceeded.
I tried to make a code that dispose / close everything to prevent more than 1000 objects connections but still error appears

My sample code (I removed thingst that are not needed)

 
Try
Using Myconn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & mypath & ";" & "Extended Properties=dBASE IV;")
  Using Mycmd As New OleDb.OleDbCommand("SELECT * FROM " & Filename, Myconn)
     Using Mydt As New DataTable
        Using Myadapter As New OleDb.OleDbDataAdapter(Mycmd)
            Myadapter.Fill(Mydt)                                         <- CODE BREAK  error System.Data.OleDb.OleDbException (0x800045005)
           

.....

            Myadapter.Dispose()
            GC.SuppressFinalize(Myadapter)
            Myadapter.Dispose()
         End Using
         Mydt.Clear()
         Mydt.Dispose()
         Mydt.Clear()
         GC.Collect()
         GC.SuppressFinalize(Mydt)
         Mydt.Dispose()
         End Using
      Mycmd.Dispose()
      Mycmd.Connection = Nothing
      GC.SuppressFinalize(Mycmd)
    End Using
    Mycn.ConnectionString = Nothing
    Mycn.Close()
    If Not cn Is Nothing Then
       CType(Mycn, IDisposable).Dispose()
    End If
   GC.SuppressFinalize(Mycn)
   Mycn.Dispose()
 End Using

Catch ex1 As Exception
Timer1.Stop()
    MessageBox.Show("Error occurred: " & vbCrLf & ex1.ToString)
Finally
    GC.Collect()
End Try

Does anyone could help me solve this problem ?

[13mic13]

For sure I have some problems with data adapter when I'm removing code related to data adapter I can make >1000 repeats ...

I also tried this:

Set running in timer tick - function -> and in function

Dim mydt As New DataTable
Dim mycn As Odbc.OdbcConnection
Dim mycmd As Odbc.OdbcCommand
Dim mydr As Odbc.OdbcDataReader


        Try
            mycn = New Odbc.OdbcConnection("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=" & mypath)
            mycmd = New Odbc.OdbcCommand("SELECT * FROM " & Filename, mycn)
            mycn.Open()
            mydr = mycmd.ExecuteReader(CommandBehavior.CloseConnection)    <- System.Data.Odbc.OdbcException (0x80131937): ERROR [HY001]
            myDataTable.Load(mydr)
            mydr.Close()

        Catch ex1 As Exception
            myDataTable = Nothing

            MessageBox.Show("Error occurred: " & vbCrLf & ex1.ToString)
        Finally
            If mycn.State = ConnectionState.Open Then
                mycn.Close()
            End If
            mycn.ConnectionString = Nothing
            mycn.Dispose()

            mycmd.Dispose()
            GC.Collect()
        End Try
        Return myDataTable

I receive an error :
System.Data.Odbc.OdbcException (0x80131937): ERROR [HY001]

[HanneSThEGreaT]

I am a bit late to the party, and all the booze is finished....

Anyways, have you tried specifying the fields explicitly, instead of using *. For example :

SELECT Field1, Field2, Field3 FROM " & Filename, mycn

Give that a shot and let us know if that worked.

Good luck !

Hannes

[dglienna]

or open the table in SQL Management Studio and get it working. It will show each error

[DataMiser]

I am curious how you are checking to see if the file has changed here or are you just doing that select on every tick?

Have you considered trying to use the file system watcher instead? I haven't tried it with a Access database but I would think it would probably trigger when the file is changed, could be a better solution than what you have now.

[dglienna]

sure. each tick takes 15-20ms to fire. DB Read prolly adds another 1/2 second to each one.

[13mic13]

Hello,

thank You all for the answers.

DataMiser:
In each tick I'm checking File.Exists. Then File.GetLastWriteTime if time is different than time stored in memory, code is executed.
I'll try to use File system watcher - to be honest I even didn't know this function


David:
from the beginning I had set up time between tick to 30sec. Unfortunately always getting system error after 1000 ticks

Hannes:
SELECT Field1, Field2, Field3 FROM " & Filename, mycn <- also didn't helped


I was trying to do something else that partially solved my problem I'm using now dataAdapter instead dataReader and I'm not closing/disposing connection and dataAdapter.
At this moment application runs more than 6000 data reads, but I'm afraid that a the end I'll have problems with lack of memory...

Thank You