SQL Data Source Configuration Missing Connection Strings

[digitall]

I am attempting to build a report with the Q2 2011 reporting tools but am running into numerous issues that are really confusing me. The report itself is part of an existing web project (not in a separate library) where an existing web.config has connection strings specified. When I try to create a new SQL data source the connection strings box lists all of my connections that show up in Server Explorer, but it won't list anything in my web.config. When I let the wizard create a new connection entry (which is identical to the existing one) the report generates  until I add a table and link it to the data source (which it created). At that point it tells me it can't connect to the DB because the connection name may be incorrect. I've confirmed the connection string it created is in the web.config but it still acts like it can't find it. I know the connection strings themselves aren't an issue as I created a new project, copied from the "old" web.config to the new one and they show up in the wizard and everything functions great. The database definitely isn't going down either as it is running on my local box.

Any ideas on what might cause this? I also tried an ObjectDataSource (using my LinqToSql objects) and in the code-behind of the report set the data source of that to a linq query but it won't run it (it was setup in the constructor).

[digitall]

Anyone?

[Steve]

Hello Digitall,

You can find detailed explanation on your inquiry in the Using connectionStrings from configuration file KB article. Review it carefully and let us know if further help is needed.

All the best,
Steve
the Telerik team

[digitall]

Why would some projects work as expected (without using the settings class like the KB suggests) and other projects (specifically only this one so far) not? I guess that's where I'm most confused.

[Ivan]

Hello Digitall,

SqlDataSource Wizard searches for existing database connections in Server Explorer and in the configuration file of the current project. For example, if you have a separate class library project for your reports, the wizard searches for connection strings only in the App.config file of that project. If you have your connection string specified in the Web.config file of another project, you need to copy it to the App.config file of the report class library as well, otherwise the wizard cannot discover it. This is done for a reason: since a solution may contain many different projects, each of them with its own configuration file, it is unknown which one might contain the correct connection string for your report, so we simply pick the configuration file of the current project where the report is located. Moreover this behavior allows you to use two different databases for development and for production. For example, the database used for development and testing purposes might be a local one, while the actual production database might be situated on a dedicated database server.

Regarding the fact that connection strings for Windows applications and class libraries are stored in the Settings.settings file as well as the App.config file, while for web applications the connection strings are stored only in the Web.config file - this inconsistency is caused by Visual Studio which treats different project types differently (not to mention web sites which are not treated as projects in Visual Studio at all). For instance, you can experience exactly the same inconsistent behavior if you add a typed DataSet and choose to store the connection string in the configuration file. SqlDataSource Wizard simply uses the same design-time services that Visual Studio exposes to all designers, so we are consistent with this inconsistency as well.

Best wishes,
Ivan
the Telerik team

[digitall]

See, this is where I'm confused though. I'm not using a separate project - it's a single web application (not web site). So the report is in the same location as the web site's web.config, yet it doesn't see that connection string. When I let it create a new connection it successfully adds the "new" connection string (same value, different name) to the appropriate web.config file, yet it has the same errors when trying to use it.

I'm pretty sure I can create a new web application project as part of this overall solution just for the report, but I really would prefer to not have to do that as it seems like I'm just patching what could be a bigger overall problem.

[Ivan]

Hello Digitall,

There are no known issues related to SqlDataSource Wizard, that would prevent it from discovering the connection strings stored in the Web.config file of a web application project, and you are the first to one report such a problem. Could you, please, send us a small sample project that we can use to reproduce this locally ourselves. It would also be very helpful if you can provide us the exact error message (along with a stack trace if possible), and a more detailed explanation where and how do you receive it.

We are waiting your response before we can proceed further with your inquiry.

All the best,
Ivan
the Telerik team

[Stefan]

Was this ever resolved I am running into the same issue.  SQL Connect strings in the web.config do not show in the datasource and vice versa.  It seems to be pulling connection strings from old projects?  And when I create new ones it does not add them to the web.config or says they are not valid.  Its out of sync and have no idea where it is pulling these from.  This is a single page web page.

[Petio Petkov]

Hi Stefan,

As it was written below, the SqlDataSource Wizard searches for existing database connections in Server Explorer and in the configuration file of the current project. 

E.g. If you open the SqlDataSource wizard in the context of a Class Library project  - the newly added connection string will be saved in the current app.config file. In all other projects(even the projects in the same solution) - you should add manually the connection string in the configuration file(e.g app.config, web.config). 

Kind regards,

Petio Petkov
the Telerik team

[Stefan]

I have added them in the webconfig manually but they do not show up in the SQLDataSource.  Are you saying I have to add them through the datasource?  The connection I see is not even in my webconfig so its pulling them from somewhere else?

[Peter]

Hello Stefan,

We will need additional information in order to pinpoint the root of the unexpected behavior. Please prepare a short video that shows the steps to reproduce the unexpected behavior. For this you can use a free tool like Jing. Additionally we will appreciate if you open a support thread and send us a runnable sample. You can use a sample database such as AdventureWorks. 

Greetings,
Peter
the Telerik team