I am writing an application that will launch when a new ascii file is created in the 'c:\ascii' directory, read the comma-delimited data into a datatable, compute some statistics (average, etc.) and export the statistics to a logfile. The code below works for the first file, but I am having problems re-using the same datatable for the next file. I get a "file already opened exclusively by another user" error at the adaptertext.fill line. I have tried several methods for clearing the datatable after each run, all to no avail.
Any help is appreciated.
Private Sub newasciifile(ByVal source As Object, ByVal e As System.IO.FileSystemEventArgs)
Dim selectfilename As New System.IO.FileInfo(e.FullPath)
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ascii\;MODE=share deny none;Extended Properties=""text;HDR=No;FMT=Delimited;"""
Dim SQLString As String = "Select F10 from " & selectfilename.Name
Dim ConnectionText As New OleDb.OleDbConnection
ConnectionText.ConnectionString = ConnectionString
Dim AdapterText As New OleDb.OleDbDataAdapter(SQLString, ConnectionText)
Dim dtModel As New DataTable("currentdata")
AdapterText.Fill(dtModel) '***THIS IS WHERE THE "FILE ALREADY OPENED..." ERROR OCCURS ON THE SECOND NEW FILE CREATED
Dim avgheading As Double = dtModel.Compute("avg(F10)", "")
Dim readingtime As Date = Now()
Dim path As String = "c:\log\logfile.txt"
Dim sw As StreamWriter = File.AppendText(path)
sw.WriteLine(selectfilename.Name.ToString & "," & readingtime.ToString & "," & avgheading.ToString)