Why does one ADO.NET Excel query work and another does not?

Question

I'm working on a SharePoint workflow, and the first step requires me to open an Excel workbook and read two things: a range of categories (from a range named, conveniently enough, Categories) and a category index (in the named range CategoryIndex). Categories is a list of roughly 100 cells, and CategoryIndex is a single cell.

I'm using ADO.NET to query the workbook

string connectionString =
    "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + temporaryFileName + ";" +
    "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();

OleDbCommand categoryIndexCommand = new OleDbCommand();
categoryIndexCommand.Connection = connection;
categoryIndexCommand.CommandText = "Select * From CategoryIndex";

OleDbDataReader indexReader = categoryIndexCommand.ExecuteReader();
if (!indexReader.Read())
    throw new Exception("No category selected.");
object indexValue = indexReader[0];
int categoryIndex;
if (!int.TryParse(indexValue.ToString(), out categoryIndex))
    throw new Exception("Invalid category manager selected");

OleDbCommand selectCommand = new OleDbCommand();
selectCommand.Connection = connection;
selectCommand.CommandText = "SELECT * FROM Categories";
OleDbDataReader reader = selectCommand.ExecuteReader();

if (!reader.HasRows || categoryIndex >= reader.RecordsAffected)
    throw new Exception("Invalid category/category manager selected.");

connection.Close();

Don't judge the code itself too harshly; it's been through a lot. Anyway, the first command never executes correctly. It doesn't throw an exception. It just returns an empty data set. (HasRows is true, and Read() returns false, but there is no data there) The second command works perfectly. These are both named ranges.

They are populated differently, however. There's a web service call that fills up Categories. Those values are displayed in a dropdown box. The selected index goes into CategoryIndex. After hours of banging my head, I decided to write a couple of lines of code so that the dropdown's value goes into a different cell, then I copy the value using a couple of lines of C# into CategoryIndex, so that the data is set identically. That turned out to be a blind alley, too.

Am I missing something? Why would one query work perfectly and the other fail to return any data?

Answer 1

I have found the issue. Excel was apparently unable to parse the value in the cell, so it was returning nothing. What I had to do was adjust the connection string to the following:

string connectionString =
    "Provider=Microsoft.ACE.OLEDB.12.0;" +
    "Data Source=" + temporaryFileName + ";" +
    "Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";

It would have been helpful if it would have thrown an exception or given any indication of why it was failing, but that's beside the point now. The option IMEX=1 tells Excel to treat all values as strings only. I'm quite capable of parsing my own integers, thankyouverymuch, Excel, so I didn't need its assistance.