CODEWITHSUNDEEP
c#sql-server
Compa
Compa

Reputation: 190

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

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'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.

Connection test

Services status

Protocol Status

Connection Selection when creating the TableAdapter

Stored procedure selection

Error

SQL Server Instance

Upvotes: 6

Views: 5122

Answers (2)

granadaCoder
granadaCoder

Reputation: 27874

It looks like you're seeing the database just fine. (Thus you get a list of stored procedures).

Try the rights on the procedure itself.

GRANT EXECUTE on [dbo].[rpt_loansAging] to [TheSqlAuthenticationUserNameWeCannotSeeInTheScreenShot]

APPPEND

Since you've tried everything else, try opening up a new VS project (Same version of VS and Target Framework) and try adding a TableAdapter in the same way.

If that works, then you got voodoo going on. You could delete and recreate them in the original project.

Every blue moon, something goes awry with Visual Studio, ESPECIALLY projects that started out as VS2003, were upgraded to VS2005, maybe upgraded to VS2010 (whatever combination).

I had a project that I upgraded from VS2003 to VS2005, and it built fine on my computer. On the build machine, it started failing........No idea what it was.

I ended up seeing some small anomaly in the "By Project" references. I simply removed all "By Project" references (within the .sln solution) ... and re-added the "By Project" references, and it fixed everything.

Go figure.

Upvotes: 0

Compa
Compa

Reputation: 190

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

PropertiesError

Upvotes: 5

Related Questions