Unable to find connection MyConnection (MySettings) for object “MySettings”

As a programmer you can imagine the occasional “All nighters” and by the end of the day (actually the next morning) sometimes you feel like you’ve been through the ringer (colloquial term used as a method to dry clothes).

I was working away on a project in VS 2010 and was adding a Table Adapter when I got this friendly message…

“Unable to find connection ‘MyConn (MySettings)1’ for object ‘MySettings’. The connection string could not be found in application settings, or the data provider associated with the connection string could not be loaded. .”

I googled the problem, because every few seconds after that I kept getting “Object Reference not set to an instance of an Object” (sounds like something Yoda would say) and was stuck cold to do any troubleshooting on my own. Well, I did find others that had a similar problem but they went deep into the workings of all the behind the scenes stuff that we usually just point and click to do. Some guy had been waiting 2 weeks from Microsoft for an answer, because they could’nt figure it out. Since I’ve been developing for a long time I realize you can never depend on the creator of the language, I actuall went to the creator of a DBMS language in the 80’s and asked them directly “What do you mean that your compiler is telling my code “it’s too complex” (thank goodness I don’t write too many 5 dimentional arrays any more).

Ok, back to the story… I spent a few hours changing my SQL query thinking I had really done something that it could’nt understand (I had some CASE statements within the inline SQL statement) and that my problem was there.

After leaving it alone for a while I went back the next night to see if I could try something different. Since there is always more than one way to handle an issue I was prepared to change my plan, but, I really had a great query that would save some code time if I could just get it to work.

Well, to make a short story longer, I ended up creating a new dataset xsd file and adding that to the project. I left the other defined dataset file intact for now, I recreated the query in the new dataset and guess what? It saved just fine (no errors at all), Since this was a new project I was able to cut and paste any existing (working) table adapters I had in the original dataset to the new one. Once I made the small modificaitons in code where I referenced the original dataset to the new dataset, deleted the original dataset and everything was back to “Full Steam Ahead”.

The moral of the story is, you learn early that you can’t get frustrated when you have to spend 4-5 hours trouble-shooting a problem that has no basis for happening in the first place, when we could have done so much more in that same amount of time.

Update: Obviously I was’nt going to get off that easy. This time it happened again when I was just creating a regular dataset, I worked around this by creating a new dataset file (xsd) and created my dataset there. I couldnt copy this new datatable over to the other dataset but, its working.

Update [Probable Solution]: OK, this may be a clue. I was adding another Query to an existing dataset and I got the object referrence error, this time I noticed that whenever I double checked the connection string on the individual data adapters (just click on the name of the table adapter and look at properties) one of them was using a data connection that isnt part of the project. So double check your tableadapter connection strings to make sure there valid. I’m not sure why all of a sudden it decided to act like this, but, this might save you some time.