Anomalies in ASP.NET ConnectionStrings

I'm updating an asp.net website app that will continue to use typed datasets.  I'm using VS2013 with SQLExpress.  

As part of this work, I just completed merging the data from three databases into two:  
 using SQL Server Management Studio:
Databases = ORG, CTLS &  DATA => CTLS & DATA  (with DATA = old DATA + old ORG)

Rather than change all the connection strings references involved in the many data sets , I pointed the connection strings for ORG at the physical DATA mdf.  

The application seems to be working fine afterwards (it uses many of the tables in all three of the original DBs, so I would have failed almost immediately if it could not reach the data or the connection strings were broken).

However, there are some things I don't understand.  

1)  The first is why web.config has the "extra" connection strings shown in the code segment below.   They've been there for a long time, however, I'd like to understand why there are there.  The system fails if I remove the ones without the file paths.

<connectionStrings>
  <add name="XYZ_CTLSConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SiteFolder\App_Data\XYZ_CTLS.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
  <add name="XYZ_DATAConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SiteFolder\App_Data\XYZ_DATA.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
  <add name="XYZ_ORGConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SiteFolder\App_Data\XYZ_DATA.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
  
  <add name="XYZ_CTLSConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SiteFolder\App_Data\XYZ_CTLS.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
  <add name="XYZ_DATAConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\XYZ_DATA.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
  <add name="XYZ_ORGConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\XYZ_DATA.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
  <add name="XYZ_CTLSConnectionString2" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\XYZ_CTLS.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
  
 
</connectionStrings>

Open in new window


2) The second thing I don't understand is that when I look in VS Server Explorer I see these data connections:

XYZ_CTLSConnectionString2 (file path)
XYZ_DATA.MDF1
XYZ_ORGConnectionString1 (file path)

Open in new window


the CTLS and ORG connections open fine, and the ORG connection has all the tables I would expect from the new DATA DB.
However, when I try open or refresh the  XYZ.DATA.MDF1 connection I get this:

The attempt to attach to the database failed with the following information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start.
)

Open in new window


I'm happy that the application is working after this database conversion, however, I need to understand what's happening with the connectionstrings and straighten that out before I feel comfortable going forward.

Any help on this would be appreciated.

Thanks!
LVL 2
codequestAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Johny BravoCommented:
Remove
 <add name="XYZ_DATAConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SiteFolder\App_Data\XYZ_DATA.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
 
  <add name="XYZ_ORGConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=D:\SiteFolder\App_Data\XYZ_DATA.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
 

and check
codequestAuthor Commented:
Thanks for the input.  Your suggestion prompted me to look more closely at what was going on (probably the top benefit I get from EE :-).  
> I looked for the "extra" connection string names (with the "1" or "2" on the ends) using global search
> the were all in the dataset XML (except for the connection string declarations)
> did a text replace on them, eliminating the ending numbers
> removed the then "duplicate connection strings" in the XML
> commented them out of the web.config connections sections
>  ending up with the first three connection strings shown in the initial post, AND
>  reversed the order of them, so DATA came after ORG

App works fine, connection strings are simplified AND the server explorer only shows CTLS and DATA.
Perfecto!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
codequestAuthor Commented:
Supplemental:  When I tried johny's original suggestion, the app failed.  It looks like maybe the "extra" connection strings were added when the datasets were updated (added tables to them) after original creation.  So with the names of those connections in the data sets, they could not be removed from web.config.   Editing the names in the dataset XML seems to have eliminated the need for them in web.config.   Good learning for me, I had been reluctant to poke around in this area before.
codequestAuthor Commented:
johny_bravo1's comment was the prompt which led to my more complete procedure
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.