Working with vw_tblParameters

support@ascassociates.biz , Sales Enquiries

Working with vw_tblParameters

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

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".

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

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz