OleDbException when opening modified spreadsheet

[holly-hacker]

I am using LinqToExcel v1.11.0 in an application that reads an Excel document, but modifying the document after having read it and then reading it again throws an exception:

System.Data.OleDb.OleDbException (0x8000FFFF): No error message available, result code: E_UNEXPECTED(0x8000FFFF).
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at LinqToExcel.Query.ExcelQueryExecutor.GetDataResults(SqlParts sql, QueryModel queryModel)
   at LinqToExcel.Query.ExcelQueryExecutor.ExecuteCollection[T](QueryModel queryModel)
   at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteCollectionQueryModel[T](QueryModel queryModel, IQueryExecutor executor)
   at Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(QueryModel queryModel, IQueryExecutor executor)
   at Remotion.Data.Linq.QueryModel.Execute(IQueryExecutor executor)
   at Remotion.Data.Linq.QueryProviderBase.Execute[TResult](Expression expression)
   at Remotion.Data.Linq.QueryableBase`1.GetEnumerator()
   at <my application>()

If I try to open another document after that, the application hangs and I managed to capture the same stacktrace with dnSpy:

    [Managed to Native Transition]
    System.Data.dll!System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(System.Data.OleDb.OleDbConnectionString constr, ref System.Data.OleDb.SessionWrapper sessionWrapper) (IL≈0x00BE, Native=0x000007FE8D00E220+0x158)
    System.Data.dll!System.Data.OleDb.OleDbConnectionInternal.OleDbConnectionInternal(System.Data.OleDb.OleDbConnectionString constr, System.Data.OleDb.OleDbConnection connection) (IL=0x0062, Native=0x000007FE8D00C750+0xFD)
    System.Data.dll!System.Data.OleDb.OleDbConnectionFactory.CreateConnection(System.Data.Common.DbConnectionOptions options, System.Data.Common.DbConnectionPoolKey poolKey, object poolGroupProviderInfo, System.Data.ProviderBase.DbConnectionPool pool, System.Data.Common.DbConnection owningObject) (IL=0x0013, Native=0x000007FE8D00C6C0+0x6D)
    System.Data.dll!System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(System.Data.Common.DbConnection owningConnection, System.Data.ProviderBase.DbConnectionPoolGroup poolGroup, System.Data.Common.DbConnectionOptions userOptions) (IL≈0x0015, Native=0x000007FE8D00C5C0+0x39)
    System.Data.dll!System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection owningConnection, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions, System.Data.ProviderBase.DbConnectionInternal oldConnection, out System.Data.ProviderBase.DbConnectionInternal connection) (IL≈0x0072, Native=0x000007FE8D00B630+0x112)
    System.Data.dll!System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory, System.Threading.Tasks.TaskCompletionSource<System.Data.ProviderBase.DbConnectionInternal> retry, System.Data.Common.DbConnectionOptions userOptions) (IL≈0x0018, Native=0x000007FE8D00ABE0+0x9A)
    System.Data.dll!System.Data.ProviderBase.DbConnectionInternal.OpenConnection(System.Data.Common.DbConnection outerConnection, System.Data.ProviderBase.DbConnectionFactory connectionFactory) (IL≈0x0000, Native=0x000007FE8D00AB60+0x16)
    System.Data.dll!System.Data.OleDb.OleDbConnection.Open() (IL=0x0012, Native=0x000007FE8D00AAA0+0x32)
>   LinqToExcel!LinqToExcel.Query.ExcelQueryExecutor.GetDataResults(LinqToExcel.Query.SqlParts sql, Remotion.Data.Linq.QueryModel queryModel) (IL=0x0023, Native=0x000007FE8D587820+0x5F)
    LinqToExcel!LinqToExcel.Query.ExcelQueryExecutor.ExecuteCollection<LinqToExcel.RowNoHeader>(Remotion.Data.Linq.QueryModel queryModel) (IL≈0x000F, Native=0x000007FE8D5848D0+0x51)
    Remotion.Data.Linq!Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteCollectionQueryModel<LinqToExcel.RowNoHeader>(Remotion.Data.Linq.QueryModel queryModel, Remotion.Data.Linq.IQueryExecutor executor) (IL=epilog, Native=0x000007FE8D584420+0x85)
    Remotion.Data.Linq!Remotion.Data.Linq.Clauses.StreamedData.StreamedSequenceInfo.ExecuteQueryModel(Remotion.Data.Linq.QueryModel queryModel, Remotion.Data.Linq.IQueryExecutor executor) (IL≈0x0039, Native=0x000007FE8D5842C0+0xD7)
    Remotion.Data.Linq!Remotion.Data.Linq.QueryModel.Execute(Remotion.Data.Linq.IQueryExecutor executor) (IL=epilog, Native=0x000007FE8D583D10+0x49)
    Remotion.Data.Linq!Remotion.Data.Linq.QueryProviderBase.Execute<System.Collections.Generic.IEnumerable<LinqToExcel.RowNoHeader>>(System.Linq.Expressions.Expression expression) (IL≈0x000C, Native=0x000007FE8D57F550+0x4C)
    Remotion.Data.Linq!Remotion.Data.Linq.QueryableBase<LinqToExcel.RowNoHeader>.GetEnumerator() (IL≈0x0000, Native=0x000007FE8D57F4A0+0x57)
    <my application>() (...)

My code looks like the following

// running GC to make sure the issue isn't an old object
GC.Collect();

ExcelQueryFactory excel = null;
try
{
    excel = new ExcelQueryFactory(path);
}
catch (InvalidOperationException ex)
{
    excel?.Dispose();

    if (ex.Message.Contains("OLEDB"))
    {
        // show user a message saying they probably need to install the redistributable...
        bool is64Bit = IntPtr.Size == 8;
        throw new Exception($"...", ex);
    }
    else
    {
        // rethrow others
        throw;
    }
}

using (excel)
{
    foreach (var row in excel.WorksheetNoHeader("...").Skip(9))
    {
        if (string.IsNullOrEmpty(row[0].Value as string))
        {
            break;
        }

        // ...
    }

    // ...
}

My application runs in a 64-bit Windows 7 Ultimate virtual machine. If I completely restart my application, it works again. I have tried switching to v2.0.0 pre-release 2, but the problem persists.

[mrworkman]

LinqToExcel doesn't support modifications to the file while it's open. So you should close the file prior to making the changes, and re-open it afterward...

But, regardless, it should not hang as a result of the exception you get. How are you performing the modifications? Programmatically, or by hand via the Excel application? This would be helpful to know so I can reproduce it.

Thanks!

[holly-hacker]

I was modifying the Excel file by hand through the Excel program. I do think I tried closing the file before reading it again but I'll try again monday when I get back to work.

[mrworkman]

Ok, cool. Thanks for getting back to me.

[holly-hacker]

I have gone through the following steps:

• Open my application, load the excel file
• Open Excel, modify the spreadsheet, save & close
• Load the modified excel file in my application.

This still gives me the reported exception.

[holly-hacker]

Any updates on this?

[mrworkman]

Sorry, no. I'll see if I can set some time aside this weekend to look into it.

[mrworkman]

I haven't been able to reproduce what you're seeing, but it could be related to connection pooling. Please add the following line right after instantiation, and let me know if that does/doesn't help:

excel.OleDbServices = OleDbServices.AllServicesExceptPooling;

[holly-hacker]

I haven't opened this VM in a while, but I can't seem to reproduce it anymore either. It's most likely something that got fixed with an update to Windows or Excel. I'll reopen this issue if the issue returns and your suggestion doesn't fix it. Sorry for wasting your time.