Oct 19, 2010 at 7:18 PM
Edited Oct 19, 2010 at 7:37 PM
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
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.)