E-commerce App 3
The Goals for this Example
In our last e-commerce app we added search functionality. However, we were
not happy with the results in that the data list used to show those results might extend into the bottom
area of the web page layout (where the copyright notice is located, etc.)
In this new version of the same e-commerce app, we display the results in a data grid and use paging
to limit the number of rows of data seen in any search results page to a maximum of 3. Here is a
picture of the first page of results when a search is done for
the string "Windows".
As you can see, links are used to allow the user to jump to the next (or previous) page of search results.
Look at this screen shot of the second page of results for the same
example search as above, the search for "Windows".
In addition, we decide not
to display all of the fields about the products that match the search condition, as that would not easily
fit into the data grid. Instead, each row of the grid will just display the name of the product and
a button to click on to go to a new page showing all of the data on this product. In fact, we will
simply redirect to the existing Results.aspx page
to show all of the data on the selected product. (For the image shown, the selected product
was "Mic1 Microprogramming Simulator".)
Copying the Previous Project
- Open the previous project, e-commerce app 2, in Visual Studio.
- Make a copy of this project and name the new project ecommerce3.
- Close the old solution and open the new ecommerce3 project.
- Save all of your files.
|
Changing from a Data List to a Data Grid
The Search Results Page
- In Visual Studio, go to the SearchResults.aspx web form. Click on the data list and then press
the delete key to remove it.
- We want to set up this form as shown in this screen shot of the
results page or in this view of the form in Visual Studio.
- Put a data grid onto this form and resize it to be approximately the same size as the data list
had been. Put it in the same location, of course, so that it shows up in the content area of
the web page.
- Leave the ID of the DataGrid as DataGrid1.
- Set the Visible property for DataGrid1 to False.
- While DataGrid1 is selected, click on the AutoFormat link in the Properties window and select
"Colorful 1".
- The old 4 labels for error messages should be fine but may need to be moved around a little
so as to fit in just above the data grid.
- Add a new label a new label with text "Search Results" in large, bold, and black font.
- Draw a DataView onto this form and leave the default ID of DataView1.
|
The DataAdapter and Stored Procedure
- Click on the SQLDataAdapter on the SearchResults.aspx web form.
- In the Properties window click on the Configure Data Adapter link so that we can redo this data
adapter.
- Set the data adapter up to use a new stored procedure, called SelectProdSearch2, that
returns just the ProductName field for all rows that match our target. Thus we leave out
the ProductDescription since we don't have room to fit it in the DataGrid without having the
grid overflow into the footer area. The search condition should be the same as before.
In addition, we only want a SELECT command, so be sure to turn off the generation of
INSERT, UPDATE, and DELETE stored procedures. Do put the results into ascending order by
the ProductName field, and indicate that you only want distinct items returned.
- To remind yourself how to use Query Builder to set up a stored procedure you can examine
this screen shot from e-commerce app 2. Just be sure
to not select the ProductDescription field this time. (You do this by making sure the Output
column is not checked for this field.)
- When finished, your stored procedure should look like the following:
|
CREATE PROCEDURE studentc.SelectProdSearch2
(
@Target varchar(50)
)
AS
SET NOCOUNT ON;
SELECT DISTINCT ProductName FROM dbo.Prod5 WHERE (ProductName LIKE '%' + @Target + '%')
OR (ProductDescription LIKE '%' + @Target + '%') ORDER BY ProductName
GO
|
- Remember to go to SQL Server Enterprise Manager and give the SQL or IWAM account Exec access
to this stored procedure. (Use whatever fits your server configuration.)
- In the Properties window for your new data adapter, expand the SelectCommand field.
Replace what is in the CommandText field with yourid.SelectProdSearch2.
(Of course, fill in your user ID in place of yourid.) Say Yes and Yes to the questions asked.
- In the Properties window for the data adapter, try out the Preview Data link to see if your
stored procedure is working.
- In Visual Studio do File, Save All.
More on the Search Results Page
- Click on your data grid and in the Properties view, click on the Property Builder link.
- Click on General. Fill in DataView1 under DataSource.
- Click on Paging. Check the Allow Paging box. Set the page size to 3.
Check "Show Navigation Buttons". Set their position to Bottom.
For Mode use "Next, Previous Buttons". For the text on the buttons use "Next" and "Previous".
See this picture of the data grid paging settings.
- Now click on the Columns tab. Under "Available columns" click on "Button Columns".
Then click on the Add button (labelled with a > symbol). Under "Header text" fill in
"For more info". Under Text put "View" and under "Command name" put "View". For Button
type select PushButton. Examine this screen shot of the
button column properties for the data grid. Click on OK once you have the setting right.
- Save all of your files before proceeding further.
|
The results.aspx.vb Code-Behind File
- Look at the code inside of the "Web Form Designer Generated Code" region.
- Add the following line in the usual location:
Protected dTable As New DataTable
- Remove the DataSet that we had placed here for the previous web app.
- Adjust the Page_Load procedure so that it looks as follows:
|
Private Sub Page_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim Substring As String
If Not IsPostBack Then
If Session.Count = 0 Then ' No session items were supplied.
SessionErrorLabel.Visible = True
Else
Try
Substring = Session("TargetSubstring")
If Substring = "" Then
SessionErrorLabel.Visible = True
Else
SqlConnection1.Open()
SqlDataAdapter1.SelectCommand.Parameters("@Target").Value = Substring
SqlDataAdapter1.Fill(dTable)
If dTable.Rows.Count = 0 Then
NoDataLabel.Visible = True
DataGrid1.Visible = False
Else
DataView1 = New DataView(dTable)
DataGrid1.DataBind()
DataGrid1.Visible = True
End If
End If
Catch exception As System.Data.SqlClient.SqlException
DatabaseErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally
SqlConnection1.Close()
End Try
End If
End If
End Sub
|
- You should be able to follow this code by now as it is similar to what we have used before.
- Overall, we use our data adapter and stored procedure to query the database for the ProductName
of each product that matches our search condition. As long as we get data back, we place it
in our DataTable and fill the grid with it.
- Save all of your files before going on.
- We need to add the following procedure to the code-behind file to handle paging in the DataGrid.
Go ahead and make this addition.
' handles DataGrid1 page changed event
Private Sub DataGrid1_PageIndexChanged(ByVal sender As Object, _
ByVal e As DataGridPageChangedEventArgs) _
Handles DataGrid1.PageIndexChanged
Dim Substring As String
If Session.Count = 0 Then ' No session items were supplied.
SessionErrorLabel.Visible = True
Else
Try
Substring = Session("TargetSubstring")
If Substring = "" Then
SessionErrorLabel.Visible = True
Else
SqlConnection1.Open()
SqlDataAdapter1.SelectCommand.Parameters("@Target").Value = Substring
SqlDataAdapter1.Fill(dTable)
If dTable.Rows.Count = 0 Then
NoDataLabel.Visible = True
DataGrid1.Visible = False
Else
DataView1 = New DataView(dTable)
DataGrid1.CurrentPageIndex = e.NewPageIndex 'Show new page
DataGrid1.DataBind()
DataGrid1.Visible = True
End If
End If
Catch exception As System.Data.SqlClient.SqlException
DatabaseErrorLabel.Visible = True
Catch exception As Exception
ErrorLabel.Visible = True
Finally
SqlConnection1.Close()
End Try
End If
End Sub
|
- The above procedure is used whenever the user clicks on the Previous or Next links at
the bottom of the DataGrid.
- It uses the same data adapter and stored procedure to get the ProductName for each product
that matches the search condition. This matching data is put in our DataTable named dTable.
- The code
DataGrid1.CurrentPageIndex = e.NewPageIndex is what is different here.
This indicates what page index to use in the data grid. Because of this we see the proper
page of data.
- There is one more procedure that we need to add to this same code-behind file. Add the following:
Private Sub DataGrid1_ItemCommand(ByVal Source As Object, _
ByVal e As DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
'Note that e.Item is the row where the user clicked a View button
If e.CommandName = "View" Then
Session.Add("ProductSelected", e.Item.Cells(1).Text)
Response.Redirect("Results.aspx")
End If
End Sub
|
- This procedure is called when the user does something with an item in the DataGrid. In our
case this something is to click on one of the View buttons.
- The code checks to see that the CommandName is View (which it will be if the user clicked
on a button since we filled in "View" for the Command name for each button).
- As long as we have a match with "View", we add to the session state that the product select
is
e.Item.Cells(1).Text . As the comment says, e.Item is the row
in the DataGrid where the user clicked the button. The Cells(1) picks out the
second cell in that row. (Remember that the counting starts at 0 and not 1.) We then
use Text to get the text for that cell. This text is the product name for the
product about which the user wants more information.
- Finally, the code redirects to the existing Results.aspx form, which we used to display the
details product information on a product selected from a drop-down list on the Products.aspx
page. We might as well re-use this Results.aspx page instead of creating a new one that does
essentially the same thing.
Testing Your Web Application
- Build your project.
- View your new web app in a browser and check that all pages display correctly
and that the database-related code works on the products and results pages as well
as the search and search results pages.
- On the search results page be sure to try out the Next and Previous links to see
that the paging works correctly. Also check the View buttons to see that they
send you to the results page for detailed information on the selected product.
- We now have a very useful search capability in our web app, and it presents the
results more nicely than our previous web app did.
|
Back to the main page for ASP .NET Web Apps
|