Hi folks,

I am trying to dynamically change (at runtime) the ConnectionString section settings in the web.config file.

I have tried the following coding, but I get an error "The configuration is read only", which happens at the .Clear stage, and if I comment this line out, it then happens at the .Add stage.


Dim conn as New ConnectionStringSettings
            Dim connString as String
            connString = "Data Source=mySqlservername;Initial Catalog=mysqldatabasename;Trusted_Connection=True"
            conn.ConnectionString = connString
            conn.Name = "LocalSqlServer"
            conn.ProviderName = "System.Data.SqlClient"
            ConfigurationManager.ConnectionStrings.Clear()
            ConfigurationManager.ConnectionStrings.Add(conn)
        ConfigurationManager.RefreshSection("connectionStr ings")



I want to be able to allow the users to change the back-end database (SQL Server) at runtime. All the databases will be structurally identical, but they will contain different data.

Any ideas?

Kind regards

Matt

You might be better off to load the connection string into some runtime variable and use that as the source for your connections. Load it from the web.config as the default, change it if necessary based on a user choice and store it somewhere it can be used by all database connection objects.

Most of my sites use a base page class that all pages derive from for the purpose of providing access to commonly used values and utilities. That's where I'd put it personally, but you may not use that model.

-Peter
compiledthoughts.com

Hi Peter,

Thanks for the suggestion. However, my understanding is that ASP controls such as the GridView pick up their ConnectionString parameters from the web.config file e.g.

<asp:GridView ..... DataSourceID="SqldataSource1" .... />
.
.
.
<asp:SqlDataSource ID="SqldataSource1" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" .... />

If this is the case, is it possible to pass a variable to the ConnectionString parameter of the <asp:SqlDataSource..../> tag?

I know I can put something like:-

<asp:SqlDataSource ....ConnectionString="Data Source=mySQLServer;Initial Catalog=mySQLDatabase;Integrated Security=True" ..... />

but how do I put a variable into the Initial Catalog section of this parameter?

If you have any further thoughts or ideas, I would be very grateful.

Kind regards,

Matt