none
OleDb INSERT to mdb file doesn't... sometimes. RRS feed

  • Question

  • This one reminds me eerily of the "LDB file that won't go away" problem.  Using C#.NET, Framework 2.0, MS Jet database engine, and MS Access mdb database files.

     

    C# Code snippet:

    Code Snippet

    string cmdStr = "INSERT INTO [TABLE1](ID,Name,IPAddress) VALUES(0,'Me','localhost')";

    cmd = new OleDbCommand(cmdStr, cn2);

    cmd.ExecuteNonQuery();

    int addrID = ...<unimportant 5 lines omitted>

    cmdStr = "INSERT INTO [TABLE2]" +

      "(ID,Name,Port,Firmware,Protocol,AddressID,HostID)" +

      "VALUES(0,'Server.rem',5555,'5.4','IPC'," + addrID.ToString() + ",0)";

    cmd = new OleDbCommand(cmdStr, cn2);

    cmd.ExecuteNonQuery();

     

     

    These are the last of many insert and update operations performed on the mdb file opened by the connection (cn2).  Both of the tables referenced in this snippet are empty (and untouched up to now) before this executes.  After this completes, a message is written to a log and the connection is Closed and Disposed. 

     

    When run in the debugger, this does exactly what you would expect: one new row in each of two tables.  When run without the debugger, the second INSERT sometimes fails to occur.  No errors and no exceptions, but no row.  The record inserted into TABLE1 is there; the one inserted into TABLE2 isn't.  The failure seems to be correlated with the collection of data inserted into the rest of the file.  If it fails on a particular set of data one time, it will fail with that data every time.  Other sets of data have worked successfully every time.  Head-scratcher.

     

    (I have similar trouble with the stubborn LDB file.  After I close this connection, I want to rename the mdb file.  If the data is below some size threshold, the LDB file persists and I can't rename the mdb file.  Over that size threshold, the LDB file is deleted when the connection is closed and I can rename the mdb file.  Just plain wierd.)

    Monday, April 21, 2008 9:36 PM

Answers

  • I see that I never posted my solution.  The lazy write search brought me to this sequence which resolved the issue.
    cn2.Close();
    cn2.Dispose();
    // ensure that all "Lazy-writes" to the database have finished before trying to manipulate the DB file
    GC.Collect();
    GC.WaitForPendingFinalizers();
    
    Monday, June 1, 2009 4:54 PM

All replies

  • Do you have a Try...Catch block anywhere in your code?

    Also, is the database local or is it on a network?

    BTW, if you plan on using Access/Jet reserved words (such as Name) I would recommend enclosing them in brackets in your SQL statements.
    Tuesday, April 22, 2008 12:23 PM
  • Yes, a try-catch block exists around this.  No errors, no exceptions thrown.

    The database is local.

    Just noticed a couple hours ago the use of the reserved word "NAME".  Haven't yet seen the effect of enclosing it in brackets.  Been fighting the stubborn LDB file issue this morning instead.

     

    More digging today uncovered related comments about:

    (1) connection property cn.Properties("Jet OLEDB:Transaction Commit Mode") 

    and (2) OLE DB Services=-4 in the connection string

    Do these have any promise for me?

    Tuesday, April 22, 2008 4:42 PM
  • What does your current connection string look like?
    Tuesday, April 22, 2008 6:03 PM
  • "Provider=Microsoft.JET.OLEDB.4.0; data source = myfile.mdb"

    I am now convinced that both of my troubles are due to the lazy write characteristic of the JET engine.  The solutions I have seen to this are all VB and don't seem to have C# counterparts.  What can I do in C# to force the write cache to flush (the write side equivalent of JRO.JetEngineClass.ResetCache)?  So far, the only thing I have found that forces the last write to occur is termination of the application, which is a bit too late for what I need to do.
    Wednesday, April 23, 2008 2:14 PM
  • Maybe this will halp you:

    http://support.microsoft.com/kb/240317
    Thursday, April 24, 2008 9:10 PM
  • I see that I never posted my solution.  The lazy write search brought me to this sequence which resolved the issue.
    cn2.Close();
    cn2.Dispose();
    // ensure that all "Lazy-writes" to the database have finished before trying to manipulate the DB file
    GC.Collect();
    GC.WaitForPendingFinalizers();
    
    Monday, June 1, 2009 4:54 PM
  • John,

    Well, fircing the .Net framework to do a full GC every time you want to assure that the dtaabse is written proplerly may be a tad extreme in some circumetances...

    Here is a totally differenc approach, but it has its own issues as well (i.e. Interop)

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/baefd6c5-771b-4904-8bd7-6d10a7741b6f

     

    Friday, August 27, 2010 6:04 PM