Click here to Skip to main content
15,885,365 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
I have a service I wrote that uses a filewatcher. On create it takes the excel file and truncates table and import it.
It works on my Windows 7 box. When I drag a new file into the folder it imports it. If i drag a newer file it deletes the old data and imports the new data.
On Windows 2012 Server the service works on the first import. on the second drop of a excel file (second time the service tries to import) I get

Error during import of file 20151106.xls
Exception occured in Microsoft JET Database Engine
Exception: 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.

This shouldn't happen as I close the connections

C#
private void copy_excel(string FullPath)
       {

           //Excel command
           string cmdText = "SELECT [ID], [NAME], [PRODID], [FTDATE]"
              " FROM [DAILY$]";

           //Excel connection string
           string excelConnString = "provider=microsoft.jet.oledb.4.0;" +
               " Data Source=" + FullPath + " ;Extended Properties=" +
               "\"Excel 8.0;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";

//OleDb Command using cmdText and Connection to read Excel
            using (OleDbConnection oleDbConn = new OleDbConnection(excelConnString))
            {
                using (OleDbCommand oleDbCmd = new OleDbCommand(cmdText, oleDbConn))
                {
                    //open excel
                    oleDbConn.Open();
                    using (OleDbDataReader dr = oleDbCmd.ExecuteReader())
                    {
                        DataTable dt = new DataTable();

                        dt.Load(dr); //Load the excel we want to write to Oracle
                        //bulkCopyOracle(dt); commented out for debug 

                    }
                }
            }
      }


The service works fine on my windows 7 box and works for the first file on Windows 2012 server. Why is the service leaving the connection oleDbConn open on Windows Enterprise Server 2012? ?
Posted
Updated 7-Dec-15 9:19am
v8

1 solution

Why are you writing poor code? You should be closing and disposing the connection yourself, not depending on some hidden functionality in one application and wondering why it doesn't work in another.

You really need to look into the using statement. It'll Dispose of the objects for you without have to write all that silly "if open" code.
 
Share this answer
 
v2
Comments
Philippe Mori 7-Dec-15 12:37pm    
Effectivelly, user should use using pattern consistently and avoid manual disposing.

And splitting the code in multiple functions won't hurt.
Sergey Alexandrovich Kryukov 7-Dec-15 14:28pm    
That's a good idea, only "using" statement does not cover all cases; please see my comment below. "Using" statement is nothing but syntactic sugar, very useful one.
—SA
Sergey Alexandrovich Kryukov 7-Dec-15 14:26pm    
Well, a 5, but I would only add one thing: the "using" statements can only be applied when you create and dispose some object in the same context (in particular, the same method). There are cases when it is not applicable, say when you create some object when you create or show some window and need to dispose when you close this window. For that reasons, the dispose chain is created, such as the one based on Dispose(bool), but anyone can create any similar mechanism.
—SA

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900