none
exception in deleting .mdb after reading a csv file RRS feed

  • Question

  • Hey Guys,

    I have a VERY strange problem...
    My application uses an OLEDB to access an Office2007Access database.
    It also uses a StreamReader to read some data from a .csv file.  All good so far...

    I have a function that erases erases my access data.mdb file. However, it throws an exception that application cannot access the file because it is used by another process. The first thing I have done is to check that all connections were closed. They indeed were closed.

    Then, I started debugging each row to find out where the problem is.

    It was AMAZING!

    It turned out that in another function, tha reads data from .csv file ( and does NOT have any connections to .mdb file! ) if the .csv file is longer than ~2040~2050 rows (this value somewhy varies between runs) - an excption is thrown wqhen I want to delete the .mdb ( via ANOTHER function).

    Here is the strange code:


          //  ...
          //  some code, checking etc...
          //  ....

           // ---  init -----------------
           StreamReader sr;
           try {           
                sr = new StreamReader(csvPath);
            } catch (Exception e) {           
                return null;
            }

            string inputLine = "";
            sr.ReadLine(); // ignore the first line - headrs names.   

            int counter = 0;
           
            //------     read lines from file -----------------------
            while (  (inputLine = sr.ReadLine()) != null) {
               
                // -----------  parse line   -------------------
                line = inputLine.Split(',');

                // ...
                // body -  some line parsing, and insertion to a List
                // ....


                counter++;

                /*                                       <----- IF THIS IS UNCOMENTED AND THE CSV FILE READING STOPS AFTER
                if (counter == 2040)               <----- ~2040 LINES, THEN THE OTHER FUNCTION THAT DELETES
                      break;                            <------THE Data.mdb DOES NOT THROW AN EXCEPTION.          
                 */                                                 P.S. this function that you see does not access the  Data.mdb  
                                                                                  
                                                                                   
                         
               
                
            }

            sr.Close
            return returnedList;
    }


    What is the problem? Memory overrun? Is it possible?

    Thanks a lot
    Yura
    Tuesday, December 22, 2009 10:37 AM

All replies

  • Use using statement instead, to dispose(free) resources like following:
    using(StreamReader sr = new StreamReader(csvPath))
    

    With best regards, Yasser Zamani
    Tuesday, December 22, 2009 11:10 AM
  • That did not work :(

    And as before, if I uncomment the statement - it works fine...
    Tuesday, December 22, 2009 11:20 AM
  • Please put the complete code which add/delete/edit the Data.mdb.
    Any code which use Data.mdb.
    With best regards, Yasser Zamani
    Tuesday, December 22, 2009 3:40 PM
  • If you're testing this in the IDE then the database may still be open by the IDE process. Try using the Process Explorer utility to see which process(es) has the database open.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, December 22, 2009 4:59 PM
  • I tried the proccess explorer and found out that data.mdb is indeed used by the application. However, I DID close it. so after debugging it turned out that after the loop above is performed, even though I close the connection afterwards , it does not close: Here is the code ( I put the whole code...)

    using (StreamReader sr = new StreamReader(csvPath)) {


                string inputLine = "";

                String[] line = null;
                String[] yearMonthDay = null;
                sr.ReadLine(); // ignore the first line - headrs names.   


                //m_dbConnection.Close();                       <<----------------------- CONNECTION IS FREED AND EVERYTHING
                while ((inputLine = sr.ReadLine()) != null) {                                        IS FINE IF PERFORMED HERE

                    // -----------  parse line   -------------------
                    line = inputLine.Split(',');

                    yearMonthDay = line[0].Split('-');
                    int year = int.Parse(yearMonthDay[0]);
                    int month = int.Parse(yearMonthDay[1]);
                    int day = int.Parse(yearMonthDay[2]);

                    DateTime date = new DateTime(year, month, day);               

                    decimal open = decimal.Parse(line[1]);
                    decimal high = decimal.Parse(line[2]);
                    decimal low = decimal.Parse(line[3]);
                    decimal close = decimal.Parse(line[4]);

                    // insert only the ones that are newer than the last update
                    if (date.CompareTo(lastUpdateDate) > 0) {
                        OHLC temp = new OHLC(date, open, high, low, close);
                        returnedList.Add(temp);
                    }

                   
               
                }
               
                //m_dbConnection.Close();             <<------------ IF PERFORMED HERE , CONNECTION IS NOT FREED AND
                                                                                      PROCCESS EXPLORER SHOWS THAT MY APP IS STILL
                                                                                      USING THE data.mdb



                sr.Dispose();
                returnedList.Reverse(); // this is because the newest data in CSV file is at the top, so we reverse so that
                // the dates are in descending (older --> newer) order

                return returnedList;
            }





    What is wrong in the loop?
    Tuesday, December 22, 2009 11:14 PM
  • If you need, here is the code where I opent the connection:


    public DataSaverLoader() {             

            string exePath = Directory.GetCurrentDirectory();
                  
            string m_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + exePath + "\\DataBase\\Data.mdb ;";

            try {
                // create an open the connection
                m_dbConnection = new OleDbConnection(m_ConnectionString); // create connection to database           
                m_dbConnection.Open();
            } catch (Exception ex) {
               
                OutputToUser.error("Error: In DataSaverLoader Ctor. \n{0}"+ex.Message);
               
            }
        }
    Wednesday, December 23, 2009 11:24 AM
  • Do you use m_dbConnection.Dispose() later?
    With best regards, Yasser Zamani
    Wednesday, December 23, 2009 3:55 PM
  • Yes I do.

    Actually, I do not use the close(); as written above. I use this function:

    public void closeConnections(){
            try {
                if (m_dbConnection != null) {
                    m_dbConnection.Close();
                    m_dbConnection.Dispose();
                }
                
            } catch (Exception ex) {
                // Somewhy an exception is alway thrown here, even if m_dbConnection was not null
            } 
    }
    Wednesday, December 23, 2009 6:08 PM
  • It's strange. is it possible mail me the sample? to yasser.zamani@live.com.NO_SPAM (remove .NO_SPAM)
    With best regards, Yasser Zamani
    Wednesday, December 23, 2009 6:14 PM
  • I don't have any data to test, please mail Data.mdb and others too if possible.
    With best regards, Yasser Zamani
    Thursday, December 24, 2009 6:26 PM