I have found lots of people asking for something similar to this but cannot find any solutions - can you help?
I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database.
Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection
string is held in the web.config file.
My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind
to change the connection string for a SQLDatatSource:
I thought I had it cracked with the following:
Public
objClientConnection As
New ConnectionStringSettings
Public
Sub Page_Init(ByVal sender
As Object,
ByVal e As System.EventArgs)
If Session("ClientDBConnectionString") <>
"" Then
objClientConnection.ConnectionString = Session("ClientDBConnectionString")
objClientConnection.Name = "ClientDBConnection"
objClientConnection.ProviderName = "System.Data.SqlClient"
Else
objClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")
End If
End Sub
The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure
there is always a design time connection string available.
The trouble is, when I try and declare the SQL data souce like this:
I get an error: The ConnectionString property has not been initialized.
I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way?
CliffMitchel...
Member
147 Points
47 Posts
Dynamic connection strings
Feb 06, 2007 02:06 PM|LINK
Dynamic connection strings
I have found lots of people asking for something similar to this but cannot find any solutions - can you help?
I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database.
Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection string is held in the web.config file.
My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind to change the connection string for a SQLDatatSource:
I thought I had it cracked with the following:
Public
objClientConnection As New ConnectionStringSettingsPublic
Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)If Session("ClientDBConnectionString") <> "" Then
objClientConnection.ConnectionString = Session("ClientDBConnectionString")
objClientConnection.Name = "ClientDBConnection"
objClientConnection.ProviderName = "System.Data.SqlClient"
Else
objClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")
End If
End Sub
The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure there is always a design time connection string available.
The trouble is, when I try and declare the SQL data souce like this:
<
asp:SqlDataSource ID="dsTest" runat="server" ConnectionString="<%# objClientConnection.ConnectionString %>" SelectCommand="usp_SEL_DocumentTypes" SelectCommandType="StoredProcedure" ></asp:SqlDataSource>I get an error: The ConnectionString property has not been initialized.
I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way?
Many thanks,
Cliff