CODEWITHSUNDEEP
c#asp.netexceldatatableoledbdataadapter
Sagar Shirke
Sagar Shirke

Reputation: 686

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

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?

Upvotes: 1

Views: 1461

Answers (1)

Hambone
Hambone

Reputation: 16397

Several suggestions.

  1. 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();
}
  1. @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.

  1. 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.

  1. 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.

Upvotes: 1

Related Questions