0

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!

goraccio
  • 21
  • 2
  • You may want to surround the filename value with quotes in your connection string: Data Source = '{0}'. In addition, it is not clear what this value evaluates to: ConfigurationManager.AppSettings[@"Path\TestData.xlsx"] It looks like you're using AppSettings collection incorrectly. – uncoder Nov 28 '17 at 21:05
  • On which line do you get the exception? – Steve Nov 28 '17 at 21:12
  • @uncoder that's true regarding the quotes, but it didn't help. As for Path\TestData.xlsx , it's a path to the file in the system [@"C:\...\Scripts\TestData.xlsx"] – goraccio Nov 28 '17 at 21:45
  • @Steve it complains on the line connection.Open(); – goraccio Nov 28 '17 at 21:48
  • You may want to clarify which line throws the exception then. – uncoder Nov 28 '17 at 21:48
  • Please check with the debugger what file is used as Data Source in your connection string. It should be a valid full filename (complete with path) and it should be an XLSX file – Steve Nov 28 '17 at 22:00

1 Answers1

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.

Chris Dunaway
  • 10,974
  • 4
  • 36
  • 48