Irregular System.Data.OleDb.OleDbException (0x80004005): Invalid argument issue

Question

On production system we are SOMETIMES getting below error message while reading excel into datatable (same code same file will not work today but will on other day) .

System.Data.OleDb.OleDbException (0x80004005): Invalid argument. System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) 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)

But the problem is that on production server it work well today but tomorrow or day after tomorrow it will not work well and then again it will start working well.

Below is the code.

     string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + readFilePath + ";Extended Properties=\"Excel 12.0;\""; 
     ExcelConnection = new OleDbConnection(ConnectionString); 
     string ExcelQuery = "Select FORMAT(SAMPDATE,'dd/MM/yyyy') as SAMPDATE,FORMAT(LANDED_ON,'dd/MM/yyyy') as LANDED_ON,FORMAT(RECDATE,'dd/MM/yyyy') as RECDATE,* from [Sheet1$]";

     ExcelCommand = new OleDbCommand(ExcelQuery, ExcelConnection); 

     ExcelConnection.Open(); 

     ExcelAdapter = new OleDbDataAdapter(ExcelCommand); 

     ExcelAdapter.Fill(dtbExcelData); 

     ExcelConnection.Close();

I have also checked value of variable readFilePath it is correct , example value is D:\Cop\Web\ABC\PAL\FIleUploaded\ER01.xls.

I am not sure why exactly same code does not allow same excel document to get uploaded but next day same code same file works without any problem. Can somebody help me?

It must be your data (spreadsheet). Have you debugged against ones that work and ones that don't. — Crowcoder
@Crowcoder..If it is related with data then how next day same excel file gets uploaded since it gets uploaded without any problem. But thanks I will again check it and will share the findings with you. Thanks — Sagar Shirke
Same exact file? Same data? Why would you import same exact data more than once? — Crowcoder
do you know if an excel processus get started in the background when using this code? because it might fail because excel process fail to start — Franck Ngako
I see. I still don't think it has anything to do with the code you have shown. Maybe try ClosedXml to read the sheet data instead and see how that goes. — Crowcoder

Hambone Hambone · Accepted Answer · 2017-11-26T02:26:13

Several suggestions.

I suspect the issue is that there are rogue instances of Excel/Access (not sure which one is leveraged here) that are preventing the process from working as expected. I would run a process once a day (or night) to just go in and terminate any lingering client. We have Excel jobs that run daily, and invariably there are always leftover instances running, causing problems, and you can't even see them running unless you go to task manager on the "Processes" tab.

If you run something as simple as this, it will purge any open instance, even those you can't see:

foreach (var process in Process.GetProcessesByName("Excel"))
{
    process.Kill();
}

@Crowcoder gave the excellent suggestion of using ClosedXml. This, or a litany of other third party readers can read Excel files without the use of OLE, and with no dependencies beyond the .NET framework itself.

It will mean more work on your end to read the contents and turn it into a datatable, but it also creates an opportunity to skip the datatable all together, which might make sense, depending on what you are trying to do.

Your comments indicate that the issue is not with the spreadsheet content itself, but I can't help but be somewhat suspicious... after all, it's a spreadsheet. There is no such thing as strong datatyping on a spreadsheet. OLE may make an assessment that a column is a certain datatype only to find a cell that has nearly all dates has the word "NONE" in one cell.

Two things to try here is first, use a datareader instead of a datatable. This allows you to not assume anything.

In this example, I think column A is a date, but rather than assume anything I render it as a string and use DateTime.TryParse to test it.

ExcelCommand = new OleDbCommand(ExcelQuery, ExcelConnection);
OleDbDataReader reader = ExcelCommand.ExecuteReader();

DateTime orderDate;

while (reader.Read())
{
    string colA = reader.GetValue(0).ToString();
    if (DateTime.TryParse(colA, out orderDate))
    {
        // do something with orderDate here
    }
}

reader.Close();

Honestly, I'm not sure if this will work, but it might be worth a shot if you are not 1,000% sure it's not a content issue.

Clearly, this is a lot more work than just slamming the contents into a datatable, but to be honest I've never been a fan of datatables anyway.

My final suggestion -- maybe use Interop to export the file as a CSV and load it that way. It's not a great suggestion, as you are creating an Excel dependency, which may be part of the problem in the first place.

Irregular System.Data.OleDb.OleDbException (0x80004005): Invalid argument issue

1 Answers