Unit Testing Error - The unit test adapter failed to connect to the data source or to read the data

Question

I'm using VSTS 2K8 and I've set up a Unit Test Project. In it, I have a test class with a method that does a simple assertion. I'm using an Excel 2007 spreadsheet as my data source.

My test method looks like this:

        [DataSource("System.Data.Odbc", 
        "Dsn=Excel Files;dbq=|DataDirectory|\\MyTestData.xlsx;defaultdir=C:\\TestData;driverid=1046;maxbuffersize=2048;pagetimeout=5", 
        "Sheet1", 
        DataAccessMethod.Sequential)]
    [DeploymentItem("MyTestData.xlsx")]
    [TestMethod()]
    public void State_Value_Is_Set()
    {
        string expected = "MD";
        string actual = TestContext.DataRow["State"] as string;
        Assert.AreEqual(expected, actual);
    }

As indicated in the method decoration attributes, my Excel spreadsheet is on my local C:/ Drive. In it, the sheet where all of my data is located is named "Sheet1".

I've copied the Excel spreadsheet into my project and I've set its Build Action = "Content" and I've set its Copy to Output Directory = "Copy if Newer".

When trying to run this simple unit test, I receive the following error:

The unit test adapter failed to connect to the data source or to read the data. For more information on troubleshooting this error, see "Troubleshooting Data-Driven Unit Tests" (http://go.microsoft.com/fwlink/?LinkId=62412) in the MSDN Library. Error details: ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Office Access database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.

I've verified that the sheet name is spelled correctly (i.e. Sheet1) and I've verified that my data sources are set correctly.

Web searches haven't turned up much at all. And I'm totally stumped.

All help or input is appreciated!!!!

Answer 1

Change the "Build Action" property of Excel(.xlsx) to Content and "Copy to Output Directory" to Copy if newer.

This worked for me when I got the same error as urs...

++ also dont forget to browse and add the excel file in Deployment tab of TestRun.config file that is in the server soution..:)

Answer 2

You should make sure your deployment is enabled in your test settings you have chosen!!!

Good luck!!!

Answer 3

I had a similar problem but was just trying to deploy a simple XML file.

The problem turned out to be the filename of the file I was deploying was too long.

Answer 4

I had two problems here, one was the miss-spelling of the desired sheet in my excel file. The other one and I think the important one was the difference between the .xls and .xlsx files. In my machine I have the Office 2007 so automatically the extension of excel files is .xlsx, but I was supposed to write a test case for a project that has been already implemented and it was using this DataSource:

[DataSource("System.Data.Odbc",
            @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=Test.xls;DefaultDir=.",
            "Data$", DataAccessMethod.Sequential)]

I created an excel file in a machine that had the Office version before 2007 so the extension was .xls and then I moved this excel file to my machine. This way I could solve my problem.

Answer 5

Solutions that I tried 1) Added data file in deployment section of local settings 2) Changed the properties of the file to "Content" and "Copy if newer" 3) Hard-coding the location of the file in deployment item attribute and in the connection string in app.config file.

None of the above worked.

Eventually I found out that the registry did not have the Jet dlls registers. I exported the Jet folder(HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet) from the registry of a machine on which I was not getting the error and imported that on to the machine I was getting the error. This resolved the issue for me.

Answer 6

The Sheet Name should have a '$' appended otherwise it will definitely fail (still valid for Visual Studio 2015) like: "Sheet1$".