If you haven't checked out data-driven unit tests yet, you should.  They're great for those cases where you want to run your unit tests using a bunch of different data.  The logic of your unit test doesn't change -- same calls, same asserts -- but the data input and the expected return values change.  When I run into this, I put the values into an Excel spreadsheet and use that as the datasource for my tests.

Last week I needed to modify some code and unit tests that I wrote for a client 6 months ago when I was still running Windows XP and Excel 2003.  I used a handful of Visual Studio Team System data-driven unit tests on that project.  Since I originally wrote that code, I've rebuilt my laptop with Vista and Office 2007.

I did a “get latest“ from TFS, opened the solution, and ran the tests.  All the data-driven tests were dead.

Here's the error:

Unit Test Adapter threw exception: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC Excel Driver] Cannot update.  Database or object is read-only.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [42000] [Microsoft][ODBC Excel Driver] Cannot update.  Database or object is read-only..

Notice that the error message starts with “Unit Test Adapter“.  This means that the actual unit test runner ran into a problem and that it (probably) has little or nothing to do with your code (usually the error happens before it runs your code) -- now you're going to have to debug Visual Studio.

Here's the code for the unit test.

[TestMethod]

[DataSource("WhosWho.SearchData")]

public void Search()

{

string searchCorpId = TestContext.DataRow["SearchCorpId"] as string;

string resultFileName =

TestContext.DataRow["resultFileName"] as string;

string detailFileName = TestContext.DataRow["detailFileName"] as string;

string firstName = TestContext.DataRow["firstName"] as string;

string lastName = TestContext.DataRow["lastName"] as string;

string corpId = TestContext.DataRow["corpId"] as string;

string email = TestContext.DataRow["email"] as string;

string userName = TestContext.DataRow["userName"] as string;

UserEditorShim editor = new UserEditorShim();

editor.SearchCorpId = searchCorpId;

WhosWhoFacade facade = new WhosWhoFacade(new SearchStrategy(

GetTestFileData(resultFileName), GetTestFileData(detailFileName)));

facade.SearchByCorpId(editor);

Assert.AreEqual(email.ToLower(), editor.Email.ToLower());

Assert.AreEqual(firstName.ToLower(), editor.FirstName.ToLower());

Assert.AreEqual(lastName.ToLower(), editor.LastName.ToLower());

Assert.AreEqual(corpId.ToLower(), editor.CorpId.ToLower());

Assert.AreEqual(userName.ToLower(), editor.UserName.ToLower());

Assert.IsTrue(editor.IsUserInfoEnabled);

}

The code looks ok.  Nothing weird happening and I'm definitely not trying to update the Excel spreadsheet that I'm using as the data source.

I tried checking out the Excel file so that it wasn't read only in the filesystem.  Nope.  Same problem.

Here's the connection string in app.config for my unit test:

Nothing crazy here.

The answer seems to be that ADO.NET connection strings to Excel 2007 are a little pickier than they used to be.

Here's the new connections string:

The solution turned out to be adding “Driver={Microsoft Excel Driver (*.xls)};“ to the start of the connection string.

Problem solved.

-Ben

-- UPDATE 4/9/2007 10:16 am: Josh Melvin sent me an email with what sounds like the real answer to what's going on and why the connection string is so picky.  ADO.NET is probably defaulting to the Excel 2007 *.xlsx driver and since my Excel data source file is an old-school *.xls, ADO.NET couldn't read the file.  The “Driver=“ directive that I added to the connection string tells ADO.NET to open my datasource using *.xls rather than *.xlsx.  Thanks, Josh.