CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

 Schoology Facebook        Search CIS Site      Tutorials

Cars App 2



Overview



What do we want to create here? The idea is to add functionality to the previous web app, larger e-commerce web app 1. Just add on to that web app (or a copy of that web app). In particular, we add the following features: First, we add product attributes. For example, the customer might select the attribute red in the color category for a particular product (car). Or, the customer might select leather for the seat covering category. We can have as many such categories as we wish. Second, we add a shopping cart and the associated checkout process. What good is an e-commerce app unless we can buy things?

Parts of the design and implementation of this web app are adapted from the book .NET E-Commerce Programming by Mike Gunderloy and Noel Jerke, Sybex (2002). Although this book appears to be out of print, get a copy if you can find one as it gives several rather complete e-commerce apps as examples, including many features not shown here (such as the separate management app mentioned below).

Let's start by taking a tour in pictures of the new web app.

  • The home page is shown in this screen shot. It looks much the same as before but notice the addition of a link for the shopping cart.
  • The brands link takes us to a screen for choosing the brand (model) of car from a drop-down list. This is the same as in the previous app.
  • Thus the next step is obviously to select a particular brand.
  • This brings us to the cars page where we select a particular model of car.
  • Doing so brings us to the selected product (Results) page where we can see the details about this product, things such as the price and description.
  • In addition, this page has us choose the attributes in each available category so as to customize the car to our wishes. We can even specify that we want more than one of this particular car by using the quantity drop-down list. (If we would choose a quantity of 3, for example, all 3 cars would have the same color and other customizable features. If we want 3 of the same model, but with different colors and other features, then we should select a quantity of 1, add that car to the shopping cart, then go to the brands and cars pages to choose the same car. This time we would customize it to a different color, etc.)
  • When you select a customizable feature, a drop-down list appears giving the possible values for this feature.
  • Once we have customized each available feature, the Add to Cart button appears. Note that the chosen attributes are added to the description box so that we can easily see what we have chosen.
  • If we decide to do so, the next step is to click on the "Add to Cart" button. This brings us to the cart page. Here we can see many of the details about every item that we have addded to the shopping cart, including brand, model, quantity, price, total cost, and the attributes that we selected for the customizable features. The description is not shown because of limited space. This screen also gives you the total number of items in the cart as well as the subtotal (without tax and shipping).
  • On this page we can click on the Remove button for any particular item. This removes that row from the cart.
  • There is also an "Empty the Cart" button which is used to remove all of the items in the shopping cart. The result is an empty cart.
  • You can return to the brands page (or use search) to look up additional cars and add them to your shopping cart. The cart shows up to 3 items per screen with paging so that you can easily click on Next to see the next group of items.
  • Of course, the cart page also has a button labelled "Buy Items in Cart". Clicking this takes you to the shipping page. This page uses required field validators to force you to enter data for all required fields.
  • The shipping page also uses a custom validator that forces you to select one of the states from the pull-down.
  • Regular expression validators are also used on the text boxes so that you cannot enter unusual characters into these.
  • The next step is to click the submit button on the shipping page. This takes you to the payment page where you enter your credit card data.
  • After submitting your payment information (assuming that your credit card payment was accepted), the order confirmation page is shown and this particular shopping trip is over. However, the user can click on Brands or Search, add cars to a new shopping cart, and do another purchase.
  • The other pages in the new web app look and function much like they did in the previous web app.
    • The search page lets you look up cars based on a target search string.
    • The search results page shows the matching items in a data grid with paging (in case all of the items don't fit on one page). Clicking on the View button in a row of this grid takes you to the selected product page that was described above.
  • You might wonder how orders are processed. The assumption here is that there is a separate management application used by those who run the web site. This management app allows a manager to see the shipping and payment information and lets the manager clear out the credit card information once the order is processed. (It is not good to keep credit card information longer than necessary since attackers love to steal this!)
  • An alternative design would be to have some sort of automatic processing of orders so that human intervention is not normally required.

Database Work



Product Table

  • The Product table from the previous web app should work fine.

Category Table

  • This table, too, should work unchanged.

CategoryProduct Table

  • This table will work unchanged unless you added new car data to the Product table.
  • If you did add new data to the Product table, then add rows to this table to indicate which of the new cars belong in which categories. (Since each category is a car manufacturer, a given model of car should only belong to one category. With products other than the cars you might want a particular product to belong to more than one category.)

AttributeCategory Table

  • This table is used to specify an ID number and a name for each category of attributes for our products (cars).
  • We use just 2 categories: color and seat covering. Then the user might choose the red attribute for color and the leather attribute for seat covering, for example.
  • Right click on Tables in Server Explorer and select Add New Table.
  • Then design this table as shown below.
Column Name Data Type Allow Nulls
AttributeCategoryNum int no
AttributeCategoryName varchar(16) no

  • Right click on the margin in front of the AttributeCategoryNum field and set it to be the primary key field.
  • Also set the properties shown at the bottom to make this same field an identity field so that the first value starts at 1, the next is 2, etc.
  • Do File, Save to save the table, nameing in AttributeCategory.
  • You can then close the table design window.
  • Then right click on the AttributeCategory table in Server Explorer and select Open Table Data.
  • Add the following data on our 2 categories:
AttributeCategoryNum AttributeCategoryName
1 Color
2 Seat Covering

Attribute Table

  • This table contains the ID numbers and names for each attribute (such as red, silver, purple, leather, cloth, etc.) as well as the AttributeCategoryNum identifying which category each of these attributes belong to. (It may be obvious to us that red, silver, and purple belong to the Color category while leather and cloth belong to the Seat Covering category, but we have to lay this out in our database.)
  • Add a new table and design it like this:
Column Name Data Type Allow Nulls
AttributeNum int no
AttributeName varchar(16) no
AttributeCategoryNum int no

  • Make AttributeNum to be the primary key field.
  • Also make this an identity field.
  • Save the table under the name Attribute.
  • Close this window and use Open Table Data on the new table.
  • Then add the following data to it:
AttributeNum AttributeName AttributeCategoryNum
1 Cinnamon 1
2 Silver 1
3 Jet Black 1
4 Cream 1
5 Aqua 1
6 Red 1
7 Sky Blue 1
8 Purple 1
9 Leather 2
10 Plastic 2
11 Cloth 2

ProductAttribute Table

  • This table is used to link the Attribute and Product tables.
  • This table only uses 2 fields, ProdNum and AttributeNum, which together form the (combination) primary key.
  • If, for example, the table contains a row of data consisting of 2, 6 this indicates that product 2 (the Tornado model of car) can have attribute 6 (Red). Of course, the Attribute table tells us that attribute 6 has name Red and is in the category with ID number 1. The AttributeCategory table then tells us that category 1 has name Color.
  • Add a new table and design it like this:
Column Name Data Type Allow Nulls
ProdNum int no
AttributeNum int no

  • To make both fields to be a combination primary key, click on the margin in front of the first field, then use CTRL right click on the margin in front of the second field and select Set Primary Key.
  • Save your table under the name ProductAttribute.
  • Then add the following data:
ProdNum AttributeNum
1 1
1 2
1 3
1 4
1 7
1 9
1 11
2 1
2 3
2 4
2 6
2 8
2 9
2 11
3 2
3 3
3 4
3 5
3 6
3 7
3 10
3 11
4 4
4 5
4 7
6 1
5 2
5 3
5 4
5 6
5 8
5 9
5 11
6 2
6 3
6 4
6 5
6 7
7 2
7 3
7 4
7 5
7 7
8 2
8 3
8 4
8 6
8 8
8 9
8 11
9 4
9 6
9 8
10 4
10 5
10 6
10 7
10 9
10 10
10 11
11 4
11 5
11 6
11 7
11 9
11 10
11 11
12 4
12 5
12 6
12 7
12 10
12 11
13 1
13 4
13 6
13 10
13 11
14 2
14 4
14 5
14 6
14 7
14 9
14 10
14 11

  • Note that not all color attributes are available for all cars. For example, from the above table we can see that car 2 (the Tornado) has available colors 1, 3, 4, 6, an 8 out of possible colors 1 through 8. (The names of these colors, of course, can be found in the Attribute table.)
  • The same car, the Tornado, has available attributes 9 and 11 for the type of seat covering out of possible values of 9, 10, and 11.
  • In fact, some products may have no attributes available for a particular category. For example, car 4 (the Famtastic) has possible attributes of 4, 5, and 7 only. These are colors since the AttributeCategoryNum is 1 for each of these. Thus there are no choices of attributes for the seat covering category. Apparently this car comes with some standard seat covering that cannot be changed.

Basket Table

  • This table is used to hold general information about the shopping basket.
  • A separate BasketItem table will hold information about each item in the shopping basket.
  • Design the table as shown below.
Column Name Data Type Allow Nulls
BasketNum int no
ShopperNum int no
BasketNumItems int no
BasketOrderPlaced int no
BasketSubtotal money no
BasketTotal money no
BasketShipping money no
BasketTax money no

  • Make BasketNum the primary key.
  • Also make it an identity field.
  • Note that the BasketOrderPlaced field value indicates whether the order has been placed (value 1) or not (value 0).
  • Set up all fields except the first 2 to have a default value of 0. This is done by click on the margin in front of a particular field and then putting 0 into the property at the bottom of the screen labelled Default Value or Binding.
  • Save this table under the name Basket.
  • No data is added to the Basket table ahead of time. Our e-commerce app will add data when users create shopping baskets.

BasketItem Table

  • This table is used to hold information about an individual product in the shopping basket.
  • Design the table as shown below.
Column Name Data Type Allow Nulls
BasketItemNum int no
BasketNum int no
ProdNum int no
BasketItemPrice money no
BasketItemProdName varchar(24) no
BasketItemQuantity int no
BasketItemAttributes varchar(64) yes

  • Make BasketItemNum the primary key.
  • Also make it an identity field.
  • Give a default value of 0 to the BasketItemPrice and BasketItemQuantity fields.
  • Save this table under the name BasketItem.
  • No data is added to the BasketItem table ahead of time. Our e-commerce app will add data when users add products to their shopping baskets.
  • To give a better idea of what this data might look like, this Word file contains a sample of data that might be stored in this table. Remember, do not add this data to your table manually. All data is added to this table automatically by the e-commerce app.
  • In this sample data we can see, for example, that basket 4 has basket items 6 and 7, which hold 1 Kangaroo car and 3 Tornados, respectively.
  • Pay careful attention to how the attributes are stored. The Kangaroo in basket item 6 has an attributes field value of "Color,1,Red,6". This indictates category 1 (Color) and attribute 6 (Red) for this car. The attributes field value for the 3 identical Tornados is "Color,1,Jet Black,3;Seat Covering,2,Leather,9". This indicates that these have been customized to have Jet Black color (category 1 is color and color 3 is Jet Black) and Leather for the seat covering.
  • Note how the groups are separated by semicolons, while the 4 items in a group are separated by commas. We will need to process this attributes string later in the VB code for our e-commerce app.

Shopper Table

  • This table is used to hold the name and contact information on each shopper.
  • We might keep this data long term since it would allow us to send email or paper mail advertising products to our customers. Keeping this data would also enable us to implement user profiles (so that a user could submit a password and have the user's name, address, etc. automatically available). We skip user profiles here so as to keep this app from being overly long to present.
  • Design the table as follows:
Column Name Data Type Allow Nulls
ShopperNum int no
ShopperFirstName varchar(24) yes
ShopperLastName varchar(32) no
ShopperStreet varchar(32) yes
ShopperCity varchar(32) yes
ShopperState varchar(2) yes
ShopperZipCode varchar(15) yes
ShopperPhone varchar(30) yes
ShopperEmail varchar(48) yes

  • Make ShopperNum the primary key.
  • Also make it an identity field.
  • Save this table under the name Shopper.
  • No data is added to this table ahead of time. Our e-commerce app will add data when a shopper checks out.

Order Table

  • This table is used to hold the name and contact information on the shopper who is placing an order along with the date the order is placed and the basket ID number.
  • Design the table as follows:
Column Name Data Type Allow Nulls
OrderNum int no
ShopperNum int no
BasketNum int no
OrderFirstName varchar(24) yes
OrderLastName varchar(32) yes
OrderStreet varchar(32) yes
OrderCity varchar(32) yes
OrderState varchar(2) yes
OrderZipCode varchar(15) yes
OrderPhone varchar(30) yes
OrderEmail varchar(48) yes
OrderDateOrdered datetime no

  • Make OrderNum the primary key.
  • Also make it an identity field.
  • Save this table under the name Order.
  • Make the BasketNum field to be a unique key. This is to prevent the possibility of having two or more orders for the same basket. Here are the steps to follow in creating this unique constraint:
    • In the table definition window, click on the margin in front of the BasketNum field.
    • Click on Table Designer, Indexes/Keys (or click the Indexes/Keys button).
    • There should already be an entry named PK_Order. If you check its Columns property, you will see that OrderNum is listed there. That says that OrderNum is the primary key.
    • Click the Add button.
    • Click in the box to the right of the Columns property. You will then see a "..." button. Click on it to get the screen for choosing the column. Choose BasketNum, of course, and click OK.
    • Click in the box to the right of the Type property and choose Unique Key from the drop-down list.
    • Click on Close.
    • Save your table again since you have just made a change to it.
  • No data is added to this table ahead of time. Our e-commerce app will add data when a shopper checks out.
  • Although the address data is the same as that stored in the Shopper table, we might periodically purge old data from the Order table, whereas the Shopper table data is likely to be kept around for a longer time (so that we can send promotions to our customers and so that the customers might have user profiles).

Payment Table

  • This table is used to hold a customer's credit card information as well as the order number so as to link the credit card information to a particular order.
  • We plan to keep the information in this table only as long as necessary in order to verify the credit card information and process the order. This is to reduce our exposure to thieves interested in stealing credit card information.
  • Design the table as follows:
Column Name Data Type Allow Nulls
PaymentNum int no
OrderNum int no
PaymentCardType varchar(16) no
PaymentCardNumber varchar(30) no
PaymentExpirationDate varchar(24) no
PaymentCardOwnerName varchar(64) no

  • Make PaymentNum the primary key.
  • Also make it an identity field.
  • Save this table under the name Payment.
  • Make OrderNum a unique key much like above. This is to prevent the possibility of having two or more sets of payment information for the same order.
  • No data is added to this table ahead of time. Our e-commerce app will add data when a shopper checks out.

Relationships Between Tables

  • We should next set up the appropriate relationships between tables based on where we have foreign keys present.
  • For a reminder on how to set up this type of relationship, look at the directions in the survey app.
  • Set up the following relationships. In each case, check the screen shot to be sure you have the same thing. Detailed directions are only given for the first relationship.
    • The BasketItem table contains BasketNum as a foreign key since BasketNum is the primary key in the Basket table. Here are the details on how to set up this relationship:
      • Begin by opening the table definition for the table containing the foreign key. Thus, in this case, you would right click on the BasketItem table and select Open Table Definition.
      • Click on Table Designer, Relationships (or click the Relationships button). This gives you a dialog box showing the relationships and their properties.
      • Click the Add button.
      • Then click in the box to the right of the Tables and Columns Specification property. A button labelled with "..." then shows up. Click on it to get the Tables and Columns dialog box.
      • First select the other table in the left column, the so-called primary key table. In this case, the table containing the primary key is the Basket table.
      • Make the BasketNum primary key to be the only field displayed underneath this. If there are other fields shown, click in its box and use the pull-down to change the entry to None. That will clear out the unwanted value.
      • The righthand column should already have the correct foreign key table shown, namely the BasketItem table. The field shown under it should be the BasketNum field, the one that matches with the left column. Both versions of BasketNum should be on the same line.
      • Click OK when you have this set up correctly.
    • The BasketItem table also contains foreign key ProdNum since ProdNum is the primary key in the Product table. Check this screen shot showing this relationship. When finished with the BasketItem table, be sure to save it.
    • Next, open the Basket table. It contains foreign key ShopperNum since ShopperNum is the primary key in the Shopper table. Check this screen shot showing this relationship. When finished with the Basket table, be sure to save it.
    • Now open the Order table. It contains ShopperNum as a foreign key since ShopperNum is the primary key in the Shopper table. Check this screen shot showing this relationship.
    • The Order table also contains BasketNum as a foreign key since BasketNum is the primary key in the Basket table. Check this screen shot showing this relationship. When finished with the Order table, be sure to save it.
    • Open the Payment table. It contains foreign key OrderNum since OrderNum is the primary key in the Order table. Check this screen shot showing this relationship. When finished with the Payment table, be sure to save it.
    • Next, open the ProductAttribute table. It contains foreign key ProdNum, which is the primary key in the Product table. Check this screen shot showing this relationship.
    • The ProductAttribute table also contains foreign key AttributeNum since AttributeNum is the primary key in the Attribute table. Check this screen shot showing this relationship. When finished with the ProductAttribute table, be sure to save it.
    • Finally open the Attribute table. It contains AttributeCategoryNum as a foreign key since AttributeCategoryNum is the primary key in the AttributeCategory table. Check this screen shot showing this relationship. When finished with the Attribute table, be sure to save it.

Work in Visual Studio



GlobalProcedures.vb

  • When a user first reaches our e-commerce site (probably at the home page, though possibly at some other page if the person has a bookmark to it) we wish to assign the user a shopper number.
  • We thus want to run the same code at the start of most of our pages. This code should check to see if the user has a shopper number. If not, a new shopper number should be assigned.
  • To insert a new row into the shopper table and return the new shopper number, we use the following stored procedure. Create it in Server Explorer and save it once the code looks like this.

CREATE PROCEDURE db_datareader.NewShopper
AS
    INSERT INTO Shopper(ShopperLastName) VALUES('')
    SELECT @@IDENTITY
    RETURN

  • Notice that the NewShopper stored procedure simply inserts a row with an empty string as the last name. (The other fields will be NULLS.) NewShopper then returns @@IDENTITY, the value from the identity field, which is ShopperNum for this table. Thus the new shopper number (1 more than the last one in this table) is returned.
  • On all of our web forms except Shipping.aspx, Payment.aspx, and Confirm.aspx make sure you have a label with ID ShopperErrorLabel and text (in red) "Error on page". Have the label's Visible property start off as False. We will make this label visible if we are somehow not able to handle the shopper number on a given page.
  • Next we need the to add a GlobalProcedures.vb file to our project. We will put a procedure in it that calls our NewShopper stored procedure. Right click on your project in Solution Explorer. Select Add, New Item, Code File. Then fill in GlobalProcedures.vb as the name for this file.
  • Place the following procedure into your new file.

Module GlobalProcedures

    Friend Sub CheckShopperNum(ByVal Session As HttpSessionState, ByRef ErrorFlag As Integer)
        Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
        Dim myCommand As SqlClient.SqlCommand = Nothing
        Dim SessionShopperNum As String

        ErrorFlag = 0

        Try
            SessionShopperNum = Session("ShopperNum")
            If (SessionShopperNum Is Nothing) OrElse (SessionShopperNum = "0") Then
                Dim connectionString As String = _
                    ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
                myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
                myCommand = New SqlClient.SqlCommand()
                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.Connection = myDatabaseConnection
                myCommand.CommandText = "NewShopper"

                myDatabaseConnection.Open()
                Session("ShopperNum") = CType(myCommand.ExecuteScalar, String)
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If
        Catch exception As System.Data.SqlClient.SqlException
            ErrorFlag = 1
        Catch exception As Exception
            ErrorFlag = 1
        Finally    'Do cleanup tasks here:
            myCommand = Nothing

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
        End Try
    End Sub

End Module

  • As usual, make the connection strings name match the one in your Web.config file, not the one shown above.
  • The above code first checks to see if ShopperNum is stored in the session state. If so it does nothing further as the user already has a shopper number. If not, the code manually connects to the database and runs out NewShopper stored procedure. The returned new shopper number is stored in the session state. If the code raises an exception the ErrorFlag parameter is set to 1. The calling procedure can then use this to make the ErrorLabel visible.
  • Adjust the Page_Load procedures for all of our existing web forms (except Shipping, Payment, and Confirm) so that they start out by calling CheckShopperNum as follows.

Dim ErrorFlag As Integer

ShopperErrorLabel.Visible = False
CheckShopperNum(Session, ErrorFlag)
If ErrorFlag = 1 Then
    ShopperErrorLabel.Visible = True
End If

Code-Behind File for the Results Form

  • Your results form should have everything on it that is needed, but check that the Add to Cart button's Visible property is set to False.
  • Also, Label2's ID should be changed to CategoryLabel so as to match with the code below.
  • We need to change the code in results.aspx.vb so that it implements the desired new functionality.
  • Thus it should allow the user to select the quantity and to customize the various features. The attributes drop-down list should only appear after the user selects a category of customizable feature. The Add to Cart button should only appear after all of the features have been customized.
  • Change the Page_Load procedure to match the following.
  • As usual, change the name of the connection to match what is in your Web.config.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim Price As Decimal
    Dim Model As String
    Dim Brand As String
    Dim Description As String
    Dim Attributes As String
    Dim ProdNum, NumAttributes As Integer
    Dim Item As New ListItem
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim myCommand As SqlClient.SqlCommand = Nothing
    Dim myReader As SqlClient.SqlDataReader = Nothing
    Dim ErrorFlag As Integer

    ShopperErrorLabel.Visible = False
    CheckShopperNum(Session, ErrorFlag)
    If ErrorFlag = 1 Then
        ShopperErrorLabel.Visible = True
    End If

    AddToCartButton.Visible = False   ' until we are sure we should enable it

    If Not IsPostBack Then
        Attributes = ""
        If Session.Count = 0 Then   ' No session items were supplied.
            ErrorLabel.Visible = True
        Else
            Try
                Model = Session("ModelSelected")
                Brand = Session("BrandSelected")
                Dim connectionString As String = _
                    ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
                myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
                myCommand = New SqlClient.SqlCommand()

                'Set up to use a stored procedure:
                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.Connection = myDatabaseConnection
                myCommand.CommandText = "SelectModelBrand"
                myCommand.Parameters.AddWithValue("@TargetModel", Model)
                myCommand.Parameters.AddWithValue("@TargetBrand", Brand)

                'Use an SqlDataReader to execute the stored procedure and get the results:
                myDatabaseConnection.Open()
                myReader = myCommand.ExecuteReader()
                If myReader.HasRows Then
                    myReader.Read()
                    Description = myReader.GetValue(0)
                    ProductDescriptionBox.Text = Description
                    ProductDescriptionBox.Visible = True
                    Session.Add("Description", Description)
                    Price = myReader.GetValue(1)
                    PriceLabel.Text = "Price:  " & String.Format("{0:C}", Price)
                    PriceLabel.Visible = True
                    Session.Add("Price", Price)
                    BrandLabel.Text = "Selected Brand: " & Brand
                    BrandLabel.Visible = True
                    ModelLabel.Text = "Selected Model: " & Model
                    ModelLabel.Visible = True
                    ProdNum = myReader.GetValue(2)
                    Session.Add("ProdNum", ProdNum)
                Else
                    ErrorLabel.Visible = True
                End If
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            Catch exception As System.Data.SqlClient.SqlException
                ErrorLabel.Visible = True
            Catch exception As Exception
                ErrorLabel.Visible = True
            Finally    'Do cleanup tasks here:
                myCommand = Nothing

                If Not myReader Is Nothing AndAlso _
                    Not myReader.IsClosed Then
                    myReader.Close()
                End If

                myReader = Nothing

                If Not myDatabaseConnection Is Nothing AndAlso _
                    myDatabaseConnection.State = ConnectionState.Open Then
                    myDatabaseConnection.Close()
                    myDatabaseConnection.Dispose()
                End If

                myDatabaseConnection = Nothing
            End Try

            Try
                Item.Text = "*** Make a choice ***"
                Item.Value = 0
                CategoryDropDownList.Items.Add(Item)

                Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
                myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
                myCommand = New SqlClient.SqlCommand()

                'Set up to use a stored procedure:
                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.Connection = myDatabaseConnection
                myCommand.CommandText = "SelectCategories"
                myCommand.Parameters.AddWithValue("@TargetProdNum", ProdNum)

                'Use an SqlDataReader to execute the stored procedure and get the results:
                myDatabaseConnection.Open()
                myReader = myCommand.ExecuteReader()
                While (myReader.Read())
                    Item = New ListItem
                    Item.Text = myReader.GetString(0)  'AttributeCategoryName
                    Item.Value = myReader.GetValue(1)  'AttributeCategoryNum
                    Item.Selected = False
                    CategoryDropDownList.Items.Add(Item)
                End While
                myReader.Close()

                NumAttributes = CategoryDropDownList.Items.Count - 1
                Session.Add("NumAttributes", NumAttributes)
                If NumAttributes > 0 Then
                    CategoryLabel.Visible = True   ' Had to rename Label2 as CategoryLabel
                    CategoryDropDownList.Visible = True
                Else
                    CategoryLabel.Visible = False
                    CategoryDropDownList.Visible = False
                    AddToCartButton.Visible = True
                End If

                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            Catch exception As System.Data.SqlClient.SqlException
                ErrorLabel.Visible = True
                CategoryLabel.Visible = False
                CategoryDropDownList.Visible = False
                AddToCartButton.Visible = False
            Catch exception As Exception
                ErrorLabel.Visible = True
                CategoryLabel.Visible = False
                CategoryDropDownList.Visible = False
                AddToCartButton.Visible = False
            Finally
                myCommand = Nothing

                If Not myReader Is Nothing AndAlso _
                    Not myReader.IsClosed Then
                    myReader.Close()
                End If

                myReader = Nothing

                If Not myDatabaseConnection Is Nothing AndAlso _
                    myDatabaseConnection.State = ConnectionState.Open Then
                    myDatabaseConnection.Close()
                    myDatabaseConnection.Dispose()
                End If

                myDatabaseConnection = Nothing
            End Try
        End If
    End If
End Sub

  • One of the first things that the above procedure does is to retrieve the brand and model selected from the session state.
  • It then uses these as parameters to pass to a stored procedure, SelectModelBrand, which is used to look up the details on this car. In particular, we get back the description, price, and product number. We display all but the product number on the results page and place all 3 values in the session state so that they will be available when we again need them.
  • This is almost the same stored procedure and mostly the same code so far that was used in the previous web app's result page. The one change to the stored procedure is that it must also select the product number.
  • Thus, in Server Explorer open the existing SelectModelBrand stored procedure and change it to the following. When you save it, the changes are made in the actual stored procedure in SQL Server.

ALTER PROCEDURE db_datareader.SelectModelBrand 
(
    @TargetModel varchar(24),
    @TargetBrand varchar(24)
)
AS
    SET NOCOUNT ON
    SELECT Product.ProdDescription, Product.ProdPrice, Product.ProdNum
    FROM Product INNER JOIN CategoryProduct 
    ON Product.ProdNum = CategoryProduct.ProdNum
    INNER JOIN Category ON CategoryProduct.CategoryNum = Category.CategoryNum
    WHERE (Product.ProdName = @TargetModel) AND (Category.CategoryName = @TargetBrand)
    RETURN

  • The above VB code has a second, Try...Catch block. It's job is to populate the drop-down list for the categories of customizable features.
  • The code runs a stored procedure to get the needed category names for the drop-down list.
  • Make a new stored procedure in the usual way. The code for it follows:

CREATE PROCEDURE db_datareader.SelectCategories
    (
    @TargetProdNum int
    )
AS
    SET NOCOUNT ON
    SELECT DISTINCT AttributeCategory.AttributeCategoryName, AttributeCategory.AttributeCategoryNum
    FROM Attribute INNER JOIN AttributeCategory 
    ON Attribute.AttributeCategoryNum = AttributeCategory.AttributeCategoryNum
    INNER JOIN ProductAttribute ON Attribute.AttributeNum = ProductAttribute.AttributeNum
    WHERE (ProductAttribute.ProdNum = @TargetProdNum) 
    ORDER BY AttributeCategory.AttributeCategoryName
    RETURN

  • Notice how the VB code uses variable Item of type ListItem to add one-by-one the items to the drop-down list, starting with the hard-coded "*** Make a choice ***".
  • The rest of the items are the category names retrieved from the database by the above stored procedure. Note that the stored procedure uses as an input parameter the ProdNum looked up by the previous stored procedure.
  • NumAttributes is assigned 1 less than the number of items in the drop-down list. This is because the first item in the list is not an attribute. Rather, it is "*** Make a choice ***". This value is saved in the session state so that it will be available later.
  • If there are no attributes, then we don't make the drop-down list visible. Instead, we just make the Add to Cart button visible as there are no customizable features for this product.
  • The next step is to add a procedure to handle things when the user selects a category from this drop-down list. Here is the code to add (to the same Results.aspx.vb file). Remember to adjust the connection string line in the usual manner.

Protected Sub CategoryDropDownList_SelectedIndexChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles CategoryDropDownList.SelectedIndexChanged

    Dim Item As New ListItem
    Dim AttributeName As String
    Dim AttributeNum As Integer
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim myCommand As SqlClient.SqlCommand = Nothing
    Dim myReader As SqlClient.SqlDataReader = Nothing

    AddToCartButton.Visible = False
    AttributeName = CategoryDropDownList.SelectedItem.Text
    AttributeNum = CType(CategoryDropDownList.SelectedItem.Value, Integer)

    If Not Equals(AttributeName, "*** Make a choice ***") Then
        Session.Add("AttributeName", AttributeName)
        Session.Add("AttributeNum", AttributeNum)
        ValueDropDownList.Items.Clear()   'Make sure nothing remains in this list.

        Try
            Item.Text = "*** Make a choice ***"
            Item.Value = 0
            ValueDropDownList.Items.Add(Item)

            Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            myCommand = New SqlClient.SqlCommand()

            'Set up to use a stored procedure:
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myDatabaseConnection
            myCommand.CommandText = "SelectCategoryValues"
            myCommand.Parameters.AddWithValue("@TargetCategoryNum", AttributeNum)
            myCommand.Parameters.AddWithValue("@TargetProdNum", Session("ProdNum"))

            'Use an SqlDataReader to execute the stored procedure and get the results:
            myDatabaseConnection.Open()
            myReader = myCommand.ExecuteReader()
            While (myReader.Read())
                Item = New ListItem
                Item.Text = myReader.GetString(0)  'AttributeCategoryName
                Item.Value = myReader.GetValue(1)  'AttributeCategoryNum
                Item.Selected = False
                ValueDropDownList.Items.Add(Item)
            End While
            myReader.Close()

            If ValueDropDownList.Items.Count > 1 Then
                ValueLabel.Visible = True
                ValueDropDownList.Visible = True
            Else   ' This case should not occur.
                ValueLabel.Visible = False
                ValueDropDownList.Visible = False
            End If

            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As Exception
            ErrorLabel.Visible = True
            ValueLabel.Visible = False
            ValueDropDownList.Visible = False
            AddToCartButton.Visible = False
        Finally
            myCommand = Nothing

            If Not myReader Is Nothing AndAlso _
                Not myReader.IsClosed Then
                myReader.Close()
            End If

            myReader = Nothing

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
        End Try
    Else   ' No selection was made in the drop-down list.
        ValueDropDownList.Visible = False
        ValueLabel.Visible = False
    End If
End Sub

  • The first thing that the above procedure does is to get the AttributeName and AttributeNum from CategoryDropDownList.
  • As long as the user selected an actual category (instead of "*** Make a choice ***"), we proceed.
  • The AttributeName and AttributeNum values are saved in session state so that they will be available after a postback. (See the ValueDropDownList_SelectedIndexChanged procedure below.)
  • The code next runs a stored procedure to obtain from the database the names of the attribute values for the selected attribute category and product.
  • Thus, create the following stored procedure:

CREATE PROCEDURE db_datareader.SelectCategoryValues 
    (
    @TargetCategoryNum int,
    @TargetProdNum int
    )
AS
    SET NOCOUNT ON
    SELECT DISTINCT Attribute.AttributeName, Attribute.AttributeNum FROM Attribute
    INNER JOIN ProductAttribute ON Attribute.AttributeNum = ProductAttribute.AttributeNum
    WHERE (Attribute.AttributeCategoryNum = @TargetCategoryNum) 
    AND (ProductAttribute.ProdNum = @TargetProdNum)
    ORDER BY Attribute.AttributeName
    RETURN

  • Note that this stored procedure returns the name and ID number for each attribute for the desired product and category of attribute.
  • The VB code uses our data reader to add the name of each applicable attribute to ValueDropDownList.
  • If there are no items in this list other than "*** Make a choice ***", we do not make it visible. Because of the way we generated the list of categories, this case should not occur. However, it is handled here just in case we missed something!
  • Next, we add code to handle things when the user selects a particular attribute value. Use the following:

Protected Sub ValueDropDownList_SelectedIndexChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles ValueDropDownList.SelectedIndexChanged

    Dim Top, k, AttributeNum, ValueNum As Integer
    Dim Found As Boolean
    Dim AttributeName, ValueName As String
    Dim TempStr, NewStr, ItemsStr, NewDescription, Attributes, Description As String
    Dim AttributesArray As String() = Nothing
    Dim ItemsArray As String() = Nothing
    Dim delimStr As String = ";"
    Dim DelimStr2 As String = ","
    Dim delimiter As Char() = delimStr.ToCharArray()
    Dim delimiter2 As Char() = DelimStr2.ToCharArray()

    ValueName = ValueDropDownList.SelectedItem.Text
    ValueNum = CType(ValueDropDownList.SelectedItem.Value, Integer)

    If Not Equals(ValueName, "*** Make a choice ***") Then
        Found = False
        TempStr = ""
        Description = Session.Item("Description")
        Attributes = Session.Item("Attributes")
        AttributeName = Session.Item("AttributeName")
        AttributeNum = Session.Item("AttributeNum")

        'Adjust the Attributes string:
        If Not Attributes = "" Then
            AttributesArray = Attributes.Split(delimiter)
            Top = AttributesArray.GetUpperBound(0)
            For k = 0 To Top
                ItemsArray = AttributesArray(k).Split(delimiter2)
                If ItemsArray(0) = AttributeName Then  ' record new value here
                    ItemsArray(2) = ValueName
                    ItemsArray(3) = ValueNum
                    Found = True
                    ItemsStr = String.Join(",", ItemsArray)
                    If Not TempStr = "" Then
                        TempStr &= ";"
                    End If
                    TempStr &= ItemsStr
                ElseIf (Not Found) And (ItemsArray(0) > AttributeName) Then
                    NewStr = AttributeName & "," & AttributeNum & "," _
                        & ValueName & "," & ValueNum
                    If Not TempStr = "" Then
                        TempStr &= ";"
                    End If
                    TempStr &= NewStr
                    TempStr = TempStr & ";" & AttributesArray(k)
                    Found = True
                Else
                    If Not TempStr = "" Then
                        TempStr &= ";"
                    End If
                    TempStr &= AttributesArray(k)
                End If
            Next
        End If
        If Not Found Then   ' record new value at end of string
            If Not TempStr = "" Then
                TempStr &= ";"
            End If
            TempStr = TempStr & AttributeName & "," & AttributeNum & "," _
                & ValueName & "," & ValueNum
        End If

        Attributes = TempStr
        Session.Add("Attributes", Attributes)

        ' Loop through the entries in Attributes to set up NewDescription:
        AttributesArray = Attributes.Split(delimiter)
        Top = AttributesArray.GetUpperBound(0)
        NewDescription = Description
        For k = 0 To Top
            ItemsArray = AttributesArray(k).Split(delimiter2)
            NewDescription &= vbCrLf
            NewDescription &= ItemsArray(0)
            NewDescription &= ": "
            NewDescription &= ItemsArray(2)
        Next
        Session.Add("NumAttributesCustomized", Top + 1)
        ProductDescriptionBox.Text = NewDescription
    End If

    ' If the user has customized all of the features, show the "Add to Cart" button.
    If Session("NumAttributesCustomized") = Session("NumAttributes") Then
        AddToCartButton.Visible = True
    Else
        AddToCartButton.Visible = False
    End If
End Sub

  • The purpose of the above code is to save all of the attribute information in the session state, to show this attribute information in the product description box, and to make the Add to Cart button visible if the user has customized all of the attribute categories for this product.
  • The attribute information is kept in a string such as this example:

Color,1,Purple,8;Seat Covering,2,Cloth,11

  • Note that each set of 4 items is separated from the next set by a semicolon.
  • The items inside of each set are separated by commas.
  • The ordering of the 4 items is always the same: category name, category number, attribute name, attribute number.
  • This format makes it relatively easy to pick out the pieces of data that we need, plus it is flexible in that it allows us to handle a variable number of categories and attributes.
  • Our VB code above begins by extracting the attribute name (ValueName) from ValueDropDownList as well as the corresponding attribute nuber (ValueNum) from this list.
  • If the selected value is "*** Make a choice ***", then we skip most of the code and proceed to the end to check if all available categories have been customized. (If so, we make the Add to Cart button visible.)
  • Otherwise, we need to process the selected attribute value.
  • We look up in session state the recorded product description, attributes string (in the above format), selected attribute name, and the corresponding attribute number.
  • The purpose of the next section of code is to add the new attribute name and number (along with the selected category name and number) to the attribute string. The new attribute string is built up in TempStr and copied to the Attributes variable and session state once it is finished.
  • TempStr starts off as an empty string.
  • We use the Split method to put into AttributesArray the sets of items we get by splitting the Attributes string apart at the semicolons.
  • We then loop through this array to see if it already contains an entry for the selected category. If so, the user is trying to revise the attribute selected for this category (perhaps by changing the color of the car, for example). We set the Found flag to be true if we find such an entry and record the new attribute name and number at the same location in the AttributesArray.
  • Note that each time around this loop we do a second split, this time to break one set of items apart at the commas. This allows us to examine the 4 individual items: the category name, category number, attribute name, attribute number.
  • If we find the desired attribute name present, we record the new values in the appropriate 2 of the 4 items and then use a Join to paste the 4 pieces together into one string with commas between the pieces. This string is then appended to TempStr, with a semicolon preceding it if TempStr already has anything in it. Thus we gradually build up our revised attributes information in TempStr.
  • The ElseIf section inside of our loop is used to handle the case where we don't find the desired attribute name, but we have just reached an attribute name that is alphabetically larger than the attribute name for the data that we wish to add. In this case we manually concatenate the 4 pieces of data and append it to TempStr. Then we tack on the set of data for the alphabetically larger attribute name.
  • The Else section of this same loop is used in all other cases to simply append the same set of data taken from the AttributesArray to TempStr.
  • After the loop ends, if the Found flag is not true, then the new attribute data has not yet been added and needs to be concatenated to the end of TempStr. This could be either because we started with an empty Attributes string or because the selected category name (called AttributeName in the code) comes alphabetically after all of the category names already in the AttributesArray. In either case, we just manually concatenate the desired set of 4 items to the end of TempStr.
  • We then copy TempStr into the Attributes string and record the same data in the session state for later use.
  • We next loop through the Attributes data to extract the attributes information and append it in readable form to the product description.
  • This begins by splitting the Attributes string apart at the semicolons.
  • We loop through the resulting array and split each entry apart at the commmas.
  • For each entry we append the category and attribute names, in readable form, to the NewDescription string (which was initialized to contain the product description).
  • Thus by the end of the loop NewDescription contains the intial product description plus one or more lines of attribute information (such as "Color: Purple").
  • We then save in session state the number of categories that have been customized. This is 1 more than the top index used in the array (since indexing starts at 0).
  • NewDescription is copied into the product description text box.
  • If the number of categories customized then matches the number of categories (here somewhat misnamed as NumAttributes) then it is time to make the Add to Cart button visible; otherwise not.
  • The final procedure to add to the Results.aspx.vb file is one to handle the event of clicking on the Add to Cart button. You can generate the outline of this procedure by double clicking on the button on the web form while in Design view. Then adjust it to match the following (except that the connection string line must be adjusted as usual).

Protected Sub AddToCartButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles AddToCartButton.Click

    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim cmdCreateBasket = New System.Data.SqlClient.SqlCommand
    Dim cmdInsertBasketItem = New System.Data.SqlClient.SqlCommand

    Try
        Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
        myDatabaseConnection = New SqlClient.SqlConnection(connectionString)

        cmdCreateBasket.CommandText = "NewBasket"
        cmdCreateBasket.CommandType = System.Data.CommandType.StoredProcedure
        cmdCreateBasket.Connection = myDatabaseConnection
        cmdCreateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdCreateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ShopperNum", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdCreateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BasketNum", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

        cmdInsertBasketItem.CommandText = "InsertBasketItem"
        cmdInsertBasketItem.CommandType = System.Data.CommandType.StoredProcedure
        cmdInsertBasketItem.Connection = myDatabaseConnection
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BasketNum", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProdNum", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Price", _
            System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, _
            CType(19, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProdName", _
            System.Data.SqlDbType.Char, 24))
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Quantity", _
            System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
            CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Attributes", _
            System.Data.SqlDbType.VarChar, 64))

        Session.Add("Quantity", CType(QuantityDropDownList.SelectedItem.Value, Integer))
        If CType(Session("BasketNum"), String) = "" Then   ' need to make a new basket
            cmdCreateBasket.Parameters("@ShopperNum").Value = Session("ShopperNum")
            myDatabaseConnection.Open()
            cmdCreateBasket.ExecuteNonQuery()
            Session.Add("BasketNum", cmdCreateBasket.Parameters("@BasketNum").Value)
            myDatabaseConnection.Close()
        End If

        'Now we have a basket, either new or old.
        cmdInsertBasketItem.Parameters("@BasketNum").Value = Session("BasketNum")
        cmdInsertBasketItem.Parameters("@ProdNum").Value = Session("ProdNum")
        cmdInsertBasketItem.Parameters("@Price").Value = Session("Price")
        cmdInsertBasketItem.Parameters("@ProdName").Value = Session("ModelSelected")
        cmdInsertBasketItem.Parameters("@Quantity").Value = Session("Quantity")
        cmdInsertBasketItem.Parameters("@Attributes").Value = Session("Attributes")
        myDatabaseConnection.Open()
        cmdInsertBasketItem.ExecuteNonQuery()
        myDatabaseConnection.Close()
        myDatabaseConnection.Dispose()
    Catch exception As Exception
        ErrorLabel.Visible = True
    Finally
        If Not myDatabaseConnection Is Nothing AndAlso _
            myDatabaseConnection.State = ConnectionState.Open Then
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        End If

        myDatabaseConnection = Nothing
    End Try

    If ErrorLabel.Visible = False Then
        Response.Redirect("Cart.aspx")
    End If
End Sub

  • This code starts by getting the quantity desired of this product and recording it in the session state.
  • Then we check to see if a basket number exists in session state. If not, we have to create one for this shopper.
  • For this purpose we use a stored procedure named NewBasket. Create this stored procedure so that it matches the following:

CREATE PROCEDURE db_datareader.NewBasket 
    (
    @ShopperNum int,
    @BasketNum int OUTPUT
    )
AS
    INSERT INTO Basket(ShopperNum) VALUES(@ShopperNum)
    SELECT @BasketNum = @@IDENTITY 
    RETURN

  • This stored procedure inserts into the Basket table the shopper number passed in as a parameter. Then it returns the new basket number from the identity field.
  • Our above VB code fills in the current shopper number as the parameter to pass to this stored procedure.
  • It then manually executes the stored procedure and places the returned basket number into the session state.
  • At this point we have a basket number (either old or new).
  • Our code then calls another stored procedure named InsertBasketItem to insert the product information into the BasketItem table (or to update the appropriate entry in BasketItem if this exact product is already present for the current basket (shopping cart).
  • Create this stored procedure as follows:

CREATE PROCEDURE db_datareader.InsertBasketItem 
    (
    @BasketNum int,
    @ProdNum int,
    @Price money,
    @ProdName varchar(24),
    @Quantity int,
    @Attributes varchar(64)
    )
AS
    DECLARE @Current int
    SELECT @Current = COUNT(*) FROM BasketItem
        WHERE (BasketNum = @BasketNum) AND (ProdNum = @ProdNum) AND (BasketItemAttributes = @Attributes)
    IF @Current = 0   /* this product is not already in the basket */
        INSERT INTO BasketItem(BasketNum, ProdNum, BasketItemPrice, BasketItemProdName, 
		BasketItemQuantity, BasketItemAttributes)
        VALUES(@BasketNum, @ProdNum, @Price, @ProdName, @Quantity, @Attributes)
    ELSE   /* this exact product is already in the basket, so just update the quantity */
        UPDATE BasketItem SET BasketItemQuantity = BasketItemQuantity + @Quantity
        WHERE  (BasketNum = @BasketNum) AND (ProdNum = @ProdNum) AND (BasketItemAttributes = @Attributes)
    RETURN

  • All of the parameters to this stored procedure are input parameters.
  • Note how @Current is set up to count the number of rows in the BasketItem table that match the basket number, product number, and attributes string. This count should be 0 or 1. Either we already have this exact product (including the same choices for all of the customizable features) in this basket or we do not.
  • If this exact product is already present, we just update the quantity by adding on the new number to be purchased to the already existing quantity to be purchased.
  • Once our VB code executes this stored procedure, it redirects to the Cart.aspx page.
  • In order to execute the 2 stored procedures just shown, the VB code had to set up information about the parameters to be passed into and out of the stored procedure. This is because we were not using a data reader, data adapter, or some other high-level machinery to aid us. That is the reason for the Parameters.Add lines in the above VB code.

The Code-Behind File for the Cars Form

  • Look at your Cars.aspx.vb file.
  • As already mentioned, we have to call CheckShopperNum at the start of the Page_Load procedure.
  • Now that we have added the code to Results.aspx.vb for the Attributes string, we need to clear out that information in the code-behind file for the Cars form just before it redirects to the Results page. This is so that old data for a previous car is not used with the newly selected car.
  • The entire revised code for Page_Load is shown below. Remember to adjust the connection string line.
  • Note how before redirecting to the Results page, it puts the empty string into Attributes in session state and completely removes NumAttributes and NumAttributesCustomized from session state.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim TargetModel As String
    Dim ErrorFlag As Integer

    ShopperErrorLabel.Visible = False
    CheckShopperNum(Session, ErrorFlag)
    If ErrorFlag = 1 Then
        ShopperErrorLabel.Visible = True
    End If

    If Not IsPostBack Then
        Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
        Dim myCommand As SqlClient.SqlCommand = Nothing
        Dim myReader As SqlClient.SqlDataReader = Nothing

        Try
            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            myCommand = New SqlClient.SqlCommand()
            BrandLabel.Text = "Brand: " & Session("BrandSelected")
            CarsList.Items.Add("*** Make a choice ***")

            'Set up to use a stored procedure:
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myDatabaseConnection
            myCommand.CommandText = "SelectProdName"
            myCommand.Parameters.AddWithValue("@TargetBrand", Session("BrandSelected"))

            'Use an SqlDataReader to execute the stored procedure and
            'get the results into the drop down list:
            myDatabaseConnection.Open()
            myReader = myCommand.ExecuteReader()
            While (myReader.Read())
                CarsList.Items.Add(myReader.GetString(0))
            End While

            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As System.Data.SqlClient.SqlException
            ErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally    'Do cleanup tasks here:
            myCommand = Nothing

            If Not myReader Is Nothing AndAlso _
                Not myReader.IsClosed Then
                myReader.Close()
            End If

            myReader = Nothing

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
        End Try
    Else   ' We have a postback.
        TargetModel = CarsList.SelectedItem.Text
        If Not Equals(TargetModel, "*** Make a choice ***") Then
            Session.Add("ModelSelected", TargetModel)
            Session.Add("Attributes", "") _
            ' To be sure that attributes for another product are cleared
            Session.Remove("NumAttributes")
            Session.Remove("NumAttributesCustomized")
            Response.Redirect("Results.aspx")
        End If
    End If
End Sub

The Code-Behind File for the SearchResults Form

  • In the same way, the code-behind file Searchresults.aspx.vb must be modified just before it redirects to the Results page.
  • Here is the modified procedure. The rest of this file stays the same.

Protected Sub ResultsGridView_ItemCommand(ByVal Source As Object, _
    ByVal e As GridViewCommandEventArgs) Handles ResultsGridView.RowCommand

    If e.CommandName = "View" Then
        ' Find the index of the row where the user clicked the View button:
        Dim index As Integer = Convert.ToInt32(e.CommandArgument)
        ' Find the model and brand of the item in this row:
        Session.Add("ModelSelected", ResultsGridView.Rows(index).Cells(1).Text)
        Session.Add("BrandSelected", ResultsGridView.Rows(index).Cells(2).Text)
        Session.Add("Attributes", "")  'To clear any attributes from an old product
        Session.Remove("NumAttributes")
        Session.Remove("NumAttributesCustomized")
        Response.Redirect("Results.aspx")
    End If
End Sub

The Shopping Cart Form

  • The Cart.aspx form was already created for you. However, the label for the database error situation should be given the ID DatabaseErrorLabel so that it matches the code below.
  • Double click on each button to create the outline of a click handler for each. We will fill in the code for these shortly.

The Code-Behind File for the Shopping Cart Form

  • In the Cart.aspx.vb file set up the Page_Load procedure to look like the following.
  • Of course, change the name of the connection to match what is in your Web.config file.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim ErrorFlag As Integer
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim cmdCreateBasket As System.Data.SqlClient.SqlCommand = Nothing

    If Not IsPostBack Then
        ShopperErrorLabel.Visible = False
        CheckShopperNum(Session, ErrorFlag)
        If ErrorFlag = 1 Then
            ShopperErrorLabel.Visible = True
        End If

        Try
            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdCreateBasket = New System.Data.SqlClient.SqlCommand()

            cmdCreateBasket.CommandText = "NewBasket"
            cmdCreateBasket.CommandType = System.Data.CommandType.StoredProcedure
            cmdCreateBasket.Connection = myDatabaseConnection
            cmdCreateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdCreateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ShopperNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdCreateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BasketNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

            If CType(Session("BasketNum"), String) = "" Then   ' need to make a new basket
                cmdCreateBasket.Parameters("@ShopperNum").Value = Session("ShopperNum")
                myDatabaseConnection.Open()
                cmdCreateBasket.ExecuteNonQuery()
                Session.Add("BasketNum", cmdCreateBasket.Parameters("@BasketNum").Value)
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            cmdCreateBasket.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdCreateBasket = Nothing
        End Try

        UpdatePage()
    End If
End Sub

  • The above procedure does our usual CheckShopperNum so as to generate a new shopper number if the user doesn't already have one.
  • The next section checks to see if there is a shopping basket number. If not, it runs our NewBasket stored procedure to create a basket number. This is the same code that we used above on the results page when the user clicked the Add to Cart button. So, the user should already have a basket number, but we want to be sure that there is a basket number no matter how the user reached the shopping cart page.
  • Finally, the above code calls an UpdatePage procedure (which we write below) to place the appropriate information about the basket items on the shopping cart page.
  • In fact, the following is the code for this UpdatePage procedure. Go ahead and add it, adjusting the connection string line in the usual way.

Private Sub UpdatePage()
    Dim BasketNum, Quantity As Integer
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim myCommand As SqlClient.SqlCommand = Nothing
    Dim adapter As SqlClient.SqlDataAdapter = Nothing
    Dim dTable As DataTable = Nothing
    Dim cmdBasketQuantitySubTotal As System.Data.SqlClient.SqlCommand = Nothing

    If Session.Count = 0 Then   ' No session items were supplied.
        ErrorLabel.Visible = True
    Else
        Try
            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            dTable = New DataTable
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            myCommand = New SqlClient.SqlCommand()

            'Set up to get the basket data via a stored procedure:
            BasketNum = Session("BasketNum")
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.Connection = myDatabaseConnection
            myCommand.CommandText = "GetBasket"
            myCommand.Parameters.AddWithValue("TargetBasketNum", BasketNum)
            adapter = New SqlClient.SqlDataAdapter(myCommand)

            ' Put the basket data into dTable:
            myDatabaseConnection.Open()
            adapter.Fill(dTable)
            myDatabaseConnection.Close()

            If dTable.Rows.Count = 0 Then
                ItemsLabel.Text = "0 items in cart"
                CartGridView.Visible = False
                SubTotalLabel.Visible = False
                BuyButton.Visible = False
                EmptyButton.Visible = False
            Else
                AdjustAttributes(dTable)
                Session.Add("DataTable", dTable)  ' Save the table for easy paging.
                CartGridView.DataSource = dTable
                CartGridView.DataBind()
                CartGridView.Visible = True
                SubTotalLabel.Visible = True
                BuyButton.Visible = True
                EmptyButton.Visible = True

                ' Get the subtotal and the quantity of items in the cart (basket):
                cmdBasketQuantitySubTotal = New System.Data.SqlClient.SqlCommand
                cmdBasketQuantitySubTotal.CommandText = "BasketQuantitySubTotal"
                cmdBasketQuantitySubTotal.CommandType = System.Data.CommandType.StoredProcedure
                cmdBasketQuantitySubTotal.Connection = myDatabaseConnection
                cmdBasketQuantitySubTotal.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                    "@RETURN_VALUE", System.Data.SqlDbType.Int, 4, _
                    System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), _
                    CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
                cmdBasketQuantitySubTotal.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                    "@TargetBasketNum", System.Data.SqlDbType.Int, 4, _
                    System.Data.ParameterDirection.Input, False, CType(10, Byte), _
                    CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
                cmdBasketQuantitySubTotal.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                    "@Quantity", System.Data.SqlDbType.Int, 4, _
                    System.Data.ParameterDirection.Output, False, CType(10, Byte), CType(0, Byte), _
                    "", System.Data.DataRowVersion.Current, Nothing))
                cmdBasketQuantitySubTotal.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                    "@SubTotal", System.Data.SqlDbType.Money, 8, _
                    System.Data.ParameterDirection.Output, False, CType(19, Byte), CType(0, Byte), _
                    "", System.Data.DataRowVersion.Current, Nothing))

                cmdBasketQuantitySubTotal.Parameters("@TargetBasketNum").Value = BasketNum
                myDatabaseConnection.Open()
                cmdBasketQuantitySubTotal.ExecuteNonQuery()
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()

                SubTotalLabel.Text = "Subtotal (before tax & shipping): " & _
                    Format(cmdBasketQuantitySubTotal.Parameters("@SubTotal").Value, "Currency")
                Quantity = cmdBasketQuantitySubTotal.Parameters("@Quantity").Value
                If Quantity = 1 Then
                    ItemsLabel.Text = "1 item in cart"
                Else
                    ItemsLabel.Text = Quantity.ToString & " items in cart"
                End If
            End If
        Catch exception As System.Data.SqlClient.SqlException
            DatabaseErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
            ' ErrorLabel.Text = exception.Message   ' Uncomment this line for debugging.
        Finally
            dTable.Dispose()
            adapter.Dispose()
            myCommand.Dispose()

            If Not cmdBasketQuantitySubTotal Is Nothing Then
                cmdBasketQuantitySubTotal.Dispose()
            End If

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            dTable = Nothing
            adapter = Nothing
            myCommand = Nothing
            cmdBasketQuantitySubTotal = Nothing
        End Try
    End If
End Sub

  • This UpdatePage procedure first checks if there is any session state information. If not, it simply displays an error message in a label.
  • Otherwise, it goes on to try to look up and display the contents of the shopping cart. If this does not work, an exception is raised and an error label is made visible.
  • To retrieve the shopping cart data we use a selection type of stored procedure. Thus, create the following stored procedure:

CREATE PROCEDURE db_datareader.GetBasket 
    (
    @TargetBasketNum int
    )
AS
    SET NOCOUNT ON
    SELECT BasketItem.ProdNum, BasketItem.BasketItemProdName,
    BasketItem.BasketItemAttributes, BasketItem.BasketItemPrice,
    BasketItem.BasketItemQuantity, BasketItem.BasketItemNum, Category.CategoryName, 
    BasketItem.BasketItemQuantity * BasketItem.BasketItemPrice AS BasketItemTotal
    FROM CategoryProduct
    INNER JOIN Category ON CategoryProduct.CategoryNum = Category.CategoryNum
    INNER JOIN Product ON CategoryProduct.ProdNum = Product.ProdNum
    INNER JOIN BasketItem ON Product.ProdNum = BasketItem.ProdNum
    WHERE (BasketItem.BasketNum = @TargetBasketNum)
    RETURN

  • Note the one computed value with alias BasketItemTotal. It is computed as the quantity times the price.
  • The stored procedure returns the desired data for all rows in the joined table that have the target basket ID number. Thus we get rows of data for all of the items in the shopping basket.
  • Back in the VB code that calls this stored procedure, note how it looks up the basket number from the session state and fills this in as the parameter to the GetBasket stored procedure.
  • If the stored procedure returns no data, we display a "0 items in cart" message on a label.
  • Otherwise, we display the shopping cart data in the grid view, though with some alteration to the attribute information (carried out by the AdjustAttributes procedure) so that it is more readable.
  • Note that we also save dTable in session state. That is to allow paging to work a little faster. When the user goes to a new page in the grid view, no data needs to be retrieved from the database, as it is already in session state.
  • To get the subtotal and number (quantity) of items in the shopping cart, we call another stored procedure. This one is named BasketQuantitySubTotal and is passed the basket ID number by our VB code. The subtotal is, of course, formatted as currency for display on the appropriate label.
  • The BasketQuantitySubTotal stored procedure should be created as follows:

CREATE PROCEDURE db_datareader.BasketQuantitySubTotal 
    (
    @TargetBasketNum int,
    @Quantity int OUTPUT,
    @SubTotal money OUTPUT
    )
AS
    SELECT @SubTotal = SUM(BasketItemQuantity * BasketItemPrice)
        FROM BasketItem WHERE BasketNum = @TargetBasketNum
    SELECT @Quantity = SUM(BasketItemQuantity)
        FROM BasketItem WHERE BasketNum = @TargetBasketNum 
    RETURN

  • This stored procedure sums up the quantity times price for each row of data in the BasketItem table having the target basket number. This then is the subtotal for this basket.
  • Similarly, it sums up the quantity for each row in the same table that has the desired basket number. This is the total number of items in the shopping basket.
  • Next we add the AdjustAttributes VB procedure that we used above:

' Adjusts the Attributes column in dTable so that the data there is more readable.
Private Sub AdjustAttributes(ByRef dTable As DataTable)
    Dim Row As DataRow
    Dim AttrString As String
    Dim AttributesArray As String() = Nothing
    Dim ItemsArray As String() = Nothing
    Dim delimStr As String = ";"
    Dim DelimStr2 As String = ","
    Dim delimiter As Char() = delimStr.ToCharArray()
    Dim delimiter2 As Char() = DelimStr2.ToCharArray()
    Dim k, Top As Integer

    For Each Row In dTable.Rows
        AttrString = Row.Item("BasketItemAttributes")
        AttributesArray = AttrString.Split(delimiter)
        Top = AttributesArray.GetUpperBound(0)
        AttrString = ""
        For k = 0 To Top
            If k <> 0 Then
                AttrString &= ", "
            End If
            ItemsArray = AttributesArray(k).Split(delimiter2)
            AttrString &= ItemsArray(0)
            AttrString &= ": "
            AttrString &= ItemsArray(2)
        Next
        Row.Item("BasketItemAttributes") = AttrString
    Next
End Sub

  • AdjustAttibutes changes the data in the BasketItemAttributes column of the data table dTable passed in as a parameter.
  • Since a data table is just an in-memory copy of a table from our database, this does not alter anything in the database.
  • A "For Each" loop is used to loop through all of the rows in dTable.
  • With each row we look up the BasketItemAttributes field value and place it in the AttrString variable. This is the field that contains a string such as this example that we looked at before:

Color,1,Purple,8;Seat Covering,2,Cloth,11

  • Our code splits this string apart at the semicolons, putting the pieces into AttributesArray.
  • We then loop through all of the pieces in AttributesArray using a "For" loop.
  • For each such piece we split it apart at the commas.
  • We use items 0 and 2 from the results, as they are the category name and attribute name (such as Color and Purple from the first piece of data in the above example). These two names are concatenated to a growing string of readable attribute information named AttrString. (Note that we reused AttrString by assigning to it the empty string before this new use of it.)
  • At the end of the inner loop, the nicely formatted AttrString contains something like "Color: Red" and is assigned into the BasketItemAttributes field of the current row of dTable.
  • Thanks to the outer loop, this updating of the BasketItemAttributes field is done for each row in dTable.
  • Next, still in Cart.aspx.vb, add the following procedure to handle paging in the grid view when the user clicks on Previous or Next to see a different page of data:

Protected Sub ResultsGridView_PageIndexChanging(ByVal sender As Object, _
    ByVal e As GridViewPageEventArgs) Handles CartGridView.PageIndexChanging

    Dim dTable As DataTable = Nothing

    If Session.Count = 0 Then   ' No session items were supplied.
        ErrorLabel.Visible = True
    Else
        Try
            dTable = Session("DataTable")
            If dTable.Rows.Count = 0 Then
                ItemsLabel.Text = "0 items in cart"
                CartGridView.Visible = False
                SubTotalLabel.Visible = False
                BuyButton.Visible = False
                EmptyButton.Visible = False
            Else
                CartGridView.DataSource = dTable
                CartGridView.PageIndex = e.NewPageIndex  'Show new page
                CartGridView.DataBind()
                CartGridView.Visible = True
                SubTotalLabel.Visible = True
                BuyButton.Visible = True
                EmptyButton.Visible = True
                ' The info displayed on the labels should not need to be changed
            End If
        Catch exception As Exception
            ErrorLabel.Visible = True
        End Try
    End If
End Sub

  • Essentially this procedure gets the data in needs from what we already stored in session state. No stored procedure call to get data from the database is needed here.
  • Note the important line that sets the CurrentPageIndex.
  • Next, add the following procedure to handle a click on any of the Remove buttons that appear in each row of shopping cart data. Adjust the connection string line in the usual manner.

Protected Sub ResultsGridView_ItemCommand(ByVal Source As Object, _
    ByVal e As GridViewCommandEventArgs) Handles CartGridView.RowCommand

    Dim BasketItemNum As Integer
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim cmdRemoveBasketItem As System.Data.SqlClient.SqlCommand = Nothing
    Dim dTable As DataTable = Nothing

    If e.CommandName = "Remove" Then
        Try
            dTable = Session("DataTable")
            ' Find the index of the GridView row where the user clicked the button:
            Dim index As Integer = Convert.ToInt32(e.CommandArgument)

            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdRemoveBasketItem = New System.Data.SqlClient.SqlCommand()

            ' Calculate the row of dTable where this basket item is, based on page we
            ' are on and the index within that page:
            BasketItemNum = dTable.Rows(CartGridView.PageSize * CartGridView.PageIndex _
                + index).ItemArray(5)
            cmdRemoveBasketItem.CommandText = "RemoveBasketItem"
            cmdRemoveBasketItem.CommandType = System.Data.CommandType.StoredProcedure
            cmdRemoveBasketItem.Connection = myDatabaseConnection

            cmdRemoveBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                "@RETURN_VALUE", System.Data.SqlDbType.Int, 4, _
                System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), _
                CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdRemoveBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                "@TargetBasketNum", System.Data.SqlDbType.Int, 4, _
                System.Data.ParameterDirection.Input, False, CType(10, Byte), _
                CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdRemoveBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
                "@TargetBasketItemNum", System.Data.SqlDbType.Int, 4, _
                System.Data.ParameterDirection.Input, False, CType(10, Byte), _
                CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

            cmdRemoveBasketItem.Parameters("@TargetBasketNum").Value = Session("BasketNum")
            cmdRemoveBasketItem.Parameters("@TargetBasketItemNum").Value = BasketItemNum
            myDatabaseConnection.Open()
            cmdRemoveBasketItem.ExecuteNonQuery()
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As System.Data.SqlClient.SqlException
            DatabaseErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
            ' ErrorLabel.Text = exception.Message  ' Uncomment this line to debug.
        Finally
            cmdRemoveBasketItem.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdRemoveBasketItem = Nothing
        End Try

        'Go back to first page as we may have removed last item on page already displayed.
        CartGridView.PageIndex = 0
        Session.Remove("DataTable")   ' Remove old DataTable as update will get a new one.
        UpdatePage()
    End If
End Sub

  • This procedure, too, looks up the data table that we saved in session state.
  • In this data table is the basket item number for the item to be removed. The trick is to figure out how to get it.
  • Since e.CommandArgument tells us the row number where the user clicked in the grid view (a number from 0 to 2, since there are up to 3 cars per page), we can add that to the page index times the number of cars per page (3 in this app, PageSize in general) to get the row number in dTable for the product to remove.
  • Column 5 of that row of dTable contains the desired basket item number.
  • This basket item number is passed as a parameter to a stored procedure, as is the basket number.
  • The stored procedure RemoveBasketItem is then executed to remove this row of data from the BasketItem table.
  • We then set the PageIndex for the grid view to 0, which indicates the first page. This is done in case the page we were on is now empty due to the removal of the only row on that page.
  • The UpdatePage procedure is then called to retrieve from BasketItems the updated shopping cart content and display it on the shopping cart page.
  • Create the RemoveBasketItem stored procedure so that it contains the following code:

CREATE PROCEDURE db_datareader.RemoveBasketItem 
    (
    @TargetBasketNum int,
    @TargetBasketItemNum int
    )
AS
    DELETE FROM BasketItem WHERE BasketNum = @TargetBasketNum
        AND BasketItemNum = @TargetBasketItemNum
    RETURN

  • Next we fill in the code for the procedure that handles a click on the Empty button.
  • The purpose of this procedure is to remove everything from the current shopping cart.
  • You can generate an outline of this procedure by double clicking on the Empty button.
  • Make your procedure look like the following, except that you must adjust the connection string line as usual.

Protected Sub EmptyButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles EmptyButton.Click

    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim cmdEmptyBasket As System.Data.SqlClient.SqlCommand = Nothing

    Try
        Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString
        myDatabaseConnection = New SqlClient.SqlConnection(connectionString)

        cmdEmptyBasket = New System.Data.SqlClient.SqlCommand()
        cmdEmptyBasket.CommandText = "EmptyBasket"
        cmdEmptyBasket.CommandType = System.Data.CommandType.StoredProcedure
        cmdEmptyBasket.Connection = myDatabaseConnection
        cmdEmptyBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
            "@RETURN_VALUE", System.Data.SqlDbType.Int, 4, _
            System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), _
            CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdEmptyBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter( _
            "@TargetBasketNum", System.Data.SqlDbType.Int, 4, _
            System.Data.ParameterDirection.Input, False, CType(10, Byte), _
            CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
        cmdEmptyBasket.Parameters("@TargetBasketNum").Value = Session("BasketNum")

        myDatabaseConnection.Open()
        cmdEmptyBasket.ExecuteNonQuery()
        myDatabaseConnection.Close()
        myDatabaseConnection.Dispose()
    Catch exception As System.Data.SqlClient.SqlException
        DatabaseErrorLabel.Visible = True
    Catch exception As Exception
        ErrorLabel.Visible = True
    Finally
        cmdEmptyBasket.Dispose()

        If Not myDatabaseConnection Is Nothing AndAlso _
            myDatabaseConnection.State = ConnectionState.Open Then
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        End If

        myDatabaseConnection = Nothing
        cmdEmptyBasket = Nothing
    End Try
    UpdatePage()
End Sub

  • The above procedure gets the basket number from the session state and passes it as a parameter to a stored procedure.
  • We then execute this stored procedure and update the data on the shopping cart page by calling the UpdatePage routine. (It will, of course, now show zero items in the cart.)
  • The needed stored procedure is called EmptyBasket and should be created as follows:

CREATE PROCEDURE db_datareader.EmptyBasket 
    (
    @TargetBasketNum int
    )
AS
    DELETE FROM BasketItem WHERE BasketNum = @TargetBasketNum 
    RETURN

  • Obviously this stored procedure deletes from the BasketItem table all rows that have the target basket number.
  • Finally, if your Cart.aspx.vb file does not have the outline of a function to handle a click on the BuyButton, double click on that button in Cart.aspx to create this outline.
  • Make this click handler look like the following so as to redirect the user to the shipping page when the Buy Items in Cart button is clicked.

Protected Sub BuyButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles BuyButton.Click

    Response.Redirect("Shipping.aspx")
End Sub

The Shipping Form

  • Shipping.aspx is pretty much ready to use.
  • However, the tab order for the controls should be adjusted to make things easier for the user. Change the TabIndex property for the controls down the middle of the form, from FirstNameBox to SubmitButton, to be the numbers from 1 to 9. This allows the user to quickly tab from one to the next.
  • The custom validator on StateList, the drop-down list, runs code on the server. This round trip to the server slows things down a little in the middle of data entry and interrupts the tabbing from one field to the next a bit.
  • Note that there is a minor mistake in the data inside StateList. The text for the states is the complete name of each, such as Pennsylvania, but New York is listed as just NY. Fix that by clicking on StateList's smart tag and selecting Edit Items. In the ListItem Collection Editor adjust the data as shown in this screen shot.

The Code-Behind File for the Shipping Page

  • In the Shipping.aspx.vb file, adjust the Page_Load procedure to look like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Me.Load

    If Not IsPostBack Then
        If CType(Session("BasketNum"), String) = "" Then   ' No shopping basket
            Response.Redirect("Cart.aspx")
        End If
    End If
End Sub

  • The above procedure simply checks to see if there is a basket number for this shopper. If not, it redirects the person to the shopping cart page (where they will see an empty shopping cart and hopefully realize that they need to add something to the cart before trying to go through the checkout process).
  • Then add the following procedure to handle the custom validator mentioned above for the state drop-down list:

Protected Sub CustomValidator1_ServerValidate(ByVal source As System.Object, _
    ByVal args As System.Web.UI.WebControls.ServerValidateEventArgs) _
    Handles CustomValidator1.ServerValidate

    If StateList.SelectedItem.ToString = "Select a state" Then
        args.IsValid = False
    Else
        args.IsValid = True
    End If
End Sub

  • As you can see, the above code only validates the data if the user has selected something in the list other than "Select a state". In other words, the user is forced to select a state.
  • Note that the state list is incomplete. You can adding the missing items if you wish.
  • You probably already have the outline of the click handler for the submit button. If not, double click on the Submit button to get the outline. Make the code match the following:

Protected Sub SubmitButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles SubmitButton.Click

    'Page.Validate()is called automatically before any click handler.
    If Page.IsValid Then
        Session("FirstName") = FirstNameBox.Text
        Session("LastName") = LastNameBox.Text
        Session("Street") = StreetBox.Text
        Session("City") = CityBox.Text
        Session("State") = StateList.SelectedItem.Value
        Session("Zipcode") = ZipcodeBox.Text
        Session("Email") = EmailBox.Text
        Session("Phone") = PhoneBox.Text
        Response.Redirect("Payment.aspx")
    End If
End Sub

  • The above procedure only does something if the page's data validates.
  • If so, it saves the shipping information supplied by the user on this page. It does so by saving it in session state.
  • Then it redirects to the payment page.

The Code-Behind File for the Payment Page

  • The Payment.aspx page should be ready to use.
  • Although we only need to write code for the Page_Load procedure, it does call five stored procedures and is therefore lengthy.
  • Furthermore, these stored procedures will all be written and called using hand-written code; we will not use a data adapter or data reader.
  • This means using Parameters.Add to set up the parameters to our SqlClient.SqlCommand objects so that the names, types, and sizes match with what each stored procedure expects.
  • Adjust your Page_Load procedure to look like this, adjusting the connection string line as usual:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Me.Load

    Dim Quantity As Integer
    Dim Subtotal, Total, Tax, Shipping As Decimal
    Dim myDatabaseConnection As SqlClient.SqlConnection = Nothing
    Dim cmdBasketDetails As System.Data.SqlClient.SqlCommand = Nothing
    Dim cmdInsertOrder As SqlClient.SqlCommand = Nothing
    Dim cmdInsertPayment As SqlClient.SqlCommand = Nothing
    Dim cmdUpdateBasket As SqlClient.SqlCommand = Nothing
    Dim cmdUpdateShopper As SqlClient.SqlCommand = Nothing
    Dim connectionString As String = _
        ConfigurationManager.ConnectionStrings("PartsConnectionString").ConnectionString

    ErrorLabel.Visible = False

    If Not IsPostBack Then
        ' Make sure the user got here from a refresh of this page or from the shipping page:
        ' **** If you want to test your app locally, comment off this section until you
        ' **** have things working.  Then uncomment this section and publish your app to the
        ' **** live web server.  You MUST adjust the 2 URIs in this code to match your situtation.
        ' **** For example, the carlsond would need to be changed.
        Try
            ' Note: If you change to using https, adjust the 2 URIs here:
            If (LCase(Request.UrlReferrer.AbsoluteUri) <> _
                "http://cis2.stvincent.edu/carlsond/cars2/shipping.aspx") _
                AndAlso (LCase(Request.UrlReferrer.AbsoluteUri) <> _
                "http://cis2.stvincent.edu/carlsond/cars2/payment.aspx") Then
                Response.Redirect("Cart.aspx")
            End If
        Catch exception As Exception
            Response.Redirect("Cart.aspx")
        End Try
        ' **** End of section to comment off when testing locally.

        Try
            ' Get the Quantity and Subtotal from the database:
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdBasketDetails = New System.Data.SqlClient.SqlCommand()
            cmdBasketDetails.CommandText = "BasketQuantitySubTotal"
            cmdBasketDetails.CommandType = System.Data.CommandType.StoredProcedure
            cmdBasketDetails.Connection = myDatabaseConnection
            cmdBasketDetails.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdBasketDetails.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TargetBasketNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdBasketDetails.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Quantity", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdBasketDetails.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Subtotal", _
                System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Output, False, _
                CType(19, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdBasketDetails.Parameters("@TargetBasketNum").Value = Session("BasketNum")

            myDatabaseConnection.Open()
            cmdBasketDetails.ExecuteNonQuery()
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()

            Subtotal = cmdBasketDetails.Parameters("@Subtotal").Value
            Quantity = cmdBasketDetails.Parameters("@Quantity").Value
            Session("Subtotal") = Subtotal
            Session("Quantity") = Quantity
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            cmdBasketDetails.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdBasketDetails = Nothing
        End Try

        If Quantity = 0 Then   ' There is nothing to purchase.
            Response.Redirect("Cart.aspx")
        End If

        Shipping = Quantity * 125  ' Assumes a $125 shipping fee.  Adjust as needed.
        Tax = 0.06 * Subtotal  ' Assumes a 6% sales tax.  Adjust this as needed.
        Total = Subtotal + Tax + Shipping
        Session("Shipping") = Shipping
        Session("Tax") = Tax
        Session("Total") = Total

        SubtotalLabel.Text = Format(Subtotal, "Currency")
        ShippingLabel.Text = Format(Shipping, "Currency")
        TaxLabel.Text = Format(Tax, "Currency")
        TotalLabel.Text = Format(Total, "Currency")
    Else   ' Postback case
        ' Save the information that's on the page into session state.
        ' Also, remove any - characters from the credit card number.
        Session("NameOnCard") = NameOnCardBox.Text
        Session("CardNumber") = Replace(CardNumberBox.Text, "-", "")  ' Drop - characters.
        Session("CardType") = CardTypeList.SelectedItem.Value
        Session("CardExpireDate") = CardExpireMonthList.SelectedItem.Value & "/" & _
            CardExpireYearList.SelectedItem.Value

        ' Here is where we would likely check to see if the credit card payment is accepted or not.
        ' If not, the user would be redirected to a page explaining that the purchase was denied.
        ' Perhaps an email explaining the denial would also be sent.
        ' Because of the redirection to the deny page, the data for this intended purchase does
        ' not get written to the Order, Payment, and Shopper tables, not does the corresponding row
        ' of the Basket table have the BasketOrderPlaced field value changed from 0 to 1.
        ' Thus the order will not get processed.  Most of the data for it is discarded, though
        ' the product details are still in the BasketItemTable.

        ' The credit card check is omitted here on our first pass at this procedure.
        ' It might well take the form of a call to a web service.

        'Use InsertOrder stored procedure to insert relevant data as a new row in Order table:
        Try
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdInsertOrder = New System.Data.SqlClient.SqlCommand()
            cmdInsertOrder.CommandText = "InsertOrder"
            cmdInsertOrder.CommandType = System.Data.CommandType.StoredProcedure
            cmdInsertOrder.Connection = myDatabaseConnection
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ShopperNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BasketNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FirstName", _
                System.Data.SqlDbType.VarChar, 24))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LastName", _
                System.Data.SqlDbType.VarChar, 32))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Street", _
                System.Data.SqlDbType.VarChar, 32))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", _
                System.Data.SqlDbType.VarChar, 32))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _
                System.Data.SqlDbType.VarChar, 2))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZipCode", _
                System.Data.SqlDbType.VarChar, 15))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Phone", _
                System.Data.SqlDbType.VarChar, 30))
            cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Email", _
                System.Data.SqlDbType.VarChar, 48))

            cmdInsertOrder.Parameters("@ShopperNum").Value = Session("ShopperNum")
            cmdInsertOrder.Parameters("@BasketNum").Value = Session("BasketNum")
            cmdInsertOrder.Parameters("@FirstName").Value = Session("FirstName")
            cmdInsertOrder.Parameters("@LastName").Value = Session("LastName")
            cmdInsertOrder.Parameters("@Street").Value = Session("Street")
            cmdInsertOrder.Parameters("@City").Value = Session("City")
            cmdInsertOrder.Parameters("@State").Value = Session("State")
            cmdInsertOrder.Parameters("@ZipCode").Value = Session("Zipcode")
            cmdInsertOrder.Parameters("@Phone").Value = Session("Phone")
            cmdInsertOrder.Parameters("@Email").Value = Session("Email")

            myDatabaseConnection.Open()
            Session("OrderNum") = cmdInsertOrder.ExecuteScalar
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            cmdInsertOrder.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdInsertOrder = Nothing
        End Try

        'Use InsertPayment stored procedure to insert relevant data as a new row in Payment table:
        Try
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdInsertPayment = New System.Data.SqlClient.SqlCommand()
            cmdInsertPayment.CommandText = "InsertPayment"
            cmdInsertPayment.CommandType = System.Data.CommandType.StoredProcedure
            cmdInsertPayment.Connection = myDatabaseConnection
            cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@OrderNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CardType", _
                System.Data.SqlDbType.VarChar, 16))
            cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CardNumber", _
                System.Data.SqlDbType.VarChar, 30))
            cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CardExpireDate", _
                System.Data.SqlDbType.VarChar, 24))
            cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@NameOnCard", _
                System.Data.SqlDbType.VarChar, 64))

            cmdInsertPayment.Parameters("@OrderNum").Value = Session("OrderNum")
            cmdInsertPayment.Parameters("@CardType").Value = Session("CardType")
            cmdInsertPayment.Parameters("@CardNumber").Value = Session("CardNumber")
            cmdInsertPayment.Parameters("@CardExpireDate").Value = Session("CardExpireDate")
            cmdInsertPayment.Parameters("@NameOnCard").Value = Session("NameOnCard")

            myDatabaseConnection.Open()
            cmdInsertPayment.ExecuteNonQuery()
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            cmdInsertPayment.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdInsertPayment = Nothing
        End Try

        'Use UpdateBasket stored procedure to update relevant data in Basket table:
        Try
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdUpdateBasket = New System.Data.SqlClient.SqlCommand()
            cmdUpdateBasket.CommandText = "UpdateBasket"
            cmdUpdateBasket.CommandType = System.Data.CommandType.StoredProcedure
            cmdUpdateBasket.Connection = myDatabaseConnection
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BasketNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BasketOrderPlaced", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Quantity", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Subtotal", _
                System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, _
                CType(19, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Total", _
                System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, _
                CType(19, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Shipping", _
                System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, _
                CType(19, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateBasket.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Tax", _
                System.Data.SqlDbType.Money, 8, System.Data.ParameterDirection.Input, False, _
                CType(19, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

            cmdUpdateBasket.Parameters("@BasketNum").Value = Session("BasketNum")
            cmdUpdateBasket.Parameters("@BasketOrderPlaced").Value = 1  'meaning true
            cmdUpdateBasket.Parameters("@Quantity").Value = Session("Quantity")
            cmdUpdateBasket.Parameters("@Subtotal").Value = Session("Subtotal")
            cmdUpdateBasket.Parameters("@Total").Value = Session("Total")
            cmdUpdateBasket.Parameters("@Shipping").Value = Session("Shipping")
            cmdUpdateBasket.Parameters("@Tax").Value = Session("Tax")

            myDatabaseConnection.Open()
            cmdUpdateBasket.ExecuteNonQuery()
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            cmdUpdateBasket.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdUpdateBasket = Nothing
        End Try

        'Use UpdateShopper stored procedure to update relevant data in Shopper table.
        'We could later use this table in the management app to send promotions to customers.
        'We could also use it to implement customer profiles so that returning customers
        'would not have to enter their address information.
        Try
            myDatabaseConnection = New SqlClient.SqlConnection(connectionString)
            cmdUpdateShopper = New System.Data.SqlClient.SqlCommand()
            cmdUpdateShopper.CommandText = "UpdateShopper"
            cmdUpdateShopper.CommandType = System.Data.CommandType.StoredProcedure
            cmdUpdateShopper.Connection = myDatabaseConnection
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ShopperNum", _
                System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, _
                CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FirstName", _
                System.Data.SqlDbType.VarChar, 24))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LastName", _
                System.Data.SqlDbType.VarChar, 32))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Street", _
                System.Data.SqlDbType.VarChar, 32))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", _
                System.Data.SqlDbType.VarChar, 32))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _
                System.Data.SqlDbType.VarChar, 2))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZipCode", _
                System.Data.SqlDbType.VarChar, 15))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Phone", _
                System.Data.SqlDbType.VarChar, 30))
            cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Email", _
                System.Data.SqlDbType.VarChar, 48))

            cmdUpdateShopper.Parameters("@ShopperNum").Value = Session("ShopperNum")
            cmdUpdateShopper.Parameters("@FirstName").Value = Session("FirstName")
            cmdUpdateShopper.Parameters("@LastName").Value = Session("LastName")
            cmdUpdateShopper.Parameters("@Street").Value = Session("Street")
            cmdUpdateShopper.Parameters("@City").Value = Session("City")
            cmdUpdateShopper.Parameters("@State").Value = Session("State")
            cmdUpdateShopper.Parameters("@ZipCode").Value = Session("Zipcode")
            cmdUpdateShopper.Parameters("@Phone").Value = Session("Phone")
            cmdUpdateShopper.Parameters("@Email").Value = Session("Email")
            myDatabaseConnection.Open()
            cmdUpdateShopper.ExecuteNonQuery()
            myDatabaseConnection.Close()
            myDatabaseConnection.Dispose()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            cmdUpdateShopper.Dispose()

            If Not myDatabaseConnection Is Nothing AndAlso _
                myDatabaseConnection.State = ConnectionState.Open Then
                myDatabaseConnection.Close()
                myDatabaseConnection.Dispose()
            End If

            myDatabaseConnection = Nothing
            cmdUpdateShopper = Nothing
        End Try

        'The next step is typically to send an email confirmation to the customer
        'and to present a page confirming the order:
        Response.Redirect("Confirm.aspx")
    End If
End Sub

  • The Page_Load procedure begins by handling the case when we do not have a postback, that is, when the page first loads.
  • The first Try...Catch compares Request.UrlReferrer.AbsoluteUri with the Uri for the shipment.aspx and payment.aspx pages. If the referrer, the page that the user came from to get to the payment page, is not the shipment page or the payment page (due to a refresh), then it does not make sense for the user to be on this page. (Perhaps the user followed a bookmark directly to the payment page.) Therefore, the user is redirected to the shopping cart page.
  • If for some reason the code cannot evaluate Request.UrlReferrer.AbsoluteUri, the Catch clause redirects the user to the shopping cart page.
  • The next Try...Catch is used to call the BasketQuantitySubTotal stored procedure, a stored procedure that we wrote previously, so as to retrieve from the database the subtotal for this order and the number of items in the order.
  • To call the BasketQuantitySubTotal stored procedure we pass it the basket number and it returns the 2 values that we want in the parameters Subtotal and Quantity.
  • We store these 2 values in the session state.
  • If the Quanity value (number of items in the order) is zero, then there is nothing to purchase, so we redirect the user to the shopping cart page.
  • We next calculate the amount of tax and shipping with some simple calculations. For a real e-commerce app these would be more complex, taking into account the tax laws of each state. Also, since these calculations might need to be changed it might make sense to call a web service to do the calculations. That way when the tax or shipping calculations need to be changed, only the web service needs to be modified. The e-commerce app itself would not have to be changed.
  • The shipping, tax, and total amounts are stored in the session state.
  • We also display the subtotal along with these 3 new monetary values in the appropriate four labels.
  • That takes care of what Page_Load does in the non-postback case. What the user sees on the screen in the normal case is the four monetary values just discussed.
  • Next, the Page_Load procedure handles the postback case. This would occur after the user fills in the credit card information needed on this page and clicks the Submit button. Of course, the user might fail to fill in some or all of the data and still click the submit button. That is why we put required field validators on this page.
  • The primary task in this section of code, then, is to call some stored procedures to write the user's data, both from this page and previous ones, to the database. This is where the user gives final assent to the order and the remaining data is committed to the database.
  • Before writing any data to the database we should fix it up a bit. Any - characters in the the credit card number are removed so that all credit card numbers get stored as a string of digits only. The modified data is saved in session state.
  • This is also the spot, as indicated by the comments in the code, where we could call a web service to validate this credit card purchase. If the purchase is rejected, the user would be redirected to a deny page, without the key purchase data being written to the database. We will consider this web service idea further below.
  • If the purchase is accepted, we next use the InsertOrder stored procedure to write a row of data to the Order table. The code for this stored procedure is shown in the next box below. We pass as parameters the values that we want placed in this new row, namely the shopper number, basket number for this order, first name and last name of the shopper, as well as the shopper's address and other contact information. Since OrderNum is an indentity field in the table, the order number is created automatically. Our stored procedure returns it when the stored procedure is called by ExecuteScalar and our VB code saves it in session state.

CREATE PROCEDURE db_datareader.InsertOrder 
    (
    @ShopperNum int,
    @BasketNum int,
    @FirstName varchar(24),
    @LastName varchar(32),
    @Street varchar(32),
    @City varchar(32),
    @State varchar(2),
    @ZipCode varchar(15),
    @Phone varchar(30),
    @Email varchar(48)
    )
AS
    INSERT INTO [Order]
    (ShopperNum, BasketNum, OrderFirstName, OrderLastName, OrderStreet, OrderCity, 
        OrderState, OrderZipCode, OrderPhone, OrderEmail, OrderDateOrdered)
    VALUES
    (@ShopperNum, @BasketNum, @FirstName, @LastName, @Street, @City, @State,
        @ZipCode, @Phone, @Email, GETDATE())
    SELECT @@IDENTITY 
    RETURN

  • The only other thing to note about this stored procedure is that it uses the GETDATE() function in SQL Server to look up the system time and date. This value is put into the OrderDateOrdered field by the stored procedure.
  • Our code next uses the InsertPayment stored procedure to write the credit card information and order number to the Payment table. The code for InsertPayment is shown below. Note how our VB code appends the credit card's month of expiration with the expiration year (and a / character in between). We then use this combined date as the value to pass in the @CardExpireDate parameter.

CREATE PROCEDURE db_datareader.InsertPayment 
    (
    @OrderNum int,
    @CardType varchar(16),
    @CardNumber varchar(30),
    @CardExpireDate varchar(24),
    @NameOnCard varchar(64)
    )
AS
    INSERT INTO Payment (OrderNum, PaymentCardType, PaymentCardNumber,
        PaymentExpirationDate, PaymentCardOwnerName)
    VALUES (@OrderNum, @CardType, @CardNumber, @CardExpireDate, @NameOnCard) 
    RETURN

  • Next we have the UpdateBasket stored procedure update the row of the Basket table for the shopping basket being purchased (the one with BasketNum as its ID number).
  • Remember that up until this point all we have in this row of data is the automatically-generated basket number and the shopper number.
  • Our stored procedure, shown below, thus finds the row with the desired basket number (as passed in via the @BasketNum parameter). It then fills in the values from the other parameters: the quantity (number of items in the basket), the subtotal, the shipping, the tax, and the total amount of the order. Notice that 1 (true) is passed in as the value that gets put into the BasketOrderPlaced field, thus indicating that the order has indeed now been placed. Our management app (not shown) might then be used to actually ship the order.

CREATE PROCEDURE db_datareader.UpdateBasket 
    (
    @BasketNum int,
    @BasketOrderPlaced int,
    @Quantity int,
    @Subtotal money,
    @Total money,
    @Shipping money,
    @Tax money
    )
AS
    UPDATE Basket
    SET BasketNumItems = @Quantity,
    BasketOrderPlaced = @BasketOrderPlaced,
    BasketSubtotal = @Subtotal,
    BasketTotal = @Total,
    BasketShipping = @Shipping,
    BasketTax = @Tax
    /* ShopperNum does not change */
    WHERE
    BasketNum = @BasketNum 
    RETURN

  • Page_Load then calls its last stored procedure, UpdateShopper.
  • You may recall that up until this point there is nothing else in the Shopper table's row of data about the current shopper than the shopper number.
  • This stored procedure, as seen below, fills in the shopper's first and last name, address, phone, and email address.

CREATE PROCEDURE db_datareader.UpdateShopper 
    (
    @ShopperNum int,
    @FirstName varchar(24),
    @LastName varchar(32),
    @Street varchar(32),
    @City varchar(32),
    @State varchar(2),
    @ZipCode varchar(15),
    @Phone varchar(30),
    @Email varchar(48)
    )
AS
    UPDATE Shopper
    SET ShopperFirstName = @FirstName,
    ShopperLastName = @LastName,
    ShopperStreet = @Street,
    ShopperCity = @City,
    ShopperState = @State,
    ShopperZipCode = @ZipCode,
    ShopperPhone = @Phone,
    ShopperEmail = @Email
    WHERE
    ShopperNum = @ShopperNum 
    RETURN

  • As mentioned before, we intend to keep this shopper information long-term, both to send promotional information (advertisements) to the shoppers and to enable us to implement shopper profiles if we wish. (The latter would, when the correct password is given, supply to a previous shopper his or her old shopper number and contact information.
  • In contrast, the Payment table of credit card information is purged quickly so that such sensitive information does not easily fall prey to thieves. Similarly, the Order table of data about particular orders is probably cleared periodically of old data.
  • Our Page_Load procedure ends by redirecting to an order confirmation page.

The Code-Behind File for the Confirmation Page

  • The Confirm.aspx page should be fine as is.
  • There is only a little that needs to be done in the Confirm.aspx.vb code-behind file.
  • Adjust the Page_Load procedure to match that shown below.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
    Handles Me.Load

    Dim OrderNum As String

    OrderNum = CType(Session("OrderNum"), String)
    If OrderNum = "" Then   'No order number, so makes no sense to be on this page.
        Response.Redirect("Cart.aspx")
    End If

    'Here we would probably send an email to the customer confirming the order.
    'We keep the example shorter by skipping that and just show the confirmation page.
    OrderNumLabel.Text = OrderNum
    Session.Abandon() 'Clear out all session information now that we are done with it.
End Sub

  • As you can see, this Page_Load procedure looks up the order number in session state.
  • If no order number is found, the user is redirected to the shopping cart page as the person does not belong on the confirmation page if no order has been placed.
  • If there is an order number, it is displayed on the confirmation page along with the usual static text.
  • Lastly, we clear out all of the session state information. That ends this shopping session. There is no sense in keeping this information in session state as it would likely confuse things if the shopper went to the brands or search page and started more shopping.
  • There is one more thing to add: the credit card check and the deny page, but we do that after checking that what we have thus far works properly.

Finishing Off the Web App



Getting the App Running and Checking the Tables

  • Once you have your web app completed, try it out locally. (Be sure the section of code in Payment.aspx.vb using Request.UrlReferrer.AbsoluteUri is commented off as previously suggested. Otherwise, it will interfere with running your app locally.)
  • Use Start Without Debugging (or even Start With Debugging if you are having problems).
  • Try submitting orders for a few shopping carts in the name of various people. (Just make up the data. For sure, don't use real credit card numbers!)
  • If all seems to work in the web interface, use Server Explorer to look at the tables to see that they have the correct data. The key tables to examine are Basket, BasketItem, Order, Payment, and Shopper.
  • You should be able to find exactly the data for what you did in the web interface.
  • Pay particular attention to removing an item from the cart and emptying the cart to see that they work, especially in the complicated case of having several pages of items in the cart.
  • If you get an error, you might try customErrors mode="Off" in your Web.config file so as to be able to see the details of the error.
  • If an exception is being thrown and indicated by a red error label showing up on one of your forms, you need a way to see more details about the exception. As sometimes indicated in comments in the VB code above, you can assign exception.Message into the Text field of a label on your form so as to see this information. Just be sure that the label's Visible property is True and that the page doesn't redirect to another one before you can see the information.
  • Once things are working fine, uncomment the section in Payment.aspx.vb using Request.UrlReferrer.AbsoluteUri, adjust the 2 URIs to where you plan to post your pages, and build and publish your app to that location. Get your web administrator to make the published copy an official IIS web app, and then test it in a browser by going to the correct web address. (For example, http://cis2.stvincent.edu/carlsond/cars2/home.aspx or similar.)
  • Make sure that if you try to go directly to the payment page, as in http://cis2.stvincent.edu/carlsond/cars2/payment.aspx, you get redirected to the cart page.

Credit Card Processing and Deny.aspx Page

  • In Payment.aspx.vb, add the code needed to call a simple web service to simulate the processing of the credit card data. You might check the Deitel and Deitel book Visual Basic 2005: How to Program, 3rd ed., chapter 22.
  • In CS 305 at Saint Vincent College, just use the demo web service that has been set up for you. See the course web page for information on this.
  • Other readers might want to set up their own demo web service with a CreditCardApproval function to process the credit card data. This data should probably include the purchase amount, the credit card type, the credit card number, the card's expiration date, and the name on the card. You might set it up to accept only one credit card type and reject all others, to use a random acceptance, or to actually use a table of information on (pretend) credit card customers and their available credit. The Deitel and Deitel book just mentioned would be helpful in creating the web service.
  • In any case, you need a new Web Content Form (not a regular web form), named Deny.aspx, in your project. Create it and add some appropriate message on it to explain to the user that the intended purchase was rejected.
  • The Deny.aspx.vb file's Page_Load routine should simply use Session.Abandon() to remove all of the information on the intended purchase from session state.
  • Test your web app to be sure that intended purchases get accepted or rejected as expected.
  • If you are curious about real credit card processing, check out TopTenReviews and Verifone.

Extra Credit Features

  • If you have any time and energy at this point, you might consider adding some extra credit features to this ecommerce application.
  • This could include anything that seems to be missing but useful for such an ecommerce application. Some possibilities include the following:
    • Force the user to start at a login page and to login with a username and password before getting to the ecommerce pages shown above. You already know the basics of doing this from the earlier web app Online Survey Requiring Login. You would want to add a logout button to the list of navigation buttons that show up on all of the pages.
    • The user should be forced to use https so as to provide encryption of data and identification of the web site via a certificate. This is just a quick web server setting.
    • Allowing separate shopping and billing addresses, along with the ability to copy one into the other at the click of a button, would be a nice feature.
    • Implement user profiles, as mentioned above, to allow users to store their contact information in the Shopper table and have it automatically retrieved on return shopping trips when they log in.
    • If your server allows apps to send email, add the sending of an email message to the user when the Confirm.aspx or Deny.aspx page is reached. (In CS 305 at Saint Vincent, our server does not allow email.) You will have to look up what code to use for this.
    • A web service could be used to calculate the amount of tax on the order, based on the applicable tax laws.
    • Create the management application previously hinted at. It should allow the management team to adjust the data in the tables, perhaps when fulfilling orders, adding new products to the site, clearing out stale data, etc. This would be a large project. It might be a Windows app or a web app, though security would be especially important if a web app is used. Chapters 10, 11, and 12 of the Gunderloy and Jerke book previously mentioned show in detail how to write such a management app.

The Web Configuration File

  • Once you are sure that your e-commerce app is working correctly, you can make some final changes to your Web.config file.
  • Set debug="false" as that will leave out debugging symbols, thus producing a smaller, faster app.
  • Delete customErrors mode="Off" if you used it to see what error messages your app was generating. You don't want attackers to be able to see these error messages if they find a way to force your app to produce an error.
  • Build and publish your web app again now that these final changes have been made.

Concluding Remarks

  • You have now completed a functional e-commerce app.
  • However, we did not write the separate management app that would allow a manager to check on things, fulfill orders, make changes to the database, etc.
  • We also only hinted at a number of extra credit features that could (or should) be added to this app.

Back to the main page for ASP .NET Web Apps



Author: Br. David Carlson
Last updated: November 23, 2008
Disclaimer

Saint Vincent College - Computing and Information Systems Department
300 Fraser Purchase Road • Latrobe, PA 15650