EF 4.1 code-first connectionstring in web.config

I'm having a bit of a nightmare day today. I've been working successfully on a code-first development locally and everything has been going along just fine with my db being dropped and recreated as required when the model changes. However, on attempting to 'upgrade' the project to a sqlserver2008 staging server on mydiscountasp.net, my problems began in earnest. As the shared hosting databases cannot be dropped/recreated by the user, I took a local backup and restored it onto the server. One step closer.. I can see the database when I log into the server via sqlserver mgt studio and it all looks fine. However, I can't seem to figure out how to connect to the database via the web.config. I had started off using the humble ado.net sysntax for the connection string along the lines of:

<add name="MyWebSiteContext" 
  connectionString="Server=esql2k123.discountasp.net;
  Database=SQL2008R2_123456_test;uid=SQL2008R2_123456_test_user;pwd=mypassword;" 
  providerName="System.Data.SqlClient"/>

this didn't work and gave an error saying that the keyword 'server' wasn't recognised. after much googling, it would appear that the issue could well be related to the providerName="System.Data.SqlClient" part.

So on changing this to providerName="System.Data.EntityClient" I was filled with a little hope. Alas, same story.

To cut a (very) long story short, I've tried many permutations (mutations being the operative word !! :)) but can't seem to strike the correct syntax requirement for the code-first ef connectionstring requirement.

Has anyone out there gone thro a similar hoop recently and found a working solution??

fingers, toes and eyes xx'd..

[UPDATE] i've managed to get my connection string to (ALMOST) work by formatting it as follows:

<add name="GBCWebSiteContext" 
connectionString="Data Source=esql2k123.discountasp.net;Initial Catalog=SQL2008R2_123456_test;Persist Security Info=True;MultipleActiveResultSets=True;User ID=SQL2008R2_123456_test_user;Password=mypass" providerName="System.Data.SqlClient"/>

I'm now able to stop and debug the controller action as it performs a linq select. HOWEVER, on attempting to return the rows, I now receive an exception which states: A trigger returned a resultset and the server option 'disallow results from triggers' is true

So, what's my final piece of the jigsaw here, is the an additional option in the connectionstring 开发者_Go百科to negate this problem??

[UPDATE 2] Hallellulah - I've cracked the nut. As I had remmed out the global.asax line:

Database.SetInitializer(new System.Data.Entity.DropCreateDatabaseIfModelChanges<MyWebSiteContext>());

I hadn't realised that I needed to include a null reference to the Database.SetInitializer().

lo and behold, the following sorted my problem:

Database.SetInitializer<MyWebSiteContext>(null);

thank you everyone for the various suggestions, they helped me focus on the problem. So happy :)

Have you tried Data source and Initial Catalog?

sqlConnectionString="data source=myServer.myDomain.com;initial catalog=mydbName;user id=myUid;password=myPwd"

Is the database server and the webserver on the same environment? have you tried Server=localhost; instead of Server=esql2k123.discountasp.net;?

Or alternatively, can you find out which ip address the database server has from where the web server is?

So, in essence, the answer to my problem was two-fold:

1. the connectionstring needed to be along the lines of (just pasting connectionstring info, all else remains the same as above):

   connectionString="Data Source=esql2k123.discountasp.net;Initial Catalog=SQL2008R2_123456_test;Persist Security Info=True;MultipleActiveResultSets=True;User ID=SQL2008R2_123456_test_user;Password=mypass"

2. The global.asax Application_Start (or as is the case with my app, the ninject OnApplicationStarted()) needed to have a null Database.SetInitializer<>() function as remming out the existing:

Database.SetInitializer(new System.Data.Entity.DropCreateDatabaseIfModelChanges<MyWebSiteContext>());

caused an issue. Replacing this with Database.SetInitializer<MyWebSiteContext>(null);

fixed the problem wholesale.

thanks for sharing the journey and your thoughts and hopefully this will serve as a starting point for anyone else having similar issues.