Solved

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

Posted on 2006-07-12
8
271 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
ID: 17095777
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
ID: 17097143
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
ID: 17100265
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 11

Expert Comment

by:coopzz
ID: 17104796
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
 

Author Comment

by:dobbinjp
ID: 17110755
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
ID: 17118974
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
ID: 17122073
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
ID: 17126646
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 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