CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

Schoology Facebook        Search CIS Site      Tutorials

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

Author: Br. David Carlson
Last updated: September 14, 2008
Disclaimer