Issue with connecting to an MDB file (and some observations)

Oct 19, 2010 at 7:18 PM
Edited Oct 19, 2010 at 7:37 PM

Hi,

I've followed the tutorial up to Part 4 and it has been very detailed, accurate and well-composed. However, I'm having some issues with the database connection to MvcMusicStore.mdb and I'm wondering if I'm missing a step so obvious to most that is was left out of the instructions.

After adding the ADO.NET Entity Data Model and selecting "Generate from database" > Next, I have the same result as your "Choose Your Data Connection" screenshot. "MvcMusicStore.mdb" is in the combobox and is also added as a new database connection to my Server Explorer window under Data Connections. Entity connection string is:

metadata=res://*/Models.StoreDB.csdl|res://*/Models.StoreDB.ssdl|res://*/Models.StoreDB.msl;provider=System.Data.SqlClient;provider connection string="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MvcMusicStore.mdf;Integrated Security=True;User Instance=True"

After changing "MvcMusicStoreEntities" to "MusicStoreEntities", I click Next and get the following error: "An error occurred while connecting to the database. The database might be unavailable. An exception of type 'System.Data.SqlClient.SqlException' occurred. The error message is: 'Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.'."

After some experimenting I'd like to make the following observations - please don't hesitate to correct me on these:

1) The "|DataDirectory|" in the wizard-generated connection string refers to the DATA folder for the SQLServer instance, not the App_Data folder in the Project.

2) If I click "New Connection..." I can step through the Connection Properties dialog, select Attach a database file" and browse to MvcMusicStore.mdb to get that path in the connection string, but then I get all kinds of errors - I think SQLServer only wants to deal with mdb files inside its DATA folder.

3) I copy the MvcMusicStore.mdb files into the SqlServer DATA folder. Go back to the wizard, click Next - still no joy.

4) I click New Connection... to open the Connection Properties dialog and select "Attach a database file" again - only this time I select the MvcMusicStore.mdb file I just added. Close, and a new connection is in the combo box and Server Explorer: "MvcMusicStore1". Click Next and voila! I'm connected and I see the Database Objects in the next wizard page!

(Now my connection string is: metadata=res://*/Models.StoreDB.csdl|res://*/Models.StoreDB.ssdl|res://*/Models.StoreDB.msl;provider=System.Data.SqlClient;provider connection string='Data Source=FALCON\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MvcMusicStore.mdf";Initial Catalog=MvcMusicStore;Integrated Security=True')

5) When I check the MSSMS a new database is there: MvcMusicStore".

So my bottom line question is: is it possible to use EF4 to connect directly to and open an mdb file inside a project folder without adding the MDB to the SqlServer DATA folder? If so, what am I missing?

(Sorry this was so long-winded.)

 

Oct 22, 2010 at 1:22 PM

I thought I'd follow up on this issue with what I've recently learned.

None of this info will be new to ASP.NET coders with a lot of past experience with SQL Server, but for us novices it may shed some light on some issues with this otherwise excellent tutorial.

With SQL Server Express 2005 it WAS possible to read MDB files without first creating a database from them: they were called "user instances" and required that the User Instance value in the connection string be set to True.

However that feature was dropped and is not available in SQL Server Express 2008. So for this tutorial, the MDB does need to be attached and loaded into the master catalog.

As far as the use of EF4 in the tutorial, the exercise becomes a "Database First" method to build the entities and use LinQ to Entities (which still has some value as a path to choose).

One thing that confused me on top of not being able to connect to the MDB file was that a connection string to the MDB kept appearing in my Server Explorer pane and would of course error because it couldn't connect to a real database in the master catalog. I discovered that VS has a feature of automatically creating a connection string for any MDB inside App_Data.

So removing the MDB file from App_Data (after manually loading it into SQL Server and then creating the ADO.NET Entity Data Model from the Database) seemed to resolve the issues.

Coordinator
Oct 25, 2010 at 11:16 PM

Thanks for following up, and glad you got it sorted out, Wayne. Two minor things to add:

  • SQL Server data files are MDF files, not MDB. MDB is the file extension file extension used for Access database files. You'll notice if you look at your connection string that it's pointing to MvcMusicStore.mdf.
  • One other option you've got if you're having trouble with user instances is to run the SQL create scripts against a local database instance of SQL Server 2005 or SQL Server 2008.
Oct 26, 2010 at 3:04 AM
Jon,
 
Sorry, I meant MDFs. I had to deal with Access on my former job and I still have nightmares about that.
 
One other problem I had was with the Authorization database area.
 
In addition to copying the files you described, you also need to copy some settings in the web.config file of the MVC app created from the full MVC2 web app template:
<membership>, <profile> and <rolemanager> nodes.
 
Even after copying these I still had problems with the ASP.NET Configuration website – the Security tab kept throwing me into the Provider tab. It couldn’t recognize the database.
I went to [%system root%]\Microsoft.NET\Framework\versionNumber\aspnet_regsql.exe and ran the database wizard. Then I created the following connection string and added it to my web.config <add name="ApplicationServices"
     connectionString="Data Source=FALCON\SQLEXPRESS;Initial Catalog=aspnetdb;Integrated Security=True" providerName="System.Data.SqlClient" />
and the Configuration website started working correctly.
 
Hope this helps.