Solved

oledb "file already opened exclusively", re-use datatable when new text files are created

Posted on 2006-07-12
8
270 Views
Last Modified: 2008-02-01
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)
        sw.Flush()
        sw.Close()

    End Sub

'****
0
Comment
Question by:dobbinjp
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:coopzz
Comment Utility
It doesn't look like your closing the connection.

After the Fill method on the adapterText put this in.
----------

ConnectionText.Close
0
 
LVL 8

Expert Comment

by:plq
Comment Utility
Use try catch finally to ensure that the connection always gets closed even when an error occurs:

try
        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)
        sw.Flush()
catch
      throw
finally
        try
         sw.Close()
        end try
end try
0
 

Author Comment

by:dobbinjp
Comment Utility
Thank you both for your quick responses. We are definitely getting somewhere. With the connectiontext.close() line I can now run the program for 4-5 files, but I do finally get the same error. I didn't quite follow the try/catch syntax (that is new to me...I need to learn it). I put it in just as you have it and the sw.close() line was not recognized and it wanted more code in the throw line.

I inserted the following if/then statement after the adapter was filled:

        If ConnectionText.State = ConnectionState.Open Then
            ConnectionText.Close()
        End If

I was thinking this could force it to close, but I still get the file opened exclusively by another user error. I tried adding a message box at the end of the code:

        If ConnectionText.State = ConnectionState.Closed Then
            MsgBox("we are good to go, it is closed!")
        Else
            MsgBox("get ready for an error, it was not closed!")
        End If

It read "we are good to go..." right before throwing that error so I am really confused now. Might it be something other than the connection being closed??? Thanks again for your assistance.
0
 
LVL 11

Expert Comment

by:coopzz
Comment Utility
ok example below to always close the connection even if an error occurs.
If it still occurs do a debug and watch it go through until it hits the error again if your running this from an event handler ie Button1_Click make sure it can't be run twice at the same time. ( it may also be because you opened it somewhere else and forgot to close it.)

---------------------

   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
        Dim sw As StreamWriter
try
        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"
        sw = File.AppendText(path)
        sw.WriteLine(selectfilename.Name.ToString & "," & readingtime.ToString & "," & avgheading.ToString)
        sw.Flush()
catch ex as exception
   msgbox(ex.message)
finally
   if (Connection is nothing) = false then ConnectionText.Close() 'close the connection
   if (sw is nothing) = false then sw.close
end try
    End Sub
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:dobbinjp
Comment Utility
Thanks again for your response, coopzz.

I implemented your code and it handles the exception now. The message was the same as before: "the file is opened exclusively by another user..." but that is better than having to go back to Visual Studio to stop things. The code launches when a file is created in the c:\ascii directory, so the connection is not open anywhere else.

I now believe the ascii file creation process (unfortunately, run by a DOS batch file since the mfr could not give me dll's) is locking the files. The reason I think this is that when I manually drop the ascii files in the watched directory, the program works great. I have modified the creation routine to copy the ascii files to a separate folder (which is watched by my code now) and the code I have posted will do the number crunching on those files. I am now getting "missing one or more parameters" on the adaptertext.fill line for some reason. I think I can probably figure out what is causing that, but I was wondering if you have any ideas or suggestions for how to do this more efficiently.

I hope my post makes sense...thanks again for helping me out!

Full details of the exception are below:

The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.,System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at lock_currents_final.Form1.newasciifile(Object source, FileSystemEventArgs e) in C:\Documents and Settings\dobbinjp\My Documents\Visual Studio 2005\Projects\current_data\lock_currents_final\Form1.vb:line 44
0
 
LVL 11

Accepted Solution

by:
coopzz earned 500 total points
Comment Utility
Ok, you may have a copy of choices  (does the file eventualy drop the connection from the bat file) if so you can put a do/for loop around the fill with a time outs and delays to catch it when it's done.[Application.DoEvents, Thread.Sleep(500) ]

'as in something like this (untested) you'll need to import system.threading
for n as integer = 1 to 20 'it will try 20 times
try
    Application.DoEvents()
    AdapterText.Fill(dtModel)
catch
    Thread.Sleep(500) 'with half second delays.
end try
next

The other is to copy the file first to another directory, and call it from there and delete when finished with.  [io.file.copy(sSource, sDest)]

Warning though if the file is still open for some reason by the other program you may end up with a buggered file anyway, so you may want to look into why the files are not being unlocked, if you dont have the source for it drop a brick on the people who wrote it and get some info on how to fix it or work around it.

>>missing one or more parameters
Is most likely the query string (most probably the file isn't where it should be, or the field)

Good Luck
0
 

Author Comment

by:dobbinjp
Comment Utility
coopzz,

Thank you SO MUCH!!! That worked great. I think the mfr software (called from the batch file) was holding on to the ascii file...the for next and try loop you recommended gets around it beautifully! I wish I didn't have to go through that, but I am glad to finally have a solution. I had been stuck for a full week on this problem. I think the missing parameter error was like you suggested: in some cases the code was running so fast (and the batch file so slow) that the ascii file wasn't created yet. I used filemon to determine that.

Thanks again for working with me. For those who run into similar problems in the future, my working code is now as follows:

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
        Dim sw As StreamWriter

        Try
            ConnectionText.ConnectionString = ConnectionString
            Dim AdapterText As New OleDb.OleDbDataAdapter(SQLString, ConnectionText)
            Dim dtModel As New DataTable("currentdata")
            For n As Integer = 1 To 20 'it will try 20 times
                Try
                    Application.DoEvents()
                    AdapterText.Fill(dtModel)
                Catch
                    System.Threading.Thread.Sleep(500) 'with half second delays.
                End Try
            Next
            ConnectionText.Close()

            Dim avgheading As Double = dtModel.Compute("avg(F10)", "")
            Dim readingtime As Date = Now()

            Dim path As String = "c:\logfiles\currentdata.txt"
            sw = File.AppendText(path)
            sw.WriteLine(selectfilename.Name.ToString & "," & readingtime.ToString & "," & avgheading.ToString)
            sw.Flush()
        Catch ex As Exception
            MsgBox(ex.Message)
            MsgBox(ex.GetBaseException.ToString)
        Finally
            If (ConnectionText Is Nothing) = False Then
                ConnectionText.Close()
                ConnectionText.Dispose()
            End If
            If (sw Is Nothing) = False Then
                sw.Close()
            End If

        End Try

    End Sub
0
 
LVL 11

Expert Comment

by:coopzz
Comment Utility
just a word of warning you may want to add this bit so you can find out if it was something else in the errors.

            For n As Integer = 1 To 20 'it will try 20 times
                Try
                    Application.DoEvents()
                    AdapterText.Fill(dtModel)
                Catch ex as exception
                    if (n >= 20) then throw ex '<-- throw again so that you can see the error now.
                    System.Threading.Thread.Sleep(500) 'with half second delays.
                End Try
            Next

Cheers

CooPzZ
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now