Figure 8.15. This Customers page is a common one used to demonstrate calling the search form.
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.
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.
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).
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).
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.
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.
|
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 |
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.
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
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.
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
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.
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
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.
|
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 |
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()
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.
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
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.
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
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.
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
 |
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. |
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.
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.
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
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.
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
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:
"Customers" for the Session object entry SearchRecordSource
"CustomerID" for the Session object entry KeyField
"CompanyName" for the Session object entry SearchField
"wfrmHowTo8_8a" for the Session object entry CallingPage
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.
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.