Failed to open a connection to the database" While creating a Table Adapter in C#

Question

So, I'm mantaining a software an ex co-worker did. He uses an xsd to create TableAdapters for his reports and other stuff.

The thing is, I need to add a new TableAdapter and I get the aforementioned error. It also says the dread "A network-related or instance-specific error occurred while establishing a connection to SQL Server."

I am at loss here and I don't know what to do. Here are the facts:

• I have 2 connection strings, one for the production environment and one for the development environment (my computer).
• When I go to settings and then the edit window of the connection string, I hit "test connection" and it says it works fine.
• Both SQL Server 2008R2 and Visual Studio 2010 are on the same machine, my machine.
• I do not have a named instance.
• I right click on the xsd design view and click "Add -> TableAdapter" and use the local connection string (the one I already tested and worked)
• I click "Use existing stored procedures" and then hit [Next]. Then I am able to pick a stored procedure for the Select section and I can see the columns.
• I click finish, the system locks for a while and then I get the error.
• Named Pipes, TCP/IP are enabled in SQL Server configuration.

I'm kinda lost and I already wasted 2 days on this. I have tried what little I know and nothings works. I hope I can find help within your wisdom.

Sorry for the long post but I wanted to provide as much info as I could in one single shot.

Answer 1

OK, After some trial and error, I found the solution. At least, I made it work on my environment.

What @Rusland said, made me think and I searched for another Settings file. It did not exist. I decided then to erase the connection strings and the Settings file to see if the project compiled. As expected, it threw a ton of errors.

So, it was not the file.

I decided to add one by one the connection strings and test as I added them. The first one that I added, was the connection string used in development (localConStr). The configuration of the table adapter was as shown in the first image. As you can see, localConStr is selected (as has always been) only this time, having only one connection string I received an error, a different error than before. The one in the second image.

How could it be possible for the TableAdapter to ask for that Connection String if there were no refferences to it? I deleted it and the Connection property of my table adapter was set to the localConStr. So... I did what any desperate man would do: CTRL+F and find all refferences to rptTbl_loansAgingTableAdapter. My only reasoning was that somewhere in the code, the connection string was probably hardcoded by my predecessor. One thing worth mentioning: I specifically used "loansAgingTableAdapter" as the search criteria because that's the one TableAdapter I was trying to modify in the first instance.

After some minutes, I was navigating the XSD at code level (it's just an XML) and to my surprise, I found an element named Connection and there was not one but TWO!. Just to make things clear, this file has been untouched by me till today (I mean, I did not touch it by hand, any changes had been made by the IDE) The two connection strings were:

<Connection AppSettingsObjectName="Settings" AppSettingsPropertyName="localConStr"...

and

<Connection AppSettingsObjectName="Settings" AppSettingsPropertyName="loanConStr" ...

Yes, I know they look very alike, but notice they are local and loan. I decided to delete the second one (loanConStr) since it was not in my Settings file anymore (loanConStr was the original connection string left by my predecessor, localConStr was created by me so that I could switch connection strings in runtime instead of modifying the connection string and recompile every time I had to make a change and deploy again).

After that, I made sure all the DataTables in my DataSet used the same connection string (localConStr) and recompiled. I then tried the things that were giving me troubles:

• First: I created a new DataTable: the action was successful
• Second: I modified an existing DataTable: success again

Last, but not least, I tried to execute the report. I ran the app and went to the report and success! Apparently, my problem is solved.

What had happened? I am not sure, but if I had to guess, I would say that the TableAdapter was trying to use a connection string that pointed to a server that did not exist in my network (my client's server) so, every time I tried to configure it or change anything, Visual Studio was unable to contact that server and that's why I gor the connection error.

Why was the TableAdapter trying to use that connection string when the local connection string was explicitly specified? I don't know, but leavin IN THE XSD only one connection string was my solution.

As a side note, you might ask "But Compa, what will happen when the user wants to run a report that uses the localConStr?" Simple, I change the connection string at runtime when the report window is opened with the one being used by the whole app.

Sorry for the long post, and if you want a TL;DR version: Go to your xds dataset file, delete any unwanted connection strings and then go to design view and make sure all your TableAdapters use the only ConnectionString specified in your xsd