OLEDBconnection exception: invalid argument in c#

Question

It's my first try to to read test data for my automated test scripts from excel sheet using oledbconnection with Dapper. Whatever i do, i get invalid argument exception. I need to select the cell from column C based on the values in columns A and B. Here is the code:

class ExcelDataAccess
{
    public static string TestDataFileConnection()
    {
        var fileName = ConfigurationManager.AppSettings[@"Path\TestData.xlsx"];
        var con = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = {0}; Extended Properties='Excel 12.0 Xml;HDR=YES;'", fileName);
        return con;
    }

    public static UserData GetTestData(int TestCaseNumber, string Key)
    {
        using (var connection = new OleDbConnection(TestDataFileConnection()))
        {
            connection.Open();
            var query = string.Format("select * from [DataSet$] where [TestCaseNumber]='{0}' and [Key]='{1}'", TestCaseNumber, Key);
            var value = connection.Query<UserData>(query).FirstOrDefault();
            connection.Close();
            return value;
        }
    }
}

In the UserData class I get and set public variables with the table headers.

Thanks for any help!

Answer 1

I'm guessing this line is the problem:

var fileName = ConfigurationManager.AppSettings[@"Path\TestData.xlsx"];

Do you really have an entry in your app.config with a key of Path\TestData.xlsx? Can you show that line of your app's .config file?

I think you may have used the wrong key. Double check your entries in the appSettings section of your app's .config file.