8.8 Make a Generic Search Form in an ASP.NET Web Application

This tutorial will show you how to add a unique search Web Form to your Web application that can be set up using session variables and used for various tables.

Creating search forms for Web applications is pretty common. You would like to be able to use the same Web Form for different tables in the same application.

This How-To will demonstrate using the same Web Form for searching various tables, taking advantage of the Session object.

Technique

This How-To will use the Session object with a Web Form, something that has been done throughout the book. You will see a good example of using the Session object to pass values-in this case, the record source, the key search field, and the display search field-to the search form. Finally, the return value will be passed back to the calling Web Form using the Session object.

One new control that is used in this How-To is the Panel control. It will be used to group the controls on the search Web Form. The Panel control will be used simply by throwing controls into it and letting it control the layout.

You will see the paging used with the DataGrid control as well.

Steps

Open and run the VB.NET -Chapter 8 solution. From the main Web Form, click on the hyperlink with the caption How-To 8.8: Make a Generic Search Form Using a Web Form. This page is a simple one that contains text boxes for the Customer table in Northwind (see Figure 8.15).

Figure 8.15. This Customers page is a common one used to demonstrate calling the search form.

graphics/08fig15.gif

Click on the Search button to open the search page, and then click on the button labeled B. You will see the DataGrid object displayed in the bottom of the form filled with the CompanyID and CompanyName fields of the Customer table, which begin with B (see Figure 8.16).

Figure 8.16. This form can be used for searching within any of the tables in your databases.

graphics/08fig16.gif

Click the Select button for one of the customers who is displayed in the grid, and then click Accept. The customer page will be displayed, and the fields will be filled in with the data from the chosen record.

  1. Create a Web Form. Then place the controls shown in Figure 8.16 of the form calling the search form, with the properties set forth in Table 8.10.

    Table 8.10. Label, TextBox, and Command Button Controls Property Settings for the Calling Form

    Object

    Property

    Setting

    DOCUMENT

    bgColor

    buttonface

    Label

    Caption

    Customer ID

    Label

    Caption

    Company Name

    Label

    Caption

    Contact

    Label

    Caption

    Contact Title

    Label

    Caption

    Address

    Label

    Caption

    City

    Label

    Caption

    Region

    Label

    Caption

    Country

    Label

    Caption

    Phone

    Label

    Caption

    Fax

    TextBox

    Name

    txtCustomerID

    TextBox

    Name

    txtCompanyName

    TextBox

    Name

    txtContactName

    TextBox

    Name

    txtContactTitle

    TextBox

    Name

    txtAddress

    TextBox

    Name

    txtCity

    TextBox

    Name

    txtRegion

    TextBox

    Name

    txtPostalCode

    TextBox

    Name

    txtCountry

    TextBox

    Name

    txtPhone

    TextBox

    Name

    txtFax

    Button

    Name

    btnSearch

     

    Caption

    &Search

  2. On btnSearch, add the code in Listing 8.45 to the Click event. This sets up the search Web Form as far as telling it what it needs to know, including how to get back to this page, which is the calling page. The search page, in this case "wfrmHowTo8_8b.aspx," is then opened.

    Listing 8.45 frmHowTo8_8a.vb: Storing Values to the Session Object for Use on Another Page
    Private Sub btnSearch_Click(ByVal sender As System.Object,
                    ByVal e As System.EventArgs) Handles btnSearch.Click
    
            Session.Item("SearchRecordSource") = "Customers"
            Session.Item("SearchField") = "CompanyName"
            Session.Item("KeyField") = "CustomerID"
            Session.Item("CallingPage") = "wfrmHowTo8_8a.aspx"
    
            Server.Transfer("wfrmHowTo8_8b.aspx")
    
    End Sub
    
  3. Add the code in Listing 8.46 to the Load event of the Web Form. If the Session object has an entry for ResultValue, then the LoadIndividual routine is executed and the ResultValue is passed. The LoadIndividual routine is described in the next step. This routine is coded so that when the page is reloaded after the search form has been used, the Session object entry will exist. When you first come into the page, the entry doesn't exist.

    Listing 8.46 frmHowTo8_8a.vb: Loading an Individual Record into Text Boxes on the Form
      Private Sub Page_Load(ByVal sender As System.Object,
               ByVal e As System.EventArgs) Handles MyBase.Load
    
           If Not (Session("ResultValue") Is Nothing) Then
               LoadIndividual(Session("ResultValue"))
           End If
    
    End Sub
    
  4. Create the LoadIndividual routine by entering the code shown in Listing 8.47 in the form. Taking the strCustID passed from the results of the search, a data adapter is created, and a data table is filled. Last, each of the TextBox controls is loaded with the value from the column with the corresponding name.

    Listing 8.47 frmHowTo8_8a.vb: Loading an Individual Record into Text Boxes on the Form
    Private Sub LoadIndividual(ByVal strCustID As String)
    
         Dim odaCustIndiv As New _
            OleDb.OleDbDataAdapter("Select * From Customers Where CustomerID = '" &
            strCustID & "'", BuildCnnStr("(local)", "Northwind"))
    
         Dim dtCustIndiv As New DataTable()
    
         odaCustIndiv.Fill(dtCustIndiv)
    
         With dtCustIndiv.Rows(0)
            Me.txtCustomerID.Text = .Item("CustomerID").ToString
            Me.txtCompanyName.Text = .Item("CompanyName").ToString
            Me.txtContactName.Text = .Item("ContactName").ToString
            Me.txtContactTitle.Text = .Item("ContactTitle").ToString
            Me.txtAddress.Text = .Item("Address").ToString
            Me.txtCity.Text = .Item("City").ToString
            Me.txtRegion.Text = .Item("Region").ToString
            Me.txtCountry.Text = .Item("Country").ToString
            Me.txtPostalCode.Text = .Item("PostalCode").ToString
            Me.txtPhone.Text = .Item("Phone").ToString
            Me.txtFax.Text = .Item("Fax").ToString
         End With
    
    End Sub
    
  5. Create the next Web Form, and call it whatever name you referred to in the search Web Form in step 2. Then place the controls shown in Figure 8.16 of the search page, with the properties set forth in Table 8.11.

    Table 8.11. Label, TextBox, DataGrid, and Command Button Controls Property Settings for the Calling Form

    Object

    Property

    Setting

    DOCUMENT

    bgColor

    buttonface

    TextBox

    Name

    Label1

     

    Text

    Click on a Letter

    Panel

    Name

    Panel1

    Button

    Name

    btnA

     

    Caption

    A

    Button

    Name

    btnB

     

    Caption

    B

    Button

    Name

    btnC

     

    Caption

    C

    ...

       

    Button

    Name

    btnZ

     

    Caption

    Z

    Button

    Name

    btnAll

     

    Caption

    All

    DataGrid

    Name

    dgSearch

     

    AllowPaging

    True

    Button

    Name

    btnAccept

     

    Caption

    &Accept

    Button

    Name

    btnCancel

     

    Caption

    &Cancel

  6. In the class module for the Web Form, add the following Private declaration just below the line of code that reads Web Form Designer Generated Code:

    Private mdtSearch As New DataTable()
    
  7. On the Web Form, add the code in Listing 8.48 to the Load event. This routine loads the data table stored in the Session object if it exists.

    Listing 8.48 frmHowTo8_8b.vb: Executing a SQL Server-Supplied Stored Procedure That Lists the Tables in the Database
    Private Sub Page_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MyBase.Load
    
        If Not (Session("SearchDataTable") Is Nothing) Then
            mdtSearch = CType(Session("SearchDataTable"), DataTable)
        End If
    
    End Sub
    
  8. For each of the command buttons that has a single letter, add the first subroutine displayed in Listing 8.49 to each of the Click events. For the btnAll Button control, add the second subroutine to the Click event. Each Button control will pass the letter it represents to the subroutine called SetData, discussed in the next step. The btnAll code simply passes the empty string.

    Listing 8.49 frmHowTo8_8b.vb: Click Events for Each of the Letter Button Controls
    Private Sub btnA_Click(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) Handles btnA.Click
    
            SetData("A")
    
    End Sub
    
    Private Sub btnAll_Click(ByVal sender As System.Object, _
                        ByVal e As System.EventArgs) Handles btnAll.Click
    
            SetData("")
    
    End Sub
    
  9. Add the subroutine in Listing 8.50 to the class module of the form. This routine takes the letter value passed in strFilterLetter as a parameter. A SQL Select string is created that takes the literal values Select, From, and Where and uses the Session object entries KeyField, SearchField, and SearchRecordSource. The SearchField property is used with the Like clause, also using the strFilterLetter and the % (wildcard). Note that if " is passed to strFilterLetter, all the records will be listed. Finally, mdtSearch is filled in the routine BindTheGrid set as the data source for dgSearch, which is the DataGrid control. The routine called BindTheGrid is located at the end of the listing.

    Listing 8.50 frmHowTo8_8a.vb: Filling the Results Set Based on the Letter Button Pressed
        Sub SetData(ByVal strFilterLetter As String)
    
            Dim odaSearch As OleDb.OleDbDataAdapter
    
            odaSearch = New _
                     OleDb.OleDbDataAdapter("Select " & Session.Item("KeyField") &
                     ", " & Session.Item("SearchField") & " From " & _
                    Session.Item("SearchRecordSource") & " Where " & _
                    Session.Item("SearchField") & " Like '" & _
                    strFilterLetter & "%'", (BuildCnnStr("(local)", "Northwind")))
    
            mdtSearch.Clear()
            odaSearch.Fill(mdtSearch)
            Session("SearchDataTable") = mdtSearch
    
            BindTheGrid()
    
       End Sub
    
    Private Sub BindTheGrid()
    
         dgSearch.DataSource = mdtSearch
         dgSearch.DataBind()
    
    End Sub
    

    Note

    graphics/note_icon.gif

    This routine, more than any in this How-To, shows the flexibility of this technique. You can use any table values for these properties. This is a major benefit when you think of how many places you can use this form without changing a line of code in the form.

  10. Right-click on the data grid and choose Property Builder. Go to the Columns tab and add a Select button. Set the Button Type to be PushButton (see Figure 8.17) and click OK.

    Figure 8.17. There is no code required for this button.

    graphics/08fig17.jpg

  11. On the buttons called btnAccept and btnCancel, add the code in Listing 8.51 to the appropriate Click event of each. The btnAccept_Click routine derives the data row that is currently selected from mdtSearch using the data grid's SelectedIndex property and adding the paging that has to occur using GetPageRows(), which returns the actual rows given the current page that the user is on in the DataGrid object. You can see the GetPageRows() routine at the bottom of the listing. The KeyField Session object entry is then used to store the individual column value of drCurr in the ResultValue Session object entry. The calling page is reloaded.

    The ResultValue is not set in the btnCancel_Click routine. The calling page is simply reloaded.

    Listing 8.51 frmHowTo8_8b.vb: Storing the Resulting Key Value to the ResultValue Session Object Entry and Returning to the Calling Page
    Private Sub btnAccept_Click(ByVal sender As System.Object,
                    ByVal e As System.EventArgs) Handles btnAccept.Click
    
        Dim drCurr As DataRow
    
        Try
            drCurr = mdtSearch.Rows(dgSearch.SelectedIndex + GetPageRows())
    
            Session("ResultValue") = drCurr.Item(Session("KeyField")).ToString
    
        Catch exc As Exception
    
        End Try
    
    
    Server.Transfer(Session("CallingPage"))
    
    
    End Sub
    
    Private Sub btnCancel_Click(ByVal sender As System.Object,
                    ByVal e As System.EventArgs) Handles btnCancel.Click
    
    Server.Transfer(Session("CallingPage"))
    
    End Sub
    
    Function GetPageRows() As Integer
    
        '-- This helps synchronize the data table rows
        '   with the data grid page and row.
        GetPageRows = dgSearch.PageSize * dgSearch.CurrentPageIndex
    
    End Function
    
  12. The last step is to add the code in Listing 8.52 for synching up pages in the data grid when the user switches pages.

Listing 8.52 frmHowTo8_8b.vb: Updating the Data Grid with the New Page Number
Private Sub dgSearch_PageIndexChanged(ByVal source As Object,
            ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs)
            Handles dgSearch.PageIndexChanged

    '-- Set the current page in the data grid.
    Me.dgSearch.CurrentPageIndex = e.NewPageIndex

    BindTheGrid()

End Sub

How It Works

When the user clicks on the Search button, the calling page stores values in the Session object for use in the search page, telling it what record source to use, as well as other information used for searching for the specific record and table desired:

After the search page is loaded, the user clicks one of the letters to narrow down the records to look for. A data adapter is passed a SQL String made up of the Session object entries just mentioned, a data table is filled, and the data grid's DataSource property sets the data table.

When the user clicks Accept, the DataTable is retrieved from the data grid, which then produces the data row that contains the key field. The key field is then stored in the ResultValue Session object entry of the search form, and the calling page is reloaded.

Back on the calling form, the LoadIndividual routine is called. Then the routine is passed the ResultValue Session object entry from the search form. The text boxes are loaded with the data row results.

Comments

This technique shows a number of ways that the various ADO.NET objects can be used. Using the Session object and other State management methods is handy for creating pages that you want to use for more than one table. You will find use for this technique throughout your applications after you become accustomed to it.

Again, you can enhance this tool in several ways. One way is to allow the user to enter a string value to type in, and narrow down the choices even more. Another way is to add a property that could be used to specify multiple columns to be displayed.