When you start with an Access form that has a screen prompt for a user to enter a value, this produces a reference inside the SQL which Access uses, for example the control [Forms]![frmproducts]![ProductId] could be included in the SQL used by the form frmproducts.

When the SQL is converted to a SQL Server view (vw_frmProducts) MUST will automatically extract the form reference and create a corresponding parameter _param_ProductID in the table Reporting.tblParameters_M that is then cross joined in the SQL using the view vw_tblParameters to filter the results to match individual user selections.

FROM Products CROSS JOIN vw_tblParameters p WHERE Product_Id = p.[_param_productId]

This article explains additional code and methods that you will need to use in the application to use this approach in your web forms.

Parameters Table

Each individual user of the system will have one row in the table Reporting.tblParameters_M that is used for filtering; the view vw_tblParameters is used by other views in the system to filter the results to a specific users choices.

You could manually add one record for each user in the system, adding the records using the SQL Server Management Studio into the table Reporting.tblParameters_M as shown below entering the Domain\Username for each system user.

Rather than manually adding a row, this row could be created when a user first lands on the systems opening page, or code could be added to the master page to check when displaying any form that the user has a row in the parameter table.

You may find it is useful to add a special dummy parameter field to help with this (as you need to insert some data to create the appropriate row, when doing this the view will automatically add the current users windows authentication name into the filter for you).

We also need to use the sp_refreshview to refresh the view so we can see the new field. No other views will need refreshed.

ALTER TABLE Reporting.tblParameters_M ADD Parameters_Dummy INT GO sp_refreshview 'Reporting.VW_tblParameters' GO SELECT * FROM vw_tblParameters GO

Sample code is show below, which can check for this row and create one when required. In the example below this has been added to the master page, but a better solution would be to add the code to the sites landing page.

Imports System.Data.SqlClient Partial Class SiteMapMaster   Inherits System.Web.UI.MasterPage   Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load     If Not IsPostBack Then     Dim cmd As New SqlCommand     Dim con As New SqlConnection     Dim dbRead As SqlDataReader     Dim strSQL As String     Dim conStr As String     conStr = ConfigurationManager.ConnectionStrings("appConnectionString").ConnectionString     con.ConnectionString = conStr     strSQL = "SELECT COUNT(*) FROM vw_tblParameters"     Try       con.Open()       cmd.CommandType = Data.CommandType.Text       cmd.CommandText = strSQL       cmd.Connection = con       dbRead = cmd.ExecuteReader()       dbRead.Read()       If dbRead(0) = 0 Then         ' need to create a new parameter record         dbRead.Close()         strSQL = "INSERT INTO vw_tblParameters(Parameters_Dummy) " & _         cmd.CommandText = strSQL         cmd.ExecuteNonQuery()       End If     Catch ex As Exception       Response.Write(ex.Message)     End Try     End If   End Sub End Class

Top of Page

Filtering Data

For each web form, MUST can add automatic controls and fields for filtering the data, this uses a form which is bound to the parameter view.

If you look at a webform which has has filtering added, at the bottom of the page you find a form control called asp:FormView ID="FormView_search".

Top of Page

Adding your own controls for filtering

You can also add your own filtering. In the example below an unbound text box is added to the form and AutoPostBack is set true for the control (this includes code to create a new filter parameter record for the user), the example calls ListView1.DataBind()to refresh the data being displayed to the user.

<asp:TextBox ID="txtEnterAData" runat="server" AutoPostBack="True"></asp:TextBox>

Imports System.Data.SqlClient Partial Class ParameterTest   Inherits System.Web.UI.Page   Protected Sub txtEnterAData_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtEnterAData.TextChanged     Dim cmd As New SqlCommand     Dim con As New SqlConnection     Dim dbRead As SqlDataReader     Dim strSQL As String     Dim conStr As String     conStr = ConfigurationManager.ConnectionStrings("appConnectionString").ConnectionString     ' set the connection string     con.ConnectionString = conStr     strSQL = "SELECT COUNT(*) FROM vw_tblParameters"     Try         con.Open()         cmd.CommandType = Data.CommandType.Text         cmd.CommandText = strSQL         cmd.Connection = con         dbRead = cmd.ExecuteReader()         dbRead.Read()         If dbRead(0) = 0 Then           ' need to create a new parameter record           dbRead.Close()           strSQL = "INSERT INTO vw_tblParameters(_param_BeginningDate) VALUES('" & _             Format(Me.txtEnterAData.Text, "Long Date") & "')"           cmd.CommandText = strSQL           cmd.ExecuteNonQuery()         Else           ' update a value           dbRead.Close()           strSQL = "UPDATE vw_tblParameters SET _param_BeginningDate ='" & _             Format(Me.txtEnterAData.Text, "Long Date") & "'"           cmd.CommandText = strSQL           cmd.ExecuteNonQuery()         End If         cmd = Nothing         con.Close()         Me.ListView1.DataBind()     Catch ex As Exception         Response.Write(ex.Message)     End Try   End Sub End Class

Top of Page

Code to download

Click this link to download the code for this article.