Dynamic connection strings

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 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:

<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

Re: Dynamic connection strings

Hi,

If your connection string is created dynamically at run time. We cannot set the connection string at design time. Binding to the property might not work since this depends on when you call DataBind on your controls.

In this case, it's better to set this in code behind event handler for Page_Load like

Me.dsTest.ConnectionString = objClientConnection.ConnectionString

Then you can call DataBind() manually.

HTH.

Re: Dynamic connection strings

Hi Kevin,

Thanks for your response. I think what you are saying is that the only way to set the connection strings dynamically at run time is to use code behind? The problem with this is that the site I am working on has a lot of interrelated, databound controls many of which are coded declaratively. I would have to recode every one of these (and there are hundreds!) in code behind and many are inside other controls (wizards, gridview, etc) and so are complicated to get at in code behind.

Given that every one of these databound controls uses:

ConnectionString=<%$ ConnectionStrings:WebTool1ConnectionString %>

I would have thought there would be a way to either:

1. dynamically change what connection string this declaration points to, or

2. change this to ConnectionString = <% some variable %> where 'some variable' is set to the required connection string in code behind.

Is this not possible?

Many thanks for you help,

Cliff

Re: Dynamic connection strings

Hi Cliff,

Could you let me see the code that you're calling DataBind on the page? Because whether this is possible depends on when data is being pulled from the server.

Thanks!

Re: Dynamic connection strings

Hi Kevin,

Sorry but I don't have permissions to upload files so will have to cut and paste the code, see below. This is a typical example of one of the pages on this site. Some are more complex with cascading DropDownLists within Panels within Wizard controls, etc.

Cliff

Source code:

<%@ Page Language="VB" MasterPageFile="~/Client.master" Trace="False" Theme="DataLabClient1"%>


<%@ Import Namespace="System.Data" %>


<%@ Import Namespace="System.Data.SqlClient" %>










<script runat="server">


Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs)


Master.Page.Title = "Datalab - Manage Reports"






End Sub






Protected Sub cbReportType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbReportType.Checked Then


pnlReportType.Visible = True


Else


pnlReportType.Visible = False


End If






End Sub


Protected Sub rblReportSource_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)


'If Not Page.IsPostBack Then

ddlReportSource.Items.Clear()


ddlReportSource.AppendDataBoundItems = True


ddlReportSource.Items.Add(New ListItem("Any", "Any"))






'End If

If rblReportSource.SelectedValue = "standard" Then


ddlReportSource.Visible = True


Else


ddlReportSource.Visible = False


End If


GridView1.DataBind()










End Sub


Protected Sub cbFirstFileDate_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbFirstFileDate.Checked Then


ddlFirstFileDate.Visible = True


Else


ddlFirstFileDate.Visible = False


End If


End Sub


Protected Sub cbProductOffer_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbProductOffer.Checked Then


pnlProductOffer.Visible = True


Else


pnlProductOffer.Visible = False


End If


End Sub


Protected Sub cbOfferType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbOfferType.Checked Then


ddlOfferType.Visible = True


Else


ddlOfferType.Visible = False


End If


End Sub


Protected Sub cbRouteType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbRouteType.Checked Then


ddlRouteType.Visible = True


Else


ddlRouteType.Visible = False


End If


End Sub


Protected Sub cbProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbProductType.Checked Then


ddlProductType.Visible = True


Else


ddlProductType.Visible = False


End If


End Sub


Protected Sub cbEligibleFor_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbEligibleFor.Checked Then


pnlEligibleFor.Visible = True


Else


pnlEligibleFor.Visible = False


End If


End Sub


Protected Sub cbHolds_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbHolds.Checked Then


pnlHolds.Visible = True


Else


pnlHolds.Visible = False


End If


End Sub






Protected Sub cbEFProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbEFProductType.Checked Then


ddlEFProductType.Visible = True


Else


ddlEFProductType.Visible = False


End If


End Sub






Protected Sub cbEFProductName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbEFProductName.Checked Then


ddlEFProductName.Visible = True


Else


ddlEFProductName.Visible = False


End If


End Sub






Protected Sub cbEFOfferType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbEFOfferType.Checked Then


ddlEFOfferType.Visible = True


Else


ddlEFOfferType.Visible = False


End If


End Sub






Protected Sub cbEFContactType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbEFContactType.Checked Then


ddlEFContactType.Visible = True


Else


ddlEFContactType.Visible = False


End If


End Sub


Protected Sub cbHProductType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbHProductType.Checked Then


ddlHProductType.Visible = True


Else


ddlHProductType.Visible = False


End If


End Sub






Protected Sub cbHProductName_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbHProductName.Checked Then


ddlHProductName.Visible = True


Else


ddlHProductName.Visible = False


End If


End Sub


Protected Sub cbPopulation_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbPopulation.Checked Then


pnlPopulation.Visible = True


Else


pnlPopulation.Visible = False


End If


End Sub






Protected Sub cbCustomerType_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbCustomerType.Checked Then


ddlCustomerType.Visible = True


Else


ddlCustomerType.Visible = False


End If


End Sub


Protected Sub cbSubPopulation_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbSubPopulation.Checked Then


pnlSubPopulation.Visible = True


Else


pnlSubPopulation.Visible = False


End If


End Sub


Protected Sub cbSelectionGroup_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbSelectionGroup.Checked Then


pnlSelectionGroup.Visible = True


Else


pnlSelectionGroup.Visible = False


End If


End Sub


Protected Sub cbTestPack_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbTestPack.Checked Then


pnlTestPack.Visible = True


Else


pnlTestPack.Visible = False


End If


End Sub


Protected Sub cbCreatedBy_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbCreatedBy.Checked Then


pnlCreatedBy.Visible = True


Else


pnlCreatedBy.Visible = False


End If


End Sub


Protected Sub cbTest_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbTest.Checked Then


pnlTest.Visible = True


Else


pnlTest.Visible = False


End If


End Sub


Protected Sub cbPack_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)


If cbPack.Checked Then


ddlPack.Visible = True


Else


ddlPack.Visible = False


End If


End Sub














Protected Sub ddlFileType_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)


ddlReportSource.Items.Clear()


ddlReportSource.AppendDataBoundItems = True


ddlReportSource.Items.Add(New ListItem("Any", "Any"))


GridView1.DataBind()






End Sub


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)


If Not Page.IsPostBack Then


'ddlReportSource.Items.Clear()


'ddlReportSource.AppendDataBoundItems = True


'ddlReportSource.Items.Add(New ListItem("Any", "Any"))





End If






End Sub










Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound


If (e.Row.RowType = DataControlRowType.DataRow) Then


Dim row As DataRowView = CType(e.Row.DataItem, DataRowView)


If (Not row Is Nothing) Then


'Dim reportType As String = LCase(Trim(CType(row("report_category"), String)))


'

Select Case LCase(Trim(rblReportSource.SelectedValue))


Case "adhoc"


' Display all command buttons





Dim convertButton As ButtonField = GridView1.Columns(3)


convertButton.Visible = True






Dim renameButton As CommandField = GridView1.Columns(4)


renameButton.Visible = True






Case "standard"


' Just display the View and Delete buttons





Dim convertButton As ButtonField = GridView1.Columns(3)


convertButton.Visible = False






Dim renameButton As CommandField = GridView1.Columns(4)


renameButton.Visible = False






Case "both"


' Display all command buttons





Dim convertButton As ButtonField = GridView1.Columns(3)


convertButton.Visible = False






Dim renameButton As CommandField = GridView1.Columns(4)


renameButton.Visible = False






Case Else


End Select


End If


End If


End Sub










Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)


' Dim row As GridViewRow = GridView1.SelectedRow


'Response.Write("process gridview row = " & row.Cells(7).Text)


'Dim DropDownList1 As New DropDownList


'Dim ddl As DropDownList = row.FindControl("DropDownList1")


'Response.Write("<br />process gridview row = " & ddl.SelectedItem.ToString)


'Response.End()













End Sub






'Protected Sub dsReportList1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles dsReportList.Deleting


' For x As Integer = 0 To e.Command.Parameters.Count - 1


'Trace.Write(e.Command.Parameters(x).ParameterName)


'Trace.Write(e.Command.Parameters(x).Value)


'Next


'End Sub









Protected Sub GridView1_RowDeleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeletedEventArgs)


If (Not IsDBNull(e.Exception)) Then


Me.lblErrorMessage.Text = e.Exception.Message


e.ExceptionHandled = True


End If


End Sub










Protected Sub dsReportList_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)


If (e.Exception IsNot Nothing) Then


Me.lblErrorMessage.Text = e.Exception.Message


e.ExceptionHandled = True


Else


Me.lblErrorMessage.Text = "Report successfully deleted."


End If


End Sub






Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)






' The GridViewCommandEventArgs class does not contain a


' property that indicates which row's command button was


' clicked. To identify which row's button was clicked, use


' the button's CommandArgument property by setting it to the


' row's index.

If e.Row.RowType = DataControlRowType.DataRow Then










' Retrieve the Button control from the first column.

Dim delButton As Button = CType(e.Row.Cells(2).Controls(0), Button)






' Set the Button's CommandArgument property with the


' row's index.

delButton.CommandArgument = e.Row.RowIndex.ToString()


delButton.Attributes.Add("onclick", "if(confirm('Are you sure you want to permanently delete this report? \nIf not, click Cancel.')){}else{return false}")


' Now repeat for the View button

Dim viewButton As Button = CType(e.Row.Cells(1).Controls(0), Button)






' Set the Button's CommandArgument property with the


' row's index.

viewButton.CommandArgument = e.Row.RowIndex.ToString()


'viewButton.Attributes.Add("onclick", "if(confirm('Are you sure you want to permanently delete this report? \nIf not, click Cancel.')){}else{return false}")


' Repeat for the Convert adhoc to standard report button

Dim convertButton As Button = CType(e.Row.Cells(3).Controls(0), Button)






' Set the Button's CommandArgument property with the


' row's index.

convertButton.CommandArgument = e.Row.RowIndex.ToString()






End If


End Sub






Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs)


If e.CommandName = "Delete_Report" Then






Me.lblErrorMessage.Text = ""


Me.lblErrorMessage.Visible = False






' Convert the row index stored in the CommandArgument


' property to an Integer.

Dim index As Integer = Convert.ToInt32(e.CommandArgument)






' Retrieve the row that contains the button clicked


' by the user from the Rows collection.

Dim row As GridViewRow = GridView1.Rows(index)


Dim lblReportName As Label = CType(row.FindControl("reportname"), Label)


Dim reportName As String = lblReportName.Text


Dim reportCategory As String = row.Cells(4).Text






' Delete the specified report

Dim clientDBConnection As New SqlConnection()


clientDBConnection.ConnectionString = Session("ClientDBConnectionString")


Try


Using reportDeleteCommand As SqlCommand = clientDBConnection.CreateCommand()


reportDeleteCommand.CommandText = "usp_DEL_Report"


reportDeleteCommand.CommandType = Data.CommandType.StoredProcedure


reportDeleteCommand.Parameters.AddWithValue("@report_name", reportName)


reportDeleteCommand.Parameters.AddWithValue("@report_category", reportCategory)


reportDeleteCommand.Connection.Open()


reportDeleteCommand.ExecuteNonQuery()


End Using


Me.lblErrorMessage.Text = "Report successfully deleted."


Me.lblErrorMessage.Visible = True


Catch ex As Exception


If (ex IsNot Nothing) Then


Me.lblErrorMessage.Text = ex.Message


Me.lblErrorMessage.Visible = True


ExceptionHandled = True






Else


Me.lblErrorMessage.Text = "Report successfully deleted."


Me.lblErrorMessage.Visible = True


End If


End Try






clientDBConnection.Dispose()


ElseIf e.CommandName = "View_Report" Then






' Convert the row index stored in the CommandArgument


' property to an Integer.

Dim index As Integer = Convert.ToInt32(e.CommandArgument)






' Retrieve the row that contains the button clicked


' by the user from the Rows collection.

Dim row As GridViewRow = GridView1.Rows(index)


Dim lblReportName As Label = CType(row.FindControl("reportname"), Label)


Dim reportName As String = lblReportName.Text


Dim lblReportCategory As Label = CType(row.FindControl("reportCategory"), Label)


Dim reportCategory As String = LCase(lblReportCategory.Text)


Response.Redirect("~/Main/Table_Manager/Reports_View.aspx?rept=" & reportName & "&cat=" & reportCategory)






ElseIf e.CommandName = "Convert_Report" Then






' Convert the row index stored in the CommandArgument


' property to an Integer.

Dim index As Integer = Convert.ToInt32(e.CommandArgument)






' Retrieve the row that contains the button clicked


' by the user from the Rows collection.





Dim row As GridViewRow = GridView1.Rows(index)






' Get the report name ...

Dim lblReportName As Label = CType(row.FindControl("reportname"), Label)


Dim reportName As String = lblReportName.Text






' ... and get the report title

Dim reportTitle As String = row.Cells(10).Text






'Response.Write("convert report!! " & reportName)


'Response.Write("<br />Reeport title = " & reportTitle)


'Response.Write("<br />DB Login = " & Session("client_DatabaseLogon"))


'Response.Write("<br /> User = " & Session("User_LongName"))


'Response.End()






' Now, convert the report from adhoc to standard





Dim clientDBConnection As New SqlConnection()


clientDBConnection.ConnectionString = Session("ClientDBConnectionString")


Try


Using reportConvertCommand As SqlCommand = clientDBConnection.CreateCommand()


reportConvertCommand.CommandText = "usp_UPD_AdHoc2Standard"


reportConvertCommand.CommandType = Data.CommandType.StoredProcedure


reportConvertCommand.Parameters.AddWithValue("@report_name", reportName)


reportConvertCommand.Parameters.AddWithValue("@report_title", reportTitle)


reportConvertCommand.Parameters.AddWithValue("@logonid ", Session("client_DatabaseLogon"))


reportConvertCommand.Parameters.AddWithValue("@user_LongName", Session("User_LongName"))






reportConvertCommand.Connection.Open()


reportConvertCommand.ExecuteNonQuery()


End Using


Me.lblErrorMessage.Text = "Report successfully converted to 'standard'."

Me.lblErrorMessage.Visible = True


Catch ex As Exception


If (ex IsNot Nothing) Then


Me.lblErrorMessage.Text = ex.Message


Me.lblErrorMessage.Visible = True


ExceptionHandled = True






Else


Me.lblErrorMessage.Text = "Report conversion status uncertain!"


Me.lblErrorMessage.Visible = True


End If


End Try






clientDBConnection.Dispose()














End If






GridView1.DataBind()


End Sub






</script>










<asp:Content ID="Content1" ContentPlaceHolderID="PageHeader" Runat="Server">


<h1 style="text-align: center">


Manage Reports</h1>


</asp:Content>


<asp:Content ID="Content2" ContentPlaceHolderID="Body" Runat="Server">


<br />


<h2>


&nbsp;</h2>


<table summary="Report filters table" frame="box">






<h1 style="text-align: left">


Report list filters</h1>






<tr>


<td colspan="2" style="width: 268px">


<asp:CheckBox ID="cbFile" runat="server" Checked="True" Enabled="False" Width="20px" Text="File" /></td>


<td colspan="2" style="width: 234px">


<asp:CheckBox ID="cbReportSource" runat="server" Checked="True" Enabled="False" Text="Report Source" /></td>


</tr>


<tr>


<td colspan="2" style="width: 268px">






<asp:Panel ID="pnlFileType" runat="server" Height="50px">


<table width="100%">


<tr>


<td >


<asp:CheckBox ID="cbFileType" runat="server" AutoPostBack="True" Text="File type" Checked="True" Enabled="False" /></td>


<td style="width: 100px; height: 55px;">


<asp:DropDownList ID="ddlFileType" runat="server" AppendDataBoundItems="True" AutoPostBack="True"


DataSourceID="dsFileType" DataTextField="file_type" DataValueField="file_type_extended" OnSelectedIndexChanged="ddlFileType_SelectedIndexChanged">


</asp:DropDownList><asp:SqlDataSource ID="dsFileType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_FileTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbFirstFileDate" runat="server" AutoPostBack="True" Text="First file date" OnCheckedChanged="cbFirstFileDate_CheckedChanged" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlFirstFileDate" runat="server" Visible="False" DataSourceID="dsFirstFileDate" DataTextField="first_file_date" DataValueField="first_file_date" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsFirstFileDate" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_FirstFileDate" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


<td colspan="2" style="width: 234px">


<asp:Panel ID="pnlReportSource" runat="server" Height="50px" Width="125px">


<table>


<tr>


<td style="width: 100px">


<asp:RadioButtonList ID="rblReportSource" runat="server" AutoPostBack="True" OnSelectedIndexChanged="rblReportSource_SelectedIndexChanged" >


<asp:ListItem Value="adhoc">Ad Hoc</asp:ListItem>


<asp:ListItem Value="standard" Selected="True">Standard</asp:ListItem>


<asp:ListItem Value="both">Both</asp:ListItem>


</asp:RadioButtonList></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlReportSource" runat="server" DataSourceID="dsStandardReports" DataTextField="standard_name" DataValueField="standard_name" AppendDataBoundItems="True" AutoPostBack="True" >


<asp:ListItem Selected="True">Any</asp:ListItem>


</asp:DropDownList><asp:SqlDataSource ID="dsStandardReports" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_StandardReports" SelectCommandType="StoredProcedure">


<SelectParameters>


<asp:ControlParameter ControlID="cbReportType" DefaultValue="False" Name="report_type_checked"


PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="ddlReportType" DefaultValue="Null" Name="report_type"


PropertyName="SelectedValue" Type="String" />


<asp:ControlParameter ControlID="cbFileType" DefaultValue="False" Name="file_type_checked"


PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="ddlFileType" DefaultValue="Null" Name="file_type"


PropertyName="SelectedItem.Text" Type="String" />


<asp:Parameter DefaultValue="False" Name="route_type_checked" Type="Boolean" />


<asp:Parameter Name="route_type" Type="String" DefaultValue="Null" />


<asp:Parameter DefaultValue="False" Name="product_type_checked" Type="Boolean" />


<asp:Parameter Name="product_type" Type="String" DefaultValue="Null" />


<asp:Parameter DefaultValue="False" Name="offer_type_checked" Type="Boolean" />


<asp:Parameter Name="offer_type" Type="String" DefaultValue="Null" />


</SelectParameters>


</asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


<tr>


<td colspan="2" style="width: 268px">


<br />


<asp:CheckBox ID="cbReportType" runat="server" OnCheckedChanged="cbReportType_CheckedChanged" AutoPostBack="True" Text="Report type" /><asp:Panel ID="pnlReportType" runat="server" Height="50px" Width="125px" Visible="False">


<asp:DropDownList ID="ddlReportType" runat="server" DataSourceID="dsReportType" DataTextField="report_type"


DataValueField="report_type" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsReportType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ReportTypes4FileType" SelectCommandType="StoredProcedure">


<SelectParameters>


<asp:ControlParameter ControlID="ddlFileType" Name="file_type" PropertyName="SelectedItem.Text"


Type="String" DefaultValue="Any" />


</SelectParameters>


</asp:SqlDataSource>


</asp:Panel>


</td>


<td colspan="2" style="width: 234px">


<asp:CheckBox ID="cbProductOffer" runat="server" AutoPostBack="True" OnCheckedChanged="cbProductOffer_CheckedChanged" Text="Product offer" /><br />


<asp:Panel ID="pnlProductOffer" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbOfferType" runat="server" AutoPostBack="True" OnCheckedChanged="cbOfferType_CheckedChanged" Text="Offer type" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlOfferType" runat="server" Visible="False" DataSourceID="dsOfferTypes" DataTextField="offer_type" DataValueField="offer_type_short" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsOfferTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_OfferTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbRouteType" runat="server" OnCheckedChanged="cbRouteType_CheckedChanged"


Text="Route type" AutoPostBack="True" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlRouteType" runat="server" Visible="False" DataSourceID="dsRouteTypes" DataTextField="route_type" DataValueField="route_type_short" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsRouteTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_RouteTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbProductType" runat="server" Text="Product type" OnCheckedChanged="cbProductType_CheckedChanged" AutoPostBack="True" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlProductType" runat="server" Visible="False" DataSourceID="dsProductTypes"


DataTextField="product_type" DataValueField="product_type_short" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsProductTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


<tr>


<td colspan="2" style="width: 268px">


<asp:CheckBox ID="cbEligibleFor" runat="server" OnCheckedChanged="cbEligibleFor_CheckedChanged"


Text="Eligible for" AutoPostBack="True" /><br />


<asp:Panel ID="pnlEligibleFor" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px; height: 29px;">


<asp:CheckBox ID="cbEFProductType" runat="server" AutoPostBack="True" Text="Product type" OnCheckedChanged="cbEFProductType_CheckedChanged" /></td>


<td style="width: 100px; height: 29px;">


<asp:DropDownList ID="ddlEFProductType" runat="server" Visible="False" DataSourceID="dsEFProductTypes" DataTextField="product_type" DataValueField="product_type_short" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsEFProductTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbEFProductName" runat="server" AutoPostBack="True" Text="Product name" OnCheckedChanged="cbEFProductName_CheckedChanged" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlEFProductName" runat="server" Visible="False" DataSourceID="dsEPProductNames" DataTextField="product_name" DataValueField="product_name" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsEPProductNames" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="up_SEL_ProductNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbEFOfferType" runat="server" AutoPostBack="True" Text="Offer type" OnCheckedChanged="cbEFOfferType_CheckedChanged" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlEFOfferType" runat="server" Visible="False" DataSourceID="dsEFOfferType" DataTextField="offer_type" DataValueField="offer_type_short" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsEFOfferType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_OfferTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px; height: 29px">


<asp:CheckBox ID="cbEFContactType" runat="server" AutoPostBack="True" Text="Contact type" OnCheckedChanged="cbEFContactType_CheckedChanged" /></td>


<td style="width: 100px; height: 29px">


<asp:DropDownList ID="ddlEFContactType" runat="server" Visible="False" DataSourceID="dsEFContactTypes" DataTextField="contact_type" DataValueField="contact_type" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsEFContactTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ContactTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


<td colspan="2" style="width: 234px">


<asp:CheckBox ID="cbHolds" runat="server" Text="Holds" OnCheckedChanged="cbHolds_CheckedChanged" AutoPostBack="True" /><br />


<asp:Panel ID="pnlHolds" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbHProductType" runat="server" AutoPostBack="True" Text="Product type" OnCheckedChanged="cbHProductType_CheckedChanged" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlHProductType" runat="server" Visible="False" DataSourceID="dsHProductType" DataTextField="product_type" DataValueField="product_type" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsHProductType" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ProductTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbHProductName" runat="server" AutoPostBack="True" Text="Product name" OnCheckedChanged="cbHProductName_CheckedChanged" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlHProductName" runat="server" Visible="False" DataSourceID="dsHProductName" DataTextField="product_name" DataValueField="product_name" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsHProductName" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ProductNames" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


<tr>


<td colspan="4">


<asp:CheckBox ID="cbPopulation" runat="server" AutoPostBack="True"


Text="Population" OnCheckedChanged="cbPopulation_CheckedChanged" />


<asp:Panel ID="pnlPopulation" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbCustomerType" runat="server" OnCheckedChanged="cbCustomerType_CheckedChanged" AutoPostBack="True" Text="Customer type" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlCustomerType" runat="server" DataSourceID="dsPopCustomerTypes" DataTextField="customer_type" DataValueField="customer_type" Visible="False" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsPopCustomerTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_CustomerTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbSubPopulation" runat="server" OnCheckedChanged="cbSubPopulation_CheckedChanged" AutoPostBack="True" Text="Sub-population" /></td>


<td style="width: 100px">


<asp:Panel ID="pnlSubPopulation" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


Sub&#8209population&nbsp;field&nbsp;filter</td>


<td style="width: 100px">


<asp:DropDownList ID="ddlsubPopulationFieldFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationsSubPopulationfields" DataTextField="field_description" DataValueField="field_name" AutoPostBack="True">


<asp:ListItem Selected="True">Not specified</asp:ListItem>


</asp:DropDownList><asp:SqlDataSource ID="dsPopulationsSubPopulationfields" runat="server"


ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulationFields"


SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


Sub-population</td>


<td style="width: 100px">


<asp:DropDownList ID="ddlSubPopulation" runat="server" DataSourceID="dsPopulationsSubPopulation" DataTextField="subpopulation_name" DataValueField="subpopulation_name" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsPopulationsSubPopulation" runat="server"


ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulations"


SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbSelectionGroup" runat="server" OnCheckedChanged="cbSelectionGroup_CheckedChanged" AutoPostBack="True" Text="Selection group" /></td>


<td style="width: 100px">


<asp:Panel ID="pnlSelectionGroup" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


Selection&nbsp;group&nbsp;field&nbsp;filter</td>


<td style="width: 100px">


<asp:DropDownList ID="ddlSelectionGroupFieldFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationSelectionGroupsubPopulation" DataTextField="field_description" DataValueField="field_name" AutoPostBack="True">


<asp:ListItem Selected="True">Not specified</asp:ListItem>


</asp:DropDownList><asp:SqlDataSource ID="dsPopulationSelectionGroupsubPopulation"


runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_SelectionGroups" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


Selection&nbsp;group&nbsp;sub&#8209population&nbsp;filter</td>


<td style="width: 100px">


<asp:DropDownList ID="ddlSelectionGroupsubPopulationFilter" runat="server" AppendDataBoundItems="True" DataSourceID="dsPopulationSubPopulationNames" DataTextField="subpopulation_name" DataValueField="subpopulation_name" CausesValidation="True">


<asp:ListItem Selected="True">Not specified</asp:ListItem>


</asp:DropDownList><asp:SqlDataSource ID="dsPopulationSubPopulationNames" runat="server"


ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>" SelectCommand="usp_SEL_SubPopulations"


SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px; height: 10px">


Selection&nbsp;group</td>


<td style="width: 100px; height: 10px">


<asp:DropDownList ID="ddlSelectionGroup" runat="server" DataSourceID="dsPopulationSelectionGroupSelectionGroup" DataTextField="selection_group" DataValueField="selection_group" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsPopulationSelectionGroupSelectionGroup"


runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_SelectionGroupsList" SelectCommandType="StoredProcedure">


</asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


<tr>


<td colspan="2" style="height: 16px; width: 268px;">


<asp:CheckBox ID="cbTestPack" runat="server" AutoPostBack="True" OnCheckedChanged="cbTestPack_CheckedChanged" Text="Test/Pack" />


<asp:Panel ID="pnlTestPack" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbTest" runat="server" AutoPostBack="True" OnCheckedChanged="cbTest_CheckedChanged" Text="Test" /></td>


<td style="width: 100px">


<asp:Panel ID="pnlTest" runat="server" Height="50px" Visible="False">


<table>


<tr>


<td style="width: 100px">


Type</td>


<td style="width: 100px">


<asp:DropDownList ID="ddlType" runat="server" AppendDataBoundItems="True" DataSourceID="dsTestTypes" DataTextField="test_type" DataValueField="test_type" AutoPostBack="True">


<asp:ListItem Selected="True" Value="Not specified">Any</asp:ListItem>


</asp:DropDownList><asp:SqlDataSource ID="dsTestTypes" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_TestTypes" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


<tr>


<td style="width: 100px">


Name</td>


<td style="width: 100px">


<asp:DropDownList ID="ddlName" runat="server" DataSourceID="dsTestPackTestName" DataTextField="test_name" DataValueField="test_name" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsTestPackTestName" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_TestName" SelectCommandType="StoredProcedure">


<SelectParameters>


<asp:Parameter DefaultValue="N" Name="test_order" Type="String" />


</SelectParameters>


</asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


</tr>


<tr>


<td style="width: 100px">


<asp:CheckBox ID="cbPack" runat="server" AutoPostBack="True" OnCheckedChanged="cbPack_CheckedChanged"


Text="Pack" /></td>


<td style="width: 100px">


<asp:DropDownList ID="ddlPack" runat="server" Visible="False" DataSourceID="dsTestPackPacks" DataTextField="pack_description" DataValueField="pack_code" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsTestPackPacks" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_Packs" SelectCommandType="StoredProcedure"></asp:SqlDataSource>


</td>


</tr>


</table>


</asp:Panel>


</td>


<td colspan="2" style="width: 234px; height: 16px">


<asp:CheckBox ID="cbCreatedBy" runat="server" AutoPostBack="True" OnCheckedChanged="cbCreatedBy_CheckedChanged"


Text="Created by" />


<asp:Panel ID="pnlCreatedBy" runat="server" Height="50px" Visible="False">


<asp:DropDownList ID="ddlCreatedBy" runat="server" DataSourceID="dsUsers4Client" DataTextField="UserName" DataValueField="UserName" AutoPostBack="True">


</asp:DropDownList><asp:SqlDataSource ID="dsUsers4Client" runat="server" ConnectionString="<%$ ConnectionStrings:webtoolUserConnectionString %>"


SelectCommand="usp_SEL_Users4Client_Unique" SelectCommandType="StoredProcedure">


<SelectParameters>


<asp:SessionParameter Name="pkClient_ID" SessionField="pkClient_ID" Type="Int32" />


</SelectParameters>


</asp:SqlDataSource>


</asp:Panel>


</td>


</tr>


</table>


<br />


<asp:Label ID="lblErrorMessage" runat="server" CssClass="warning" ></asp:Label><br />


<asp:GridView ID="GridView1" runat="server" EmptyDataText="No reports were found matching the above criteria."


DataSourceID="dsReportList" AllowSorting="True" AutoGenerateColumns="False"


onrowcreated="GridView1_RowCreated"


OnSelectedIndexChanged="GridView1_SelectedIndexChanged"


DataKeyNames="report_name" OnRowCommand="GridView1_RowCommand">


<Columns>


<asp:TemplateField ShowHeader="False" Visible=False>


<ItemTemplate>


<asp:Label ID="reportname" Runat="Server"


Text='<%# Eval("report_name") %>' Visible="false" />

<asp:Label ID="reportCategory" Runat="Server"


Text='<%# Eval("report_category") %>' Visible="false" />

</ItemTemplate>


<HeaderStyle BackColor="White" />


</asp:TemplateField>


<asp:buttonfield commandname="View_Report" ButtonType="Button" text="View" >


<ControlStyle CssClass="button" />


<HeaderStyle BackColor="White" />


</asp:buttonfield>


<asp:buttonfield commandname="Delete_Report" ButtonType="Button" text="Delete" >


<ControlStyle CssClass="button" />


<HeaderStyle BackColor="White" />


</asp:buttonfield>


<asp:buttonfield commandname="Convert_Report" ButtonType="Button" text="Convert" >


<ControlStyle CssClass="button" />


<HeaderStyle BackColor="White" />


</asp:buttonfield>


<asp:CommandField ButtonType="Button" ShowEditButton="True" >


<HeaderStyle BackColor="White" />


<ControlStyle CssClass="button" />


</asp:CommandField>


<asp:BoundField DataField="report_category" HeaderText="Cat." InsertVisible="False" ReadOnly="True" SortExpression="report_category" />


<asp:BoundField DataField="offer_type" HeaderText="Offer type" InsertVisible="False" ReadOnly="True" SortExpression="offer_type" />


<asp:BoundField DataField="route_type" HeaderText="Route type" InsertVisible="False" ReadOnly="True" SortExpression="route_type" />


<asp:BoundField DataField="product_type" HeaderText="Product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type" />


<asp:BoundField DataField="first_file_date" HeaderText="First file date" InsertVisible="False" ReadOnly="True" SortExpression="first_file_date" />


<asp:BoundField DataField="report_title" HeaderText="Report title" InsertVisible="False" SortExpression="report_title" />


<asp:BoundField DataField="start_date_formatted" HeaderText="Creation date" InsertVisible="False" ReadOnly="True" SortExpression="start_date_formatted" />


<asp:BoundField DataField="report_type" HeaderText="Report type" InsertVisible="False" ReadOnly="True" SortExpression="report_type" />


<asp:BoundField DataField="number_of_files" HeaderText="# files" InsertVisible="False" ReadOnly="True" SortExpression="number_of_files" />


<asp:BoundField DataField="customer_type" HeaderText="Population - Customer type" InsertVisible="False" ReadOnly="True" SortExpression="customer_type" />


<asp:BoundField DataField="subpopulation_name" HeaderText="Population - sub-population" InsertVisible="False" ReadOnly="True" SortExpression="subpopulation_name" />


<asp:BoundField DataField="selection_group" HeaderText="Population - Selection group" InsertVisible="False" ReadOnly="True" SortExpression="selection_group" />


<asp:BoundField DataField="product_type_eligibility" HeaderText="Eligibility - product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type_eligibility" />


<asp:BoundField DataField="product_name_eligibility" HeaderText="Eligibility - product name" InsertVisible="False" ReadOnly="True" SortExpression="product_name_eligibility" />


<asp:BoundField DataField="offer_type_eligibility" HeaderText="Eligibility - offer type" InsertVisible="False" ReadOnly="True" SortExpression="offer_type_eligibility" />


<asp:BoundField DataField="contact_type_eligibility" HeaderText="Eligibility - contact type" InsertVisible="False" ReadOnly="True" SortExpression="contact_type_eligibility" />


<asp:BoundField DataField="product_type_holding" HeaderText="Holds - product type" InsertVisible="False" ReadOnly="True" SortExpression="product_type_holding" />


<asp:BoundField DataField="product_name_holding" HeaderText="Holds - product name" InsertVisible="False" ReadOnly="True" SortExpression="product_name_holding" />


<asp:BoundField DataField="promotion_code" HeaderText="Promotion code" InsertVisible="False" ReadOnly="True" SortExpression="promotion_code" />


<asp:BoundField DataField="userlongname" HeaderText="Created by" InsertVisible="False" ReadOnly="True" SortExpression="userlongname" />


</Columns>


</asp:GridView>


<asp:SqlDataSource ID="dsReportList" runat="server" ConnectionString="<%$ ConnectionStrings:WebTool1ConnectionString %>"


SelectCommand="usp_SEL_ReportList" SelectCommandType="StoredProcedure"


UpdateCommand="usp_UPD_AdHocReportTitle" UpdateCommandType="StoredProcedure" >


<SelectParameters>


<asp:ControlParameter ControlID="rblReportSource" Name="radioReportSource" PropertyName="SelectedValue" Type="String" />


<asp:ControlParameter ControlID="ddlReportSource" Name="reportSource" PropertyName="SelectedValue" Type="String" />


<asp:ControlParameter ControlID="cbProductType" Name="productTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbRouteType" Name="routeTypechecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbOfferType" Name="offerTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbReportType" Name="reportTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbPack" Name="packChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbFirstFileDate" Name="firstFileDateChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbCreatedBy" Name="createdByChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbEFProductType" Name="eFProductTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbEFProductName" Name="eFProductChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbEFOfferType" Name="eFOfferTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbEFContactType" Name="eFContactTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbHProductType" Name="hProductTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbHProductName" Name="hProductNameChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbCustomerType" Name="customerTypeChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbSubPopulation" Name="subPopulationChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbSelectionGroup" Name="selectionGroupChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="cbTest" Name="testChecked" PropertyName="Checked" Type="Boolean" />


<asp:ControlParameter ControlID="ddlProductType" Name="productType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlRouteType" Name="routeType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlOfferType" Name="offerType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlReportType" Name="reportType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlPack" Name="packCode" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlFirstFileDate" Name="firstFileDate" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlCreatedBy" Name="createdBy" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlEFProductType" Name="eFProductType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlEFProductName" Name="eFProductName" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlEFOfferType" Name="eFOfferType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlEFContactType" Name="eFContactType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlHProductType" Name="hProductType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlHProductName" Name="hProductName" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlCustomerType" Name="customerType" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlSubPopulation" Name="subPopulation" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlSelectionGroup" Name="SelectionGroup" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlFileType" Name="fileType" PropertyName="SelectedItem.Text" DefaultValue=Null Type="String" />


<asp:ControlParameter ControlID="ddlName" Name="testName" PropertyName="SelectedValue" DefaultValue=Null Type="String" />


</SelectParameters>


<UpdateParameters>


<asp:Parameter Name="report_name" Type="String" />


<asp:Parameter Name="report_title" Type="String" />


</UpdateParameters>


</asp:SqlDataSource>


&nbsp;<br />


<br />


</asp:Content>

Re: Dynamic connection strings

Hi,

I've got a solution for you, but hold onto your hat Cliff, there's a fair bit to it!!! OK... let's go for it....

The real issue is that when you use markup, you can only "bind" the ConnectionString and ProviderName properties using expression builders (i.e. with <%$ notation)... which limits you to Resources, AppSettings and ConnectionStrings. Fortunately, this is our saviour, as we can create our own ExpressionBuilder classes. What's more, with the power of Reflector, we can see what the existing ExpressionBuilder classes do, copy them and make our own modifications! So here's the plan...

We create a new ExpressionBuilder class, called SessionConnectionStringsExpressionBuilder
We tell ASP.NET about the new expression builder class in web.config
We replace all normal <%$ ConnectionStrings:ConnectionString %> and <%$ ConnectionStrings:ConnectionString.ProviderName %> markup with our own expression builder calls <%$ SessionConnectionStrings:ConnectionString %> and <%$ SessionConnectionStrings:ConnectionString.ProviderName %>

Sounds simple in theory, and in practise it's pretty straightforward too... there's just a fair bit of code to paste in here!! But before I do that, we need to discuss the SessionConnectionStringsExpressionBuilder class. Here's how I designed things:

I create a variable in the user's Session to store all our ConnectionStringSettings of type ConnectionStringSettingsCollection. The advantages of doing this are a) that it means we only use up 1 session variable and b) it can be easily substituted for ConfigurationManager.ConnectionStrings
I define a default key by which to store this session variable ("SESSION_STATE_CONNECTION_STRINGS"), but this can be overridden in the appSettings in the web.config file if you happen to be using that already for something else
I expose methods to Add and Remove connection string settings from the Session making it simple for you to code things
When you add connection string settings to the session, there must be a setting of the same name in the web.config connectionStrings section.
My reasoning for this last point is that the fallback position is to use a standard connection string if it doesn't exist in session... we can only adopt this fallback position if the connectionString exists!!

So enough rambling and on with the instructions... take a deep breath and follow!!

SessionConnectionStringsExpressionBuilder class

Create a new class in your App_Code directory and paste this code in:

Imports System.Web
Imports System.Web.Compilation
Imports System.CodeDom
Imports System.ComponentModel
Imports System.Resources
Imports System.Threading
Imports System.Globalization
Imports System.Security.Permissions


<ExpressionPrefix("SessionConnectionStrings")> _
<ExpressionEditor("System.Web.UI.Design.ConnectionStringsExpressionEditor, System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"), AspNetHostingPermission(SecurityAction.LinkDemand, Level:=AspNetHostingPermissionLevel.Minimal), AspNetHostingPermission(SecurityAction.InheritanceDemand, Level:=AspNetHostingPermissionLevel.Minimal)> _
Public Class SessionConnectionStringsExpressionBuilder
    Inherits ExpressionBuilder


#Region "Session Handling"
    ' we will store alternative connection strings in session state in a collection under a single key
    ' this is the default key we'll use
    ' otherwise use ConnectionStringSessionKey in AppSettings
    Private Const DEFAULT_SESSION_KEY As String = "SESSION_STATE_CONNECTION_STRINGS"


    ' helper function to pull out the session key to use
    Private Shared Function GetSessionKey() As String
        ' see if the user has overridden the session key
        Dim SessionKey As String = ConfigurationManager.AppSettings.Get("ConnectionStringSessionKey")
        If String.IsNullOrEmpty(SessionKey) Then
            ' no so use the default
            SessionKey = DEFAULT_SESSION_KEY
        End If
        Return SessionKey
    End Function
#End Region


#Region "AddConnectionStrings To Session"
    Public Shared Sub AddConnectionStringSettings(ByVal settings As ConnectionStringSettings)
        ' see if we've got these connection string settings in the config file
        Dim cs As ConnectionStringSettings = ConfigurationManager.ConnectionStrings.Item(settings.Name)
        If cs Is Nothing Then
            ' no, so we've got no back up when the session times out... so throw an exception
            Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {settings.Name}))
        End If
        ' everything's OK, so add in the settings!
        GetConnectionStringSettings().Add(settings)
    End Sub
    Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String)
        AddConnectionStringSettings(New ConnectionStringSettings(name, connectionString))
    End Sub
    Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String, ByVal providerName As String)
        AddConnectionStringSettings(New ConnectionStringSettings(name, connectionString, providerName))
    End Sub
    Public Shared Sub RemoveConnectionStringSettings(ByVal settings As ConnectionStringSettings)
        GetConnectionStringSettings.Remove(settings)
    End Sub
    Public Shared Sub RemoveConnectionStringSettings(ByVal name As String)
        GetConnectionStringSettings.Remove(name)
    End Sub
#End Region


#Region "ConnectionStrings Helper Functions"


    Private Shared Function GetConnectionStringSettings() As ConnectionStringSettingsCollection
        Dim session As HttpSessionState = HttpContext.Current.Session
        Dim sessionKey As String = GetSessionKey()
        Dim connectionStrings As ConnectionStringSettingsCollection = session.Item(sessionKey)
        If connectionStrings Is Nothing Then
            connectionStrings = New ConnectionStringSettingsCollection()
            session.Item(sessionKey) = connectionStrings
        End If


        Return connectionStrings


    End Function


    Private Shared Function GetConnectionStringSettings(ByVal connectionStringName As String) As ConnectionStringSettings


        Dim css As ConnectionStringSettingsCollection = GetConnectionStringSettings()
        Dim cs As ConnectionStringSettings = css.Item(connectionStringName)
        If cs Is Nothing Then
            cs = ConfigurationManager.ConnectionStrings.Item(connectionStringName)
        End If


        Return cs
    End Function


    ' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection!
    Public Shared Function GetConnectionString(ByVal connectionStringName As String) As String
        Dim settings1 As ConnectionStringSettings = GetConnectionStringSettings(connectionStringName)
        If (settings1 Is Nothing) Then
            Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {connectionStringName}))
        End If
        Return settings1.ConnectionString
    End Function


    Public Shared Function GetConnectionStringProviderName(ByVal connectionStringName As String) As String
        Dim settings1 As ConnectionStringSettings = GetConnectionStringSettings(connectionStringName)
        If (settings1 Is Nothing) Then
            Throw New InvalidOperationException(SR.GetString("Connection_string_not_found", New Object() {connectionStringName}))
        End If
        Return settings1.ProviderName
    End Function


#End Region


#Region "Main ConnectionStringsExpressionBuilder Code"
    ' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection!
    Public Overrides Function GetCodeExpression(ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As System.CodeDom.CodeExpression
        Dim pair1 As Pair = DirectCast(parsedData, Pair)
        Dim text1 As String = CStr(pair1.First)
        If CBool(pair1.Second) Then
            Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionString", New CodeExpression() {New CodePrimitiveExpression(text1)})
        End If
        Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionStringProviderName", New CodeExpression() {New CodePrimitiveExpression(text1)})
    End Function
    Public Overrides Function EvaluateExpression(ByVal target As Object, ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As Object
        Dim pair1 As Pair = DirectCast(parsedData, Pair)
        Dim text1 As String = CStr(pair1.First)
        Dim flag1 As Boolean = CBool(pair1.Second)
        Dim settings1 As ConnectionStringSettings = SessionConnectionStringsExpressionBuilder.GetConnectionStringSettings(text1)
        If flag1 Then
            Return SessionConnectionStringsExpressionBuilder.GetConnectionString(text1)
        End If
        Return SessionConnectionStringsExpressionBuilder.GetConnectionStringProviderName(text1)
    End Function


#Region "Simple Copies From ConnectionStringExpressionBuilder Class"
    Public Overrides ReadOnly Property SupportsEvaluate() As Boolean
        Get
            Return True
        End Get
    End Property


    Public Overrides Function ParseExpression(ByVal expression As String, ByVal propertyType As Type, ByVal context As ExpressionBuilderContext) As Object
        Dim text1 As String = String.Empty
        Dim flag1 As Boolean = True
        If (Not expression Is Nothing) Then
            If expression.EndsWith(".connectionstring", StringComparison.OrdinalIgnoreCase) Then
                text1 = expression.Substring(0, (expression.Length - ".connectionstring".Length))
            ElseIf expression.EndsWith(".providername", StringComparison.OrdinalIgnoreCase) Then
                flag1 = False
                text1 = expression.Substring(0, (expression.Length - ".providername".Length))
            Else
                text1 = expression
            End If
        End If
        Return New Pair(text1, flag1)
    End Function
#End Region


#End Region


End Class




#Region "Resource Helper Class"
' cut down version of System.Web.SR
' used to pull culture sensitive resources out of System.Web
' simply used Reflector to pull out what is required!
Friend NotInheritable Class SR


    Private Shared loader As SR
    Private Shared s_InternalSyncObject As Object
    Private SRresources As ResourceManager


    Public Shared ReadOnly Property Resources() As ResourceManager
        Get
            Return SR.GetLoader.SRresources
        End Get
    End Property


    Private Shared ReadOnly Property InternalSyncObject() As Object
        Get
            If (SR.s_InternalSyncObject Is Nothing) Then
                Dim obj1 As New Object
                Interlocked.CompareExchange(SR.s_InternalSyncObject, obj1, Nothing)
            End If
            Return SR.s_InternalSyncObject
        End Get
    End Property


    Private Shared ReadOnly Property Culture() As CultureInfo
        Get
            Return Nothing
        End Get
    End Property


    Friend Sub New()
        Me.SRresources = New ResourceManager("System.Web", MyBase.GetType.Assembly)
    End Sub


    Public Shared Function GetString(ByVal name As String) As String
        Dim sr1 As SR = SR.GetLoader
        If (sr1 Is Nothing) Then
            Return Nothing
        End If
        Return sr1.SRresources.GetString(name, SR.Culture)
    End Function


    Public Shared Function GetString(ByVal name As String, ByVal ParamArray args As Object()) As String
        Dim sr1 As SR = SR.GetLoader
        If (sr1 Is Nothing) Then
            Return Nothing
        End If
        Dim text1 As String = sr1.SRresources.GetString(name, SR.Culture)
        If ((args Is Nothing) OrElse (args.Length <= 0)) Then
            Return text1
        End If
        Dim num1 As Integer = 0
        Do While (num1 < args.Length)
            Dim text2 As String = TryCast(args(num1), String)
            If ((Not text2 Is Nothing) AndAlso (text2.Length > 1024)) Then
                args(num1) = (text2.Substring(0, 1021) & "...")
            End If
            num1 += 1
        Loop
        Return String.Format(CultureInfo.CurrentCulture, text1, args)
    End Function


    Public Shared Function GetObject(ByVal name As String) As Object
        Dim sr1 As SR = SR.GetLoader
        If (sr1 Is Nothing) Then
            Return Nothing
        End If
        Return sr1.SRresources.GetObject(name, SR.Culture)
    End Function


    Private Shared Function GetLoader() As SR
        If (SR.loader Is Nothing) Then
            SyncLock SR.InternalSyncObject
                If (SR.loader Is Nothing) Then
                    SR.loader = New SR
                End If
            End SyncLock
        End If
        Return SR.loader
    End Function


End Class
#End Region

Set Up The web.config

We now need to get ASP.NET to recognise our new expression builder... this is done by adding these lines in under the <system.web><compilation> section:

The expressionPrefix is what we will use in our markup.

Change Our Markup

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
  SelectCommand="SELECT * FROM Authors"  
  ProviderName="<%$ ConnectionStrings:MyConnectionString.ProviderName %>" />

becomes

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
  ConnectionString="<%$ SessionConnectionStrings:MyConnectionString %>"
  SelectCommand="SELECT * FROM Authors"
  ProviderName="<%$ SessionConnectionStrings:MyConnectionString.ProviderName %>" />

Setting The Session Connection String

Now all you should need to do is set the session connection string. I've added some shared methods to the class to assist with that:

Public Shared Sub AddConnectionStringSettings(ByVal settings As ConnectionStringSettings)
Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String)
Public Shared Sub AddConnectionStringSettings(ByVal name As String, ByVal connectionString As String, ByVal providerName As String)

So you might call:

SessionConnectionStringsExpressionBuilder.AddConnectionStringSettings("MyConnectionString", "xxx Connection Details xxx"", "System.Data.SQLClient")

Caveat

There's one minor caveat here... if you try and add the session connection string on Page_Init (etc)... the page has already evaluated the ExpressionBuilder for your SqlDataSource. So you will have to be a little bit careful about your coding logic, and might have to force a page reload.

Good luck with this, let me know how you get on and if it's suitable for your situation/any problems you've got!

Cheers,

James

Re: Dynamic connection strings

WOW!

James, many, many thanks.

This looks like a great solution. You will have to allow me some time to understand it more fully and try and implement it - hopefully over the weekend - although I am already doing client work all weekend so I may struggle ;-)

I will get to it ASAP and let you know how I get on. A couple of people from other forums have asked me to keep them posted on possible solutions so you may have made several people happy in one go!

Thanks again and have a good weekend.

Cliff

Re: Dynamic connection strings

No worries Cliff... the best way to understand what I've done is probably to use Reflector on the original ConnectionStringsExpressionBuilder class and look at the differences between that and what I've done... to be honest there aren't too many changes, just a conditional substitution between the ConfigurationManager and the Session!

Cheers,

James

Re: Dynamic connection strings

Hi James,

I'm getting a compliation error on the new ConnectionStringsExrpessionBuilder class that I don't understand:

Error 102 Type 'CodeBLOCKED' is not defined. C:\Documents and Settings\Cliff\My Documents\My Webs\DataLabDotNet\App_Code\SessionConnectionStringsExpressionBuilder.vb 111 127 C:\...\DataLabDotNet\

Any suggestions?

Thanks,

Cliff

Re: Dynamic connection strings

Hmmm... I'm a bit confused with that too!!

Just googled CodeBLOCKED and it only seems to come up with a couple of hits... relating to Beta 2!

Will have a look later for you... but in the meantime, can you confirm your environment for me please?

I'm also contemplating putting together a SessionAppSettingsExpresssionBuilder and a SessionResourcesExpressionBuilder to finish this off... this will probably be a few days, and I need to find somewhere to upload the code to!

Thanks,

James

Re: Dynamic connection strings

Hi James,

Thanks for the prompt response. I think I have it working!

I changed the CodeBlocked to CodeExpression and it seems to work:

If CBool(pair1.Second) Then
Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionString", New CodeExpression() {New CodePrimitiveExpression(text1)})
End If
Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionStringProviderName", New CodeExpression() {New CodePrimitiveExpression(text1)})

I don't know the significance of CodeBlocked so I may have screwed something up! However, I have successfully changed a connecting string and back again on a test page. I am getting an error sometimes pointing to line 44: GetConnectionStringSettings().Add(settings)

"The entry 'WebTool1ConnectionString' has already been added"

However, I suspect this is because I am doing this in Page_PreInit on my test page (and noting your caveat!) I will test more fully in the way I actually want to use it (setting the connection string once for the entire session when the user logs in). I'll let you know how I get on.

I am using VisualStudio 2005 with ASP.Net 2 and using VB.Net, SQL Server 2005.

Thanks again,

Cliff

Re: Dynamic connection strings

Sorry James, I meant to include my amended version of the CodeBlocked lines:

If CBool(pair1.Second) Then

Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionString", New CodeExpression() {New CodePrimitiveExpression(text1)})

End If

Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionStringProviderName", New CodeExpression() {New CodePrimitiveExpression(text1)})

Cliff

Re: Dynamic connection strings

Not sure what the CodeBLOCKED stuff is all about (or why you changed it!!!)... so can't help there!

The error you're getting seems a little weird too... it looks like it is moaning because you've already added the connection string... but you are allowed to do that... it simply overwrites the connection string for the name! So again... don't know... you could try putting a try catch round that line... and then a bit of debugging!

Re: Dynamic connection strings

James,

This is really confusing and difficult to discuss because the forum software here keeps changing the code snippets that I post. Below is a part of YOUR code as you originally posted it. This gives a compilation error aound the "New CodeBLOCKED EXPRESSION", as reported above.

#Region "Main ConnectionStringsExpressionBuilder Code"
    ' pulled from ConnectionStringExpressionBuilder class... just change how we get the connection strings collection!
    Public Overrides Function GetCodeExpression(ByVal entry As System.Web.UI.BoundPropertyEntry, ByVal parsedData As Object, ByVal context As System.Web.Compilation.ExpressionBuilderContext) As System.CodeDom.CodeExpression
        Dim pair1 As Pair = DirectCast(parsedData, Pair)
        Dim text1 As String = CStr(pair1.First)
        If CBool(pair1.Second) Then
            Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionString", New CodeBLOCKED EXPRESSION {New CodePrimitiveExpression(text1)})
        End If
        Return New CodeMethodInvokeExpression(New CodeTypeReferenceExpression(MyBase.GetType), "GetConnectionStringProviderName", New CodeBLOCKED EXPRESSION {New CodePrimitiveExpression(text1)})
    End Function

I got around this by changing the codeBlocked part to just CodeExpression with () after it and this now seems to work (sorry but the forum won't let me actually type it exactly!)

I wonder if the ASP.Net Forums software is detecting what it thinks is dangerous code being posted and is therfore BLOCKING it???? Please look at your original code as you pasted it and confirm you can see "New CodeBLOCKED EXPRESSION". Is this what your original source code looked like?

Thanks,

Cliff

Re: Dynamic connection strings

Aha!!! It's all making sense now!!! Looks like you are indeed right!! There is no CodeBLOCKED in my original code!!

It obviously doesn't like that!!!! So when you change it, does everything work OK for you??

I'm going to find somewhere to post the source code and create a link to it from there... will get on the case tonight!!

Cheers,

James

Re: Dynamic connection strings

Yes, everything appears to be working fine after making that change. I am still testing but so far the ConnectionString is correctly changed based on the user logon and I have applied the markup changes (basically a simple 'change all' edit applied to the connection strings) to one of my more complicated pages and it appears to work perfectly. I don't pretend to understand what every line of your code does but I understand the approach and am REALLY grateful for all your help. Once you have a better place to post the code I will pass on the link to other people who are struggling with this if that is OK with you?

Many, many thanks!

Cliff

Re: Dynamic connection strings

No worries at all Cliff!! Glad you're all sorted. I'll drop a post once it's finished!

Cheers,

James

Re: Dynamic connection strings

Hi Cliff,

Have a look here:

http://2jsltd.co.uk/

NB this is just a temporary location for it!!!

Cheers,

James

Re: Dynamic connection strings

Excellent!

Re: Dynamic connection strings

Excellent!

Cliff