CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

Schoology Facebook        Search CIS Site      Tutorials

Cars App 1



What's the Plan?


The plan is to create a web app similar to e-commerce app 3, but to adapt it for an online store selling made-to-order cars from various manufacturers. The main feature that is added is the grouping of products (cars) by departments (brands, manufacturers, categories, or whatever you wish to call this). Thus, a customer would typically select a brand of car from a drop-down list and then select a particular model of car from the drop-down list that appears as a result of the choice of brand. Selecting the model brings up a page of detailed information on the product (car).

Since our business builds cars as the orders arrive, there is no inventory of cars to keep track of. In other e-commerce applications, tracking inventory and letting customers see if a product is in stock or not might be needed. Inventory information might be kept in its own table or might be a column in the Product table.

The search functionality in this app can also be used to look up cars matching a certain target string. This search functionality is expanded so that the matches for the target string are sought in substrings of the product name (car model), product brand name (manufacturer), and product description. The brand name is the new item here.
  • The home page is shown in this screen shot.
  • The privacy page and contact page are much the same as we used before.
  • The brands page allows the user to choose from a pull-down list the brands (manufacturers) of cars.
  • Doing so brings up the cars page, which allows the user to choose a particular model of car from a pull-down list.
  • That then brings up the results page, which shows the details about that particular car, such as the description and price.
  • The search page is similar to what we used before.
  • After entering a search string at the search page, the search results page is shown.
  • Clicking on one of the View buttons on the search results page takes you to the results page already seen above.

Copying an Old Project

  • Although you can build this project from scratch, it is probably easier to copy e-commerce app 3.
  • Open the project, e-commerce app 3, in Visual Studio.
  • Make a copy of this project and name the new project cars1.
  • Close the old solution and open the new cars1 project.
  • Save all of your files.

Database Changes



Product Table

  • We basically create entirely new tables to hold the data for our cars web site.
  • First, use SQL Server's Enterprise Manager to create a table called Product designed as follows:
Column Name Data Type Length Allow Nulls
ProdNum Int 4 no
ProdName char 24 no
ProdDescription varchar 128 yes
ProdPrice money 8 no

  • In addition, make ProdNum to be the primary key field.
  • Also make it an Identity field.
  • Fill in descriptions for each field if you wish to do so.
  • Add the following data to this table:
ProdNum ProdName ProdDescription ProdPrice
1 Hurricane Top of line luxury automobile. GPS navigation system comes standard. 35255
2 Tornado Top of line sports car. 32999
3 Cheetah Modestly-priced sports car. 27880
4 Famtastic Modestly-priced family car. 22555
6 Lightning Sporty two-seater electric/gas hybrid car. 33000
7 Bolt Small 4-seater electric/gas hybrid car. 36500
8 Thunder Large 6-seater electric/gas hybrid car. 39999
9 Rocket Our fastest sports car. 38400
10 Laser Small 2-seater sports car. 37600
11 Kangaroo Large all terrain vehicle. 35000
12 Rabbit Small all terrain vehicle. 33900
13 M-star Medium-sized sport utility vehicle. 29000
14 Roady Modestly-priced sports car. 28100
15 Hippo Large sport utility vehicle. 31200

Category Table

  • Next, create a table called Category to hold the product categories (brands or manufacturers if you prefer that terminology).
  • Use the following design in Enterprise Manager:
Column Name Data Type Length Allow Nulls
CategoryNum Int 4 no
CategoryName char 24 no

  • In addition, make CategoryNum to be the primary key field.
  • Also make it an Identity field.
  • Fill in descriptions for each field if you wish to do so.
  • Add the following data to this table:
CategoryNum CategoryName
1 Electra Motors
2 Alpha Omega
3 Vanguard
4 Road Master

CategoryProduct Table

  • Next, create a table called CategoryProduct to link the Category and Product tables.
  • Use the following design in Enterprise Manager:
Column Name Data Type Length Allow Nulls
CategoryNum Int 4 no
ProdNum int 4 no

  • In addition, make CategoryNum and ProdNum to be the composite primary key field.
  • To do this, use CTRL-click on the 2 rows and then press the key button.
  • Fill in descriptions for each field if you wish to do so.
  • Add the following data to this table:
CategoryNum ProdNum
1 6
1 7
1 8
2 9
2 10
2 11
2 12
3 1
3 2
3 3
3 4
4 13
4 14
4 15

Relationships Between Tables

  • We should set up the appropriate relationships between the CategoryProduct table and each of the other two tables.
  • This is done much like in the previous survey app.
  • Open the CategoryProduct table in design view.
  • Set up a relationship between the ProdNum (primary key) field of the Product table and the ProdNum (foreign key) field of the CategoryProduct table. You can refer to this picture of the setup for this relationship.
  • Similarly, set up a relationship between the CategoryNum (primary key) field of the Category table and the CategoryNum (foreign key) field of the CategoryProduct table. You can refer to this picture of the setup for this relationship.

Web Page Changes and Additions



Home Page

  • The home page should be adjusted to contain the text "Order a new car, built as you like it!" and other lines as seen in this screen shot, though you can adjust the contents as you like.
  • In either HTML view or in Properties, change the title of the home page to "Home" or perhaps Home followed by the name of the web site.
  • In addition, the header.txt and footer.txt files that give the standard layout for each page need to be adjusted. The changes for header.txt are summarized here:
    • The name of the business, found in the line with font size 7, has been changed from "Br. David's Software" to something about cars. The color was changed to red for this.
    • The image displayed at the top of the page was changed to cars1.jpg and was derived from clip art. You will probably want to drag this image file from My Computer to Solution Explorer so as to add it to this project. Also in Solution Explorer, right click on the old earth image and delete it from the project.
    • All of the navigation icons along the left side have been changed to use http://cis.stvincent.edu/icons/ball.red.gif in the img src tag.
    • The Home link and icon all use home.aspx in the A HREF tags as the page to go to. In addition, the old hw5.aspx home page was renamed as home.aspx so as to match this.
    • The second link was changed to "Brands" and uses brands.aspx in the A HREF tags as the page to go to.
    • The text for the link that said "Contact Us" has been shortened to "Contact".
    • The search and privacy links use the same text and pages to jump to as before. The only thing that changes for these is the new icon, as mentioned above.
  • The changes for footer.txt are simple ones. Adjust the copyright notice to reflect the company name, adjust the date as desired, and fill in whatever you like for the warning. The screen shots use this: "Warning: This is a demo, not a real e-commerce site! Any resemblance to real cars is accidental."

Privacy and Contact Pages

  • No changes are needed on the privacy page other than to change the title to Privacy (with the company name appended if you wish).
  • On the contact page, change the contact information to something reasonable for this business. Perhaps the email address should be for the sales department, for example. Also change the title to Contact (with the company name appended if you want). You can refer to this screen shot.

Brands Page

  • Rename the old products.aspx page as brands.aspx.
  • We want this page to look like this.
  • Change the title to Brands (with the company name appended if you wish).
  • You might want to change the text of the label to "Select a brand of car from the pull-down:".
  • Change the ID of the drop-down list to BrandList.
  • Click on the data adapter at the bottom of this page and then click on the configure link at the bottom of the Properties window.
  • Set up the data adapter to use a new stored procedure named SelectCategoryName.
  • Do NOT generate insert, delete, or update stored procedures to go along with this one.
  • The stored procedure should simply return the CategoryName column from the Category table and put the results in ascending order.
  • The resulting stored procedure should look like this:

CREATE PROCEDURE studentc.SelectCategoryName
AS
    SET NOCOUNT ON;
SELECT CategoryName FROM dbo.Category ORDER BY CategoryName
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.SelectProdSearch. (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.
  • Of course, save all of your files now and periodically throughout the development of this web app.

Brands Code-Behind File

  • Open the brands.aspx.vb code-behind file.
  • Adjust the code to match that shown below. These changes involve referring to the BrandList instead of the old ProductList, redirecting to cars.aspx instead of results.aspx, etc.

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 TargetBrand As String

    If Not IsPostBack Then
        Try
            BrandList.Items.Add("*** Make a choice ***")
            SqlConnection1.Open()
            dReader = SqlDataAdapter1.SelectCommand.ExecuteReader
            While (dReader.Read())
                BrandList.Items.Add(dReader.GetString(0))
            End While
        Catch exception As System.Data.SqlClient.SqlException
            ErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try
    Else   ' Postback case:
        TargetBrand = BrandList.SelectedItem.Text
        If Not Equals(TargetBrand, "*** Make a choice ***") Then
            Session.Add("BrandSelected", TargetBrand)
            Response.Redirect("cars.aspx")
        End If
    End If
End Sub

Cars Page

  • We need to create that cars.aspx page.
  • Begin by adding a new web form to your project and name the form cars.aspx.
  • We want this page to look like this picture.
  • You can copy the 2 labels and the drop-down list from the brands.aspx form to the cars.aspx form.
  • Make a copy of the large font label and place it at the top, so that there are 2 large labels above the drop-down list.
  • The top label will be used to display the brand of car selected on the previous page.
  • Change the ID of this label to BrandLabel and use the empty string for its text.
  • Change the text for the label below this to "Select model of car from the pull-down:"
  • Change the ID of the drop-down list to CarsList and make sure that AutoPostBack is True.
  • Change the title of this page to "Cars" (with the company name appended if you like).
  • Remember to go to HTML view and include the header.txt and footer.txt files. (If you have forgotten where they go, just look at one of the old web pages such as contact.apsx.)
  • Add an SqlDataAdapter to this form and set it up to use a new stored procedure called SelectProdName.
  • Use query builder to set up this stored procedure as shown here. Note that you put all 3 of our tables into query builder: Product, Category, and CategoryProduct. The stored procedure should return the ProdName field from the Product table and the CategoryName field from the Category table for all rows of the joined table where the @TargetBrand parameter matches the CategoryName. This is because we want to get a list of the names of the products (cars) for the selected category (brand). We will put this list of product names into the drop-down list on the cars page. We also order this data by ProdName.
  • Do NOT generate insert, delete, or update stored procedures to go along with this stored procedure.
  • The resulting stored procedure should look like this:

CREATE PROCEDURE studentc.SelectProdName
(
    @TargetBrand char(24)
)
AS
    SET NOCOUNT ON;
SELECT dbo.Product.ProdName, dbo.Category.CategoryName
FROM dbo.Product INNER JOIN dbo.CategoryProduct 
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Category.CategoryName = @TargetBrand)
ORDER BY dbo.Product.ProdName
GO

  • As usual 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.SelectProdSearch. (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.

Cars Code-Behind File

  • In the cars.apsx.vb code-behind file we set up the BrandLabel so that it displays the brand name already chosen (and available here because we saved it in the session state).
  • We also pass this brand name as the parameter to the stored procedure so that it can look up the names of all the car models for this manufacturer (brand). These model names are then used to populate the CarList drop-down list.
  • If the user selects an item from the list (other than the "*** Make a choice ***" string) we add the selected model name to the session state and redirect to the results.aspx page.
  • Add Protected dReader As System.Data.SqlClient.SqlDataReader in the usual spot inside the Web Form Designer Generated Code region.
  • The complete code for the Page_Load procedure should look like this:

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 TargetModel As String

    If Not IsPostBack Then
        Try
            BrandLabel.Text = "Brand (manufacturer): " & Session("BrandSelected")
            CarsList.Items.Add("*** Make a choice ***")
            SqlConnection1.Open()
            SqlDataAdapter1.SelectCommand.Parameters("@TargetBrand").Value = 
Session("BrandSelected")
            dReader = SqlDataAdapter1.SelectCommand.ExecuteReader
            While (dReader.Read())
                CarsList.Items.Add(dReader.GetString(0))
            End While
        Catch exception As System.Data.SqlClient.SqlException
            ErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try
    Else   ' Postback case:
        TargetModel = CarsList.SelectedItem.Text
        If Not Equals(TargetModel, "*** Make a choice ***") Then
            Session.Add("ModelSelected", TargetModel)
            Response.Redirect("results.aspx")
        End If
    End If
End Sub

Results Page

  • Go to the results.aspx page.
  • It should still contain old content about Br. David's Software.
  • We want to revise this page as shown in this screen shot.
  • Remove the download link and the image.
  • The 4 labels and text box should remain but move them so that the items simply go in sequence down the page.
  • Change the ID of the top label to BrandLabel and make its text field empty.
  • Change the ID of the next label to ModelLabel and make its text field empty as well.
  • We will fill in these labels from the code-behind file.
  • Change the title of the page to "Selected Product" (with the company name appended if you wish).
  • Click on the data adapter at the bottom of this page and then click on the configure link at the bottom of the Properties window.
  • Set up the data adapter to use a new stored procedure named SelectModelBrand.
  • Do NOT generate insert, delete, or update stored procedures to go along with this.
  • The stored procedure should return the product description and product price for the brand and model of car chosen by the user.
  • Use query builder to set up this stored procedure as shown here. Once again you put all 3 of our tables into query builder: Product, Category, and CategoryProduct.
  • The resulting stored procedure should look like this:

CREATE PROCEDURE studentc.SelectModelBrand
(
    @TargetModel char(24),
    @TargetBrand char(24)
)
AS
    SET NOCOUNT ON;
SELECT dbo.Product.ProdDescription, dbo.Product.ProdPrice
FROM dbo.Product INNER JOIN dbo.CategoryProduct 
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Product.ProdName = @TargetModel) AND (dbo.Category.CategoryName = @TargetBrand)
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.SelectProdSearch. (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.

Results Code-Behind File

  • The existing Page_Load code in this file needs to be modified a bit though the overall design is much the same.
  • In particular, we now have 2 parameters to fill in for the stored procedure, namely the model and brand of car.
  • The revised code should look like this:

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 Price As Decimal
    Dim Model As String
    Dim Brand As String

    BrandLabel.Visible = False
    ErrorLabel.Visible = False
    ModelLabel.Visible = False
    PriceLabel.Visible = False
    ProductDescriptionBox.Visible = False

    If Not IsPostBack Then
        If Session.Count = 0 Then   ' No session items were supplied.
            ErrorLabel.Visible = True
        Else
            Try
                Model = Session("ModelSelected")
                Brand = Session("BrandSelected")
                SqlConnection1.Open()
                SqlDataAdapter1.SelectCommand.Parameters("@TargetModel").Value = Model
                SqlDataAdapter1.SelectCommand.Parameters("@TargetBrand").Value = Brand
                SqlDataAdapter1.Fill(dTable)

                If dTable.Rows.Count = 0 Then
                    ErrorLabel.Visible = True
                Else
                    ProductDescriptionBox.Text = dTable.Rows.Item(0).Item(0)
                    ProductDescriptionBox.Visible = True
                    Price = dTable.Rows.Item(0).Item(1)
                    PriceLabel.Text = "Price:  " & String.Format("{0:C}", Price)
                    PriceLabel.Visible = True
                    BrandLabel.Text = "Selected Brand: " & Brand
                    BrandLabel.Visible = True
                    ModelLabel.Text = "Selected Model: " & Model
                    ModelLabel.Visible = True
                End If
            Catch exception As System.Data.SqlClient.SqlException
                ErrorLabel.Visible = True
            Catch exception As Exception
                ErrorLabel.Visible = True
            Finally
                SqlConnection1.Close()
            End Try
        End If
    End If
End Sub

Search Page

  • Change this page so that the first label reads "You can search for cars whose brand name, model name,".
  • Then have the second label continue with "or description contains a desired term or substring."
  • Add the required field validator and the same regular expression validator that we used in e-commerce app 4.
  • Change the title of the page to "Search" (with the company name appended if you wish).
  • You can refer to this screen shot of the search page.
  • No changes should be needed to the code-behind file for this page.

Search Results Page

  • Open SearchResults.aspx.
  • Change the title of the page to "Search Results" (with the company name appended if you desire).
  • The rest of the contents of the page should be fine, including the Property Builder settings for the data grid, where we set things up to use Previous and Next buttons for paging and to use push buttons labelled View to allow the user to view the details on any one product.
  • Click on the data adapter at the bottom of this page and then click on the configure link at the bottom of the Properties window.
  • Set up the data adapter to use a new stored procedure named SelectLikeBrandModelDesc.
  • Do NOT generate insert, delete, or update stored procedures to go along with this.
  • The stored procedure should return the product name and category (brand) name for any cars whose brand name, model name, or description contain the parameter value as a substring.
  • Use query builder to set up this stored procedure as shown here. Once again you put all 3 of our tables into query builder: Product, Category, and CategoryProduct.
  • The resulting stored procedure should look like this:

CREATE PROCEDURE studentc.SelectLikeBrandModelDesc
(
    @Target varchar(24)
)
AS
    SET NOCOUNT ON;
SELECT dbo.Product.ProdName, dbo.Category.CategoryName
FROM dbo.Product INNER JOIN dbo.CategoryProduct 
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Product.ProdName LIKE '%' + @Target + '%') 
OR (dbo.Category.CategoryName LIKE '%' + @Target + '%')
OR (dbo.Product.ProdDescription LIKE '%' + @Target + '%')
ORDER BY dbo.Category.CategoryName, dbo.Product.ProdName
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.SelectProdSearch. (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.

Search Results Code-Behind File

  • No changes should be needed in the Page_Load procedure in this file.
  • Nor are any changes needed in the DataGrid1_PageIndexChanged procedure.
  • You will, however, need to change the DataGrid1_ItemCommand procedure that handles things when the user clicks on a View button to see the details of a particular car. In particular, you need to put both the model and brand selected into the session state before redirecting to the results page. Change the code to match 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("ModelSelected", e.Item.Cells(1).Text)
        Session.Add("BrandSelected", e.Item.Cells(2).Text)
        Response.Redirect("results.aspx")
    End If
End Sub

  • Recall that e.Item.Cells(1) refers to column 1 of the row in the data grid that was clicked. This is where we have the model name.
  • Note that numbering starts at 0, so that column 0 is the one containing the View buttons.
  • Column 2 is the last column in the data grid and is where we have the brand name.
  • The results.aspx page is the same one that the cars.aspx page sends the user to when a particular car is selected. Here we redirect to the same results page for displaying the detailed information on the selected car, identified by model (ProdName) and brand (CategoryName).

Testing Your App

  • 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 all of the pages that use this.
  • Be sure to check that you can look up a product (car) by starting at the brands page where you select a brand of car, then getting redirected to the cars page where you select a model of car, and finally getting redirected to the results page where you see the details on this car.
  • 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.

Back to the main page for ASP .NET Web Apps



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