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. 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 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.)
  • 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. The result is as shown in this picture which shows the results after the Laser car is removed 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 as seen in this screen shot.
  • Of course, the cart page also has a button labelled "Buy Items in Cart". Clicking this takes you to the shipping page. As you can see in this image, 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 as seen in this picture of the shipping page.
  • 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, the order confirmation page is shown and this particular shopping trip is over.
  • The other pages in the new web app look and function much like they did in the previous web app. Here are some images of a few of these pages.
    • 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, perhaps provides the capability to verify the credit card 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.
  • You can add additional car data to it if you desire.

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.
  • Design the table as shown below:
Column Name Data Type Length Allow Nulls
AttributeCategoryNum Int 4 no
AttributeCategoryName varchar 16 no

  • Make AttributeCategoryNum to be the key field.
  • Also set the properties shown at the bottom when in design view to make this an identity field so that the first value starts at 1, the next is 2, etc.
  • Then 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.)
  • Design the table like this:
Column Name Data Type Length Allow Nulls
AttributeNum Int 4 no
AttributeName varchar 16 no
AttributeCategoryNum int 4 no

  • Make AttributeNum to be the key field.
  • Also set the properties shown at the bottom when in design view to make this an identity field.
  • Then add the following data:
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 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.
  • Design the table this way:
Column Name Data Type Length Allow Nulls
ProdNum Int 4 no
AttributeNum int 4 no

  • CTRL click on both fields and make them to be the combination primary key.
  • 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
6 2
6 3
6 4
6 6
6 8
6 9
6 11
7 2
7 3
7 4
7 5
7 7
8 2
8 3
8 4
8 5
8 7
9 2
9 3
9 4
9 6
9 8
9 9
9 11
10 4
10 6
10 8
11 4
11 5
11 6
11 7
11 9
11 10
11 11
12 4
12 5
12 6
12 7
12 9
12 10
12 11
13 4
13 5
13 6
13 7
13 10
13 11
14 1
14 4
14 6
14 10
14 11
15 2
15 4
15 5
15 6
15 7
15 9
15 10
15 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 Length Allow Nulls
BasketNum int 4 no
ShopperNum int 4 no
BasketNumItems int 4 no
BasketOrderPlaced int 4 no
BasketSubtotal money 8 no
BasketTotal money 8 no
BasketShipping money 8 no
BasketTax money 8 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.
  • 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 Length Allow Nulls
BasketItemNum int 4 no
BasketNum int 4 no
ProdNum int 4 no
BasketItemPrice money 8 no
BasketItemProdName char 24 no
BasketItemQuantity int 4 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.
  • 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 will look like, the following is a sample of typical data 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.
BasketItemNum BasketNum ProdNum BasketItemPrice
1 1 8 39999
2 2 9 38400
3 2 4 22555
4 3 9 38400
5 3 13 29000
6 4 10 37600
7 4 2 32999
8 5 1 35255
9 5 8 39999
10 6 6 33000

BasketItemProdName BasketItemQuantity BasketItemAttributes
Thunder 4 Color,1,Purple,8;Seat Covering,2,Leather,9
Rocket 1 Color,1,Jet Black,3;Seat Covering,2,Cloth,11
Famtastic 1 Color,1,Red,6
Rocket 1 Color,1,Sky Blue,7;Seat Covering,2,Cloth,11
M-star 2 Color,1,Jet Black,3;Seat Covering,2,Plastic,10
Laser 1 Color,1,Red,6
Tornado 3 Color,1,Jet Black,3;Seat Covering,2,Leather,9
Hurricane 1 Color,1,Purple,8;Seat Covering,2,Cloth,11
Thunder 1 Color,1,Silver,2;Seat Covering,2,Leather,9
Lightning 1 Color,1,Cream,4;Seat Covering,2,Plastic,10

  • In this sample data we can see, for example, that basket 4 has basket items 6 and 7, which hold 1 Laser and 3 Tornados, respectively.
  • Pay careful attention to how the attributes are stored. The Laser 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.
  • Although your browser may show the attributes string (in the table above) on more than one line, it is really just one continuous string.
  • 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 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 Length Allow Nulls
ShopperNum int 4 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.
  • 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 Length Allow Nulls
OrderNum int 4 no
ShopperNum int 4 no
BasketNum int 4 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 8 no

  • Make OrderNum the primary key.
  • Also make it an identity field.
  • Add a "unique constraint" for the BasketNum field. 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 Designer, right click and select Properties.
    • Select the Indexes/Keys tab.
    • Select New.
    • Under Column Name select the BasketNum column.
    • Check the Create UNIQUE box.
    • Select the Constraint option.
  • 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 Length Allow Nulls
PaymentNum int 4 no
OrderNum int 4 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.
  • Add a "unique constraint" for the OrderNum field 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 set up the appropriate relationships between tables based on where we have foreign keys present.
  • We already did this in the previous web app for the Product, CategoryProduct, and Category tables.
  • For a reminder on how to set up this type of relationship, look at the directions in the survey app.
  • Set up relations for each of the following:
    • The BasketItem table contains BasketNum as a foreign key since BasketNum is the primary key in the Basket table. Check this screen shot showing this relationship.
    • 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.
    • The Basket table contains foreign key ShopperNum since ShopperNum is the primary key in the Shopper table.
    • The Order table contains ShopperNum as a foreign key since ShopperNum is the primary key in the Shopper table.
    • The Order table also contains BasketNum as a foreign key since BasketNum is the primary key in the Basket table.
    • The Payment table contains foreign key OrderNum since OrderNum is the primary key in the Order table.
    • The ProductAttribute table contains foreign key ProdNum, which is the primary key in the Product table.
    • The ProductAttribute table also contains foreign key AttributeNum since AttributeNum is the primary key in the Attribute table.
    • The Attribute table contains AttributeCategoryNum as a foreign key since AttributeCategoryNum is the primary key in the AttributeCategory table.

Work in Visual Studio



Copying the Old Project

  • Begin by making a copy of the previous e-commerce web app.
  • Name the new project cars2.
  • Close the old solution and open the new cars2 project.
  • Save all of your files.

Adjusting header.txt and footer.txt

  • In header.txt add the link labelled Cart so that it takes the user to the Cart.aspx form (to be added to the project below).
  • Also make the tables to be wider: 800 pixels wide (120 more than it was before). We will need more room across the page in order to fit the shopping cart information.
  • Adjust the last column in each table so that it is 120 pixels wider than it was previously.
  • In footer.txt move the warning so that it comes before the copyright notice and date of last update. This might be a more sensible order.
  • If you wish, you can copy the contents of the revised header.txt file. If your browser shows it as a web page, use View Source to see the actual contents of the file.
  • Similarly, here is the revised footer.txt file.

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. Use SQL Server's Enterprise Manager to create a new stored procedure and manually type in this code. (Right click on Stored Procedures and select New Stored Procedure.) Of course, fill in your ID instead of studentc.

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

  • 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 place 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, Add New Item, Code File. Then fill in GlobalProcedures.vb as the name for this file.
  • Place the following procedure into your new file. Fill in your PC's ID in the "workstation id=" section, your ID in the "CommandText" section, and your starting database location in the "catalog=" section. If it is easier, you can copy the needed connection string from the autogenerated code section for one of your web forms that used a data adapter.

Imports System.Web
Imports System.Web.SessionState
Imports System.Data
Imports System.Data.SqlClient

Module GlobalProcedures

Friend Sub CheckShopperNum(ByVal Session As HttpSessionState, ByRef ErrorFlag As Integer)
    Dim SessionShopperNum As String
    Dim MyConnection As SqlConnection

    ErrorFlag = 0

    Try
        SessionShopperNum = Session("ShopperNum")
        If (SessionShopperNum Is Nothing) OrElse (SessionShopperNum = "0") Then
            MyConnection = New SqlClient.SqlConnection("workstation id=CISPC19;" & _
               "packet size=4096;integrated security=SSPI;data source=""CIS-W2KSERVER"";" & _
               "persist security info=False;initial catalog=studentc")
            Dim Command As New SqlCommand
            Command.Connection = MyConnection
            Command.CommandText = "studentc.NewShopper"
            Command.CommandType = CommandType.StoredProcedure
            MyConnection.Open()
            Session("ShopperNum") = CType(Command.ExecuteScalar, String)
            MyConnection.Close()
        End If
    Catch ex As Exception
        ErrorFlag = 1
    End Try
End Sub

End Module

  • We are manually writing the code to call the stored procedure because Visual Studio's Query Builder won't give us what we want. Query Builder is great if we need a simple SELECT command, but here we also insert into the table. Query Builder can produce standard SELECT, INSERT, DELETE, and UPDATE commands, but it gives all 4 at once and may not match our specialized needs.
  • 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 so that they start out by calling CheckShopperNum as follows. The only web forms that we will not add this code to are the shipping and payment pages to be created later. This is because they have their own checks which will cause the user to be redirected to the shopping cart page if things are not reasonably initialized. Once on the cart page, the user will get a shopper number if need be.

Dim ErrorFlag As String

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

Changes to the Results Form

  • This page is already partially done. It shows the details about a particular product, such as its description and price.
  • Now we want to add to it so that the user can customize certain features of the product (such as the color).
  • Once the customizing is complete, we present an Add to Cart button to the user.
  • Here is an image of the results form in Visual Studio.
  • Add the drop-down lists and button to this form as seen in the above images.
  • Change the ID of the button to AddToCartButton and make it not visible initially.
  • Change the ID of the leftmost drop-down list to QuantityDropDownList and let it be visible initially. Under Properties, click on the Items field and then click on the 3 dots to bring up the collection editor where you can enter data for this list. Fill in 1, 2, 3, 4, and 5 as the possible items and have 1 be selected by default. Have both the text and value for each item be the same (for example 2 and 2). Here is a picture of item 1 in the collection editor.
  • Place a label just above this drop-down list and have it display the text "Quantity:".
  • Change the ID of the next drop-down list to CategoryDropDownList and have it not be visible initially. Set autopostback to be true for this drop-down list so that as soon as the user selects a new item, we can have some code executed to process the selection.
  • Place a label just above this drop-down list and change its ID to Category Label. Have it not be initially visible and set it to display the text "Customizable feature:".
  • Change the ID of the next drop-down list to ValueDropDownList and have it not be visible initially. Set autopostback to be true for this drop-down list so that as soon as the user selects a new item, we can have some code executed to process the selection.
  • Place a label right above this drop-down list and change its ID to ValueLabel. Have it not be initially visible and set it to display the text "Customize feature to:".
  • The items which are not initially visible will be made visible at the appropriate points in the VB code.
  • Move the label with ID ErrorLabel (and text "Error in accessing database") to a position just above the text box.
  • Also put the label with ID ShopperErrorLabel next to ErrorLabel.
  • Both labels should have their Visible property set to False under the Properties window.

Code-Behind File for the Results Form

  • 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.
  • Add the following code to the usual spot in the automatically-generated code:

Protected dTable As New DataTable
Protected dReader As System.Data.SqlClient.SqlDataReader

  • We use both this data table and data reader in the Page_Load procedure.
  • Change the Page_Load procedure to match the following:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.Load
	
    Dim Price As Decimal
    Dim Model As String
    Dim Brand As String
    Dim Description As String
    Dim Attributes As String
    Dim ErrorFlag As String
    Dim ProdNum, NumAttributes As Integer
    Dim Item As New ListItem

    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
                BrandLabel.Visible = False
                ErrorLabel.Visible = False
                ModelLabel.Visible = False
                PriceLabel.Visible = False
                ProductDescriptionBox.Visible = False
                ShopperErrorLabel.Visible = False
                Model = Session("ModelSelected")
                Brand = Session("BrandSelected")
                SqlConnection1.Open()
                SqlDataAdapter1.SelectCommand.Parameters("@TargetModel").Value = Model
                SqlDataAdapter1.SelectCommand.Parameters("@TargetBrand").Value = Brand
                SqlDataAdapter1.Fill(dTable)

                If dTable.Rows.Count = 0 Then
                    ErrorLabel.Visible = True
                Else
                    Description = dTable.Rows.Item(0).Item(0)
                    ProductDescriptionBox.Text = Description
                    ProductDescriptionBox.Visible = True
                    Session.Add("Description", Description)
                    Price = dTable.Rows.Item(0).Item(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 = dTable.Rows.Item(0).Item(2)
                    Session.Add("ProdNum", ProdNum)
                End If
            Catch exception As System.Data.SqlClient.SqlException
                ErrorLabel.Visible = True
            Catch exception As Exception
                ErrorLabel.Visible = True
            Finally
                SqlConnection1.Close()
            End Try
        End If

        Try
            Item.Text = "*** Make a choice ***"
            Item.Value = 0
            Item.Selected = True
            CategoryDropDownList.Items.Add(Item)
            SqlConnection1.Open()
            SqlDataAdapter2.SelectCommand.Parameters("@TargetProdNum").Value = ProdNum
            dReader = SqlDataAdapter2.SelectCommand.ExecuteReader
            While (dReader.Read())
                Item = New ListItem
                Item.Text = dReader.GetString(0)  'AttributeCategoryName
                Item.Value = dReader.GetValue(1)  'AttributeCategoryNum
                Item.Selected = False
                CategoryDropDownList.Items.Add(Item)
            End While
            dReader.Close()
            NumAttributes = CategoryDropDownList.Items.Count - 1
            Session.Add("NumAttributes", NumAttributes)
            If NumAttributes > 0 Then
                CategoryLabel.Visible = True
                CategoryDropDownList.Visible = True
            Else
                CategoryLabel.Visible = False
                CategoryDropDownList.Visible = False
                AddToCartButton.Visible = True
            End If
        Catch exception As Exception
            ErrorLabel.Visible = True
            CategoryLabel.Visible = False
            CategoryDropDownList.Visible = False
            AddToCartButton.Visible = False
        Finally
            SqlConnection1.Close()
        End Try
    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 as shown here:

CREATE PROCEDURE studentc.SelectModelBrand
    @TargetModel char(24),
    @TargetBrand char(24)
AS
    SET NOCOUNT ON;
SELECT dbo.Product.ProdDescription, dbo.Product.ProdPrice, dbo.Product.ProdNum
FROM dbo.Product INNER JOIN dbo.CategoryProduct 
ON dbo.Product.ProdNum = dbo.CategoryProduct.ProdNum
INNER JOIN dbo.Category ON dbo.CategoryProduct.CategoryNum = dbo.Category.CategoryNum
WHERE (dbo.Product.ProdName = @TargetModel) AND (dbo.Category.CategoryName = @TargetBrand)
GO

  • One way to revise this stored procedure is to use "Configure data adapter" on your data adapter for this stored procedure and use Query Builder to recreate the stored procedure.
  • Another way would be to paste the above stored procedure code into SelectModelBrand in Enterprise Manager.
  • Next, the above VB code has a second, new 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. This stored procedure is a straightforward selection and can be created in Visual Studio with Query Builder. Drag a new SqlDataAdapter to the results form and tell the wizard to create a new stored procedure. Also tell it to skip Insert, Update, and Delete procedures. See this picture of Query Builder to see how the query was designed. Save your stored procedure under the name SelectCategories.
  • Remember to give the appropriate user exec access to this stored procedure via Enterprise Manager.
  • Also remember to regenerate the parameters that the data adapter sends to this stored procedure. Recall that you look under the SelectCommand in the data adapter's properties. Find the CommandText property and change it to studentc.SelectCategories. (Use your ID, of course.)
  • In the rest of this description we will not repeat the last 2 reminders. You will need to remember to do these if they apply.
  • Although you do not have to hand-code this stored procedure, here is the code so that you can easily compare it with what Query Builder creates for you:

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

  • 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:

Private 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

    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.
        ValueDropDownList.Visible = True
        ValueLabel.Visible = True

        Try
            Item.Text = "*** Make a choice ***"
            Item.Value = 0
            Item.Selected = True
            ValueDropDownList.Items.Add(Item)
            SqlConnection1.Open()
            SqlDataAdapter3.SelectCommand.Parameters("@TargetCategoryNum").Value = AttributeNum
            SqlDataAdapter3.SelectCommand.Parameters("@TargetProdNum").Value = Session("ProdNum")
            dReader = SqlDataAdapter3.SelectCommand.ExecuteReader
            While (dReader.Read())
                Item = New ListItem
                Item.Text = dReader.GetString(0)
                Item.Value = dReader.GetValue(1)
                Item.Selected = False
                ValueDropDownList.Items.Add(Item)
            End While
            dReader.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
        Catch exception As Exception
            ErrorLabel.Visible = True
            ValueLabel.Visible = False
            ValueDropDownList.Visible = False
            AddToCartButton.Visible = False
        Finally
            SqlConnection1.Close()
        End Try
    Else
        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.
  • This stored procedure can be set up by dragging an SqlDataAdapter to the results form and telling the wizard to create a new stored procedure. Also tell it to skip Insert, Update, and Delete procedures. See this picture of Query Builder to see how the query was designed. Save your stored procedure under the name SelectCategoryValues.
  • Here is the code for the stored procedure itself so that you can better check what Query Builder produces for you:

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

  • 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:

Private Sub ValueDropDownList_SelectedIndexChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles ValueDropDownList.SelectedIndexChanged
	
    Dim AttrNum, ValNum As Integer
    Dim Top, k, AttributeNum, ValueNum As Integer
    Dim Found As Boolean
    Dim AttrName, AttributeName, ValName, 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 at 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:

Private Sub AddToCartButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles AddToCartButton.Click
	
    Try
        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")
            SqlConnection1.Open()
            cmdCreateBasket.ExecuteNonQuery()
            Session.Add("BasketNum", cmdCreateBasket.Parameters("@BasketNum").Value)
            SqlConnection1.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")
        SqlConnection1.Open()
        cmdInsertBasketItem.ExecuteNonQuery()
    Catch exception As Exception
        ErrorLabel.Visible = True
    Finally
        SqlConnection1.Close()
    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 manually in Enterprise Manager. Make it match the following:

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

  • 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 manually 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 directly in Enterprise Manager so that it matches the following:

CREATE PROCEDURE studentc.InsertBasketItem
    @BasketNum int,
    @ProdNum int,
    @Price money,
    @ProdName char(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
GO

  • 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, which we create below.
  • Since we are manually executed 2 stored procedures here, we need to add some items to the top of the results.aspx.vb file. Place the following items in the obvious spot near the top of this file:

Me.cmdCreateBasket = New System.Data.SqlClient.SqlCommand
Me.cmdInsertBasketItem = New System.Data.SqlClient.SqlCommand

'cmdCreateBasket
'
Me.cmdCreateBasket.CommandText = "studentc.[NewBasket]"
Me.cmdCreateBasket.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdCreateBasket.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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
'
Me.cmdInsertBasketItem.CommandText = "studentc.[InsertBasketItem]"
Me.cmdInsertBasketItem.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsertBasketItem.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
Me.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))
Me.cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ProdName", _
    System.Data.SqlDbType.Char, 24))
Me.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))
Me.cmdInsertBasketItem.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Attributes", _
    System.Data.SqlDbType.VarChar, 64))

Protected WithEvents cmdCreateBasket As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdInsertBasketItem As System.Data.SqlClient.SqlCommand

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.
  • The entire revised code for Page_Load is shown below. 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.

Private Sub Page_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
	
    'Put user code to initialize the page here
    Dim TargetModel As String
    Dim ErrorFlag As String

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

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

Private Sub DataGrid1_ItemCommand(ByVal Source As Object, _
    ByVal e As DataGridCommandEventArgs) Handles DataGrid1.ItemCommand

    'Note that e.Item is the row where the user clicked a View button
    If e.CommandName = "View" Then
        Session.Add("ModelSelected", e.Item.Cells(1).Text)
        Session.Add("BrandSelected", e.Item.Cells(2).Text)
        Session.Add("Attributes", "")  'To be sure that attributes from an old product are cleared
        Session.Remove("NumAttributes")
        Session.Remove("NumAttributesCustomized")
        Response.Redirect("results.aspx")
    End If
End Sub

The Shopping Cart Form

  • Here is an image of the completed page as displayed by a browser.
  • Also check this image of the form in Visual Studio.
  • As always, adjust this form (in HTML view) to include our header.txt and footer.txt files.
  • Set the title of the page to be "Cart, CarCrazyUsa.com".
  • The main item on this page is a data grid. Place one on the form, then resize it and locate it as shown in the images above. The default ID of DataGrid1 is fine. Set its Visible property to False.
  • Drag a data view to the form. Leave it with the default ID of DataView1.
  • Click on the data grid. Then use Auto Format under Properties to select "Colorful 1" as the desired format.
  • Click on the data grid. Then click on Property Builder under Properties. Then customize the following:
    • Under the Columns tab, uncheck "Create columns automatically at run time". Then add the following columns, in the order listed, in each case by clicking on Data Field and then clicking the > button to add the item.
      • Brand (as name and Header text), CategoryName (as Data Field), and check the boxes for Visible and Read only. Check this image to see how this looks in Property Builder.
      • Model (as name and Header text), BasketItemProdName (as Data Field), and check the boxes for Visible and Read only.
      • Features (as name and Header text), BasketItemAttributes (as Data Field), and check the boxes for Visible and Read only.
      • Quantity (as name and Header text), BasketItemQuantity (as Data Field), and check the boxes for Visible and Read only.
      • Price (as name and Header text), BasketItemPrice (as Data Field), {0:C} as Data formatting expression (for currency format), and check the boxes for Visible and Read only.
      • Total (as name and Header text), BasketItemTotal (as Data Field), {0:C} as Data formatting expression, and check the boxes for Visible and Read only.
      • BasketItemNum (as name and Header text), but do not check visible for this one. The only other item that has to be filled in on this screen is BasketItemNum for Data Field. We use this invisible column to hold the basket item number so that we can get at it when we need it, even though it cannot be seen on the screen.
      As the last column in the list, add a button column. Do this by clicking on Button Column and then the > button. Name the column Remove with Remove also as the Header text. Also use Remove for Text and Command. For Button type select PushButton. Check Visible. See this picture to see how this looks.
    • Under the Paging tab, check the Allow paging and Show navigation button boxes. Set the page size to 2 since we won't have enough room to display much more than 2 rows of data at once on the shopping cart page. Use Bottom for the Position of the navigation buttons, use "Next, Previous buttons" for the Mode and use Next and Previous for the text on the 2 buttons.
    • Under the General tab, set the data source to be DataView1. Also set it to show the header but not the footer to the table. Don't set the data source until the previous steps have been completed. (It seems to interfere with adding a new bound column.)
  • Add to the cart.aspx form the various labels seen in the pictures above. These are:
    • A label with text "To add items to your cart, use Brands or Search. Do not use your browser's back button." Make Visible to be True. It does not matter what ID this label has. We tell users to avoid the back button as it is difficult when using the back button to guarantee that the right portions of the old session state information get properly cleared and replaced by new information.
    • A label with ID ItemsLabel, no text, and Visible set to True. We will use this label to display the number of items (cars) in the shopping cart.
    • A label with ID SubTotalLabel, no text, and Visible set to True. We will use this label to display the subtotal for all items in the shopping cart.
    • A label with ID Database error, text in red of "Database error", and Visible set to False.
    • A label with ID ShopperErrorLabel, red text of "Error on page", and Visible set to False.
    • A label with ID ErrorLabel, text in red of "An error happened", and Visible set to False.
  • Add to the form a button with ID BuyButton, text "Buy Items in Cart", and Visible set to True.
  • Add another button with ID EmptyButton, text "Empty the Cart", and Visible set to True.
  • 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, expand the "Web Form Designer Generated Code" region.
  • Add the following in the usual spot.

Protected dTable As New DataTable
Protected WithEvents cmdCreateBasket As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdBasketQuantitySubTotal As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdRemoveBasketItem As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdEmptyBasket As System.Data.SqlClient.SqlCommand

  • These set up a data table and four SqlCommand objects that we will use to manually call four stored procedures. We do this manually because Query Builder won't give us what we want.
  • Next, add the following to the InitializeComponent procedure. Change the user ID from studentc to your own, of course.

Me.cmdCreateBasket = New System.Data.SqlClient.SqlCommand
Me.cmdBasketQuantitySubTotal = New System.Data.SqlClient.SqlCommand
Me.cmdRemoveBasketItem = New System.Data.SqlClient.SqlCommand
Me.cmdEmptyBasket = New System.Data.SqlClient.SqlCommand
'
'cmdCreateBasket
'
Me.cmdCreateBasket.CommandText = "studentc.[NewBasket]"
Me.cmdCreateBasket.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdCreateBasket.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
'
'cmdBasketQuantitySubTotal
'
Me.cmdBasketQuantitySubTotal.CommandText = "studentc.[BasketQuantitySubTotal]"
Me.cmdBasketQuantitySubTotal.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdBasketQuantitySubTotal.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
Me.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))
'
'cmdRemoveBasketItem
'
Me.cmdRemoveBasketItem.CommandText = "studentc.[RemoveBasketItem]"
Me.cmdRemoveBasketItem.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdRemoveBasketItem.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
'
'cmdEmptyBasket
'
Me.cmdEmptyBasket.CommandText = "studentc.[EmptyBasket]"
Me.cmdEmptyBasket.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdEmptyBasket.Connection = Me.SqlConnection1
Me.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))
Me.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))

  • Next, set up the Page_Load procedure for the shopping cart page to look like this:

Private Sub Page_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
	
    Dim ErrorFlag As Integer
    Dim Quantity As Integer

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

        Try
            If CType(Session("BasketNum"), String) = "" Then   ' need to make a new basket
                cmdCreateBasket.Parameters("@ShopperNum").Value = Session("ShopperNum")
                SqlConnection1.Open()
                cmdCreateBasket.ExecuteNonQuery()
                Session.Add("BasketNum", cmdCreateBasket.Parameters("@BasketNum").Value)
            End If
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        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.

Private Sub UpdatePage()
    Dim BasketNum, Quantity As Integer

    If Session.Count = 0 Then   ' No session items were supplied.
        ErrorLabel.Visible = True
    Else
        Try
            'Get the basket data:
            BasketNum = Session("BasketNum")
            SqlConnection1.Open()
            SqlDataAdapter1.SelectCommand.Parameters("@TargetBasketNum").Value = BasketNum
            SqlDataAdapter1.Fill(dTable)
            If dTable.Rows.Count = 0 Then
                ItemsLabel.Text = "0 items in cart"
                DataGrid1.Visible = False
                SubTotalLabel.Visible = False
                BuyButton.Visible = False
                EmptyButton.Visible = False
            Else
                AdjustAttributes(dTable)
                DataView1 = New DataView(dTable)
                DataGrid1.DataBind()
                DataGrid1.Visible = True
                SubTotalLabel.Visible = True
                BuyButton.Visible = True
                EmptyButton.Visible = True
                ' Get the subtotal and the quantity of items in the cart (basket):
                cmdBasketQuantitySubTotal.Parameters("@TargetBasketNum").Value = BasketNum
                cmdBasketQuantitySubTotal.ExecuteNonQuery()
                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
        Finally
            SqlConnection1.Close()
        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 for 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. This stored procedure can be created with Query Builder. So, drag an SqlDataAdapter to your form and set it up to create a new stored procedure. Skip Insert, Update, and Delete. See this picture of Query Builder to see how this stored procedure was designed. To make it easier to see if you have the right code for your stored procedure, here is the code. Change studentc to your own ID, as usual.

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

  • By placing the 4 needed tables into Query Builder, it will produce the join operations for you.
  • 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 data grid in the usual way, although with some alteration to the attribute information (carried out by the AdjustAttributes procedure) so that it is more readable.
  • To get the subtotal and number (quantity) of items in the shopping cart, we manually 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 needs to be created manually in Enterprise Manager. Check this image showing the stored procedure code or the following listing:

CREATE PROCEDURE studentc.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
GO

  • 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 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 an data 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, add the following procedure to handle paging in the data grid when the user clicks on Previous or Next to see a different page of data:

Private Sub DataGrid1_PageIndexChanged(ByVal sender As Object, _
ByVal e As DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
    Dim BasketNum, Quantity As Integer

    If Session.Count = 0 Then   ' No session items were supplied.
        ErrorLabel.Visible = True
    Else
        Try
            'Get the basket data:
            BasketNum = Session("BasketNum")
            SqlConnection1.Open()
            SqlDataAdapter1.SelectCommand.Parameters("@TargetBasketNum").Value = BasketNum
            SqlDataAdapter1.Fill(dTable)
            If dTable.Rows.Count = 0 Then
                ItemsLabel.Text = "0 items in cart"
                DataGrid1.Visible = False
                SubTotalLabel.Visible = False
                BuyButton.Visible = False
                EmptyButton.Visible = False
            Else
                AdjustAttributes(dTable)
                DataView1 = New DataView(dTable)
                DataGrid1.CurrentPageIndex = e.NewPageIndex  'Show new page
                DataGrid1.DataBind()
                DataGrid1.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 System.Data.SqlClient.SqlException
            DatabaseErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try
    End If
End Sub

  • The above code is almost the same as that used in the UpdatePage procedure that we already wrote.
  • The only addition is the important line that sets the CurrentPageIndex.
  • As indicated by the comment, the section of code that placed the subtotal and total number of basket items into their respective labels has been removed. That information does not change no matter what page of the data one looks at.
  • Next, add the following procedure to handles a click on one of the Remove buttons that appears in each row of shopping cart data.

Private Sub DataGrid1_ItemCommand(ByVal Source As Object, _
ByVal e As DataGridCommandEventArgs) Handles DataGrid1.ItemCommand
    Dim ItemNum As Integer
    'Note that e.Item is the row where the user clicked a button
    If e.CommandName = "Remove" Then
        Try
            ItemNum = CType(e.Item.Cells(6).Text, Integer)
            cmdRemoveBasketItem.Parameters("@TargetBasketNum").Value = Session("BasketNum")
            cmdRemoveBasketItem.Parameters("@TargetBasketItemNum").Value = ItemNum
            SqlConnection1.Open()
            cmdRemoveBasketItem.ExecuteNonQuery()
        Catch exception As System.Data.SqlClient.SqlException
            DatabaseErrorLabel.Visible = True
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try

        'Go back to first page as we may have removed last item on page already displayed.
        DataGrid1.CurrentPageIndex = 0
        UpdatePage()
    End If
End Sub

  • This procedure gets the value from column 6 of the row where the user clicked the Remove button. You may recall that the sixth column is the hidden column that contains the 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 CurrentPageIndex for the data grid 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.
  • Manually create our RemoveBasketItem stored procedure so that it contains the following code:

CREATE PROCEDURE studentc.RemoveBasketItem
    @TargetBasketNum int,
    @TargetBasketItemNum int
AS
    DELETE FROM BasketItem WHERE BasketNum = @TargetBasketNum _
	   AND BasketItemNum = @TargetBasketItemNum
GO

  • 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.
  • Make your procedure look like this:

Private Sub EmptyButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles EmptyButton.Click
    Try
        cmdEmptyBasket.Parameters("@TargetBasketNum").Value = Session("BasketNum")
        SqlConnection1.Open()
        cmdEmptyBasket.ExecuteNonQuery()
    Catch exception As System.Data.SqlClient.SqlException
        DatabaseErrorLabel.Visible = True
    Catch exception As Exception
        ErrorLabel.Visible = True
    Finally
        SqlConnection1.Close()
    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. (It will, of course, now show zero items in the cart.)
  • The needed stored procedure is called EmptyBasket and should be created in SQL Server's Enterprise Manager to have the following code:

CREATE PROCEDURE studentc.EmptyBasket
    @TargetBasketNum int
AS
    DELETE FROM BasketItem WHERE BasketNum = @TargetBasketNum
GO

  • Obviously this stored procedure deletes from the BasketItem table all rows that have the desired basket number.
  • Finally, fill in the one needed line of code to redirect the user to the shipping page (which we create later) when the Buy Items in Cart button is clicked. Here is how this BuyButton_Click procedure should look:

Private 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

  • Add a new web form named shipping.aspx to our project.
  • We wish to obtain a page that will look like this screen shot and this one.
  • You can also refer to this image of the shipping form as seen in Visual Studio.
  • In HTML view, include our header.txt and footer.txt files as we did in all of the other web forms.
  • Change the title to "Shipping, CarCrazyUsa.com".
  • Add the label that says "Enter Shipping Information" and have it displayed in a large font.
  • Under this add 4 text boxes with IDs FirstNameBox, LastNameBox, StreetBox, and CityBox. Change the tab index for these to 1 through 4, respectively. (The purpose of this is to allow the user to simply press the tab key to easily go from one box to the next.) Leave Visible set to True for all 4.
  • Under this add a drop-down list with ID StateList. Set its tab index to 5. Have Visible be True. Click on the Items property and then on the ... button to get the Collection Editor. Begin by adding an entry that has "Select a state" as the Text and 0 as the Value. Set Selected to True. Then add in the names of the 50 states and District of Columbia, in alphabetical order, for the Text entry and give each its usual 2-letter abbreviation as the Value entry. Leave Selected at False for all of these. Check this image to see what this looks like when the list is partially completed.
  • Below this drop-down list add 3 more text boxes with IDs ZipcodeBox, EmailBox, and PhoneBox. Change the tab index for these to 6 through 8, respectively. Visible should be True for all of these.
  • To the left of the 7 text boxes and 1 drop-down list put labels that display these messages in this order, top-to-bottom on the screen: * First Name, * Last Name, *Street Address, * City, * State, * Zipcode, Email, Phone. All should be visible.
  • Put a button below the last text box. Change its ID to SubmitButton and its text to Submit. It, too, should be visible. Double click this button to add to the code-behind file the outline of a click handler for this button.
  • Put a label at the top right in extra small font size that displays this text: "* indicates a required field".
  • We also need to add data validators. For each one, make sure that the EnableClientScript property is set to True. This is to give the user quick help if the person forgets to fill in a required field or similar. The validator also runs code at the server to check the data. This is for security purposes, to make sure that only reasonable data gets processed by our app.
    • Add a required field validator to the right of each of the first 5 text boxes. For each, the control to validate property should be set to the text box to the left of the validator. Use "Required" as the error message for each.
    • Add a regular expression validator to the right of each of the two validators for the first name and last name text boxes. For each, the control to validate should be the text box that it aligns with horizontally. Use "Illegal data" for the error message field. Use [a-zA-Z.,'\ ]{1,64} for the validation expression, since it allows 1 through 64 instances of the listed characters, namely lower and upper case letters, period, command, apostrophe, and space. Note that the \ is used to quote the space, so that the \ followed by a space represents a single character, a space. This list of characters and maximum length of 64 seem to be reasonable to handle people's names.
    • Add a regular expression validator to the right of the required field validator for the street address box. Have it validate this text box and use "Illegal data" for the error message. Use [a-zA-Z0-9#.,'\ ]{1,64} for the validation expression. This is almost the same as the previous regular expression, but it allows the additional characters 0 through 9 and #. These are included here since they often occur in street addresses.
    • Add a regular expression validator to the right of the required field validator for the city box. Have it validate this text box and use "Illegal data" for the error message. Use the same validation expression that we used above for the first and last name boxes.
    • Add a regular expression validator to the right of the required field validator for the zipcode box. Have it validate this text box and use "Illegal data" for the error message. Click in the ValidationExpression field and then on the ... button to get the Regular Expression Editor. Select the built-in regular expression labelled "U.S. ZIP Code".
    • Add a regular expression validator to the right of the email address box. Have it validate this text box and use "Illegal data" for the error message. Click in the ValidationExpression field and then on the ... button to get the Regular Expression Editor. Select the built-in regular expression labelled "Internet Email Address".
    • Add a regular expression validator to the right of the phone number box. Have it validate this text box and use "Illegal data" for the error message. Select the built-in validation expression for "U.S. Phone Number".
    • Note that we are assuming, to keep things simpler, that our web app will only be used inside the U.S.
    • Add a custom validator to the right of the state drop-down list. Set the ControlToValidate to StateList. Use "Must select" as the error message. Leave the ID as the default CustomValidator1. We will add code for this validator below.

The Code-Behind File for the Shipping Page

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

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.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 that we added above for the state drop-down list:

Private 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 (or D.C.).
  • You probably already have the outline of the click handler for the submit button. Add the code shown below:

Private Sub SubmitButton_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles SubmitButton.Click
    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 the session state.
  • Then it redirects to the payment page (to be added below).

The Payment Page

  • Look at this image of the payment page to see how it should appear. This image shows that a required field validator has indicated to the user that the box for the credit card number must be filled in.
  • Similary, this other image of the payment form shows a regular expression validator complaining because a # symbol was included in the name field.
  • Check this image of the payment.aspx form as it appears in Visual Studio. It gives you the overall sense of what goes where on this page.
  • Thus, add a new web form called payment.aspx to your project.
  • Change the title of this page to "Payment, CarCrazyUsa.com".
  • In HTML view include our usual header.txt and footer.txt files.
  • The 2 labels in a large font display the messages "Order Summary:" and "Payment Information:".
  • The labels going down the middle of the page display the messages "Subtotal:", "Shipping:", "Tax:", "Total:", "Name on credit card:", "Credit card number:", "Credit card type:", "Expiration month:", and "Expiration year:".
  • Just to the right of each of these labels place the following (in this order): A label with ID SubtotalLabel and no text, a label with ID ShippingLabel and no text, a label with ID TaxLabel and no text, a label with ID TotalLabel and no text, a text box with ID NameOnCardBox and TabIndex 1, a text box with ID CardNumberBox and TabIndex 2, a drop-down list with ID CardTypeList and TabIndex 3, a drop-down list with ID CardExpireMonthList and TabIndex 4, and a drop-down list with ID CardExpireYearList and TabIndex 5. All of these items should be visible. The TabIndex values are arranged so that the user can start on the first text box and easily tab through the other items in order.
  • For the first drop-down list, go to Properties, click on Items, and click on the ... button to bring up the Collection Editor. Use the Collection Editor to fill in Visa, MasterCard, and American Express (both for Text and Value).
  • Similary, for the second drop-down list use the Collection Editor to fill in the 12 months of the year. Use the name of the month for Text and the numbers 1 through 12 for Value.
  • For the third drop-down list use the Collection Editor to fill in the years 2005 through perhaps 2014 (for both Text and Value).
  • Below this last drop-down list put a button with ID SubmitButton and text "Submit". Double click on it to place the outline for a click handler in the code-behind file.
  • Above the first text box place a label with ID ErrorLabel and text (in red) "Error on page". Set Visible to False for this label, though it won't really matter since the code-behind file will set it to False and only turn it to True if an error occurs.
  • Below the label that says "Payment Information:" place a required field validator. Set it to validate the NameOnCardBox and set the error message to "Name on card is required".
  • Below this place a regular expression validator. Have it also validate the NameOnCardBox and set its error message to "Illegal data for name". For the validation expression use [a-zA-Z.,'\ ]{1,64}, a regular expression that we have used before. It allows up to 64 characters in the list: lower case letters, upper case letters, period, comma, single quote (apostrophe), and space.
  • Below this put a required field validator. Have it validate the CardNumberBox and set its error message to "Card number required".
  • One last validator is needed. Below the last one put a regular expression validator and have it validate the same CardNumberBox. We want to see that the user enters a reasonable credit card number. Use "Illegal data for card number" for the error message and [-0-9]{1,64} for the validation expression. This regular expression restricts the user to digits 0 through 9 and the - symbol (which is often used between groups of digits in credit card numbers). A maximum of 64 characters is more than we need, but the main point is to limit the user so that a huge string cannot be entered here.

The Code-Behind File for the Payment Page

  • Although we only need to write code for the Page_Load procedure, it does call five stored procedures.
  • Furthermore, these stored procedures will all be written and called using hand-written code; we will not use a data adapter, nor we will create the stored procedures with Query Builder.
  • So that we will be able to call our stored procedures, we first add the following at the top of the code-behind file, in the region for automatically-generated code. We do this so that we have an SqlConnection and five SqlCommand variables available for our use.

Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection ' added by hand
Protected WithEvents cmdBasketDetails As System.Data.SqlClient.SqlCommand ' added manually
Protected WithEvents cmdInsertOrder As System.Data.SqlClient.SqlCommand ' added manually
Protected WithEvents cmdInsertPayment As System.Data.SqlClient.SqlCommand ' added manually
Protected WithEvents cmdUpdateBasket As System.Data.SqlClient.SqlCommand ' added manually
Protected WithEvents cmdUpdateShopper As System.Data.SqlClient.SqlCommand ' added manually

  • Then add the following to the InitializeComponent procedure so as to initialize these six items:

Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.cmdBasketDetails = New System.Data.SqlClient.SqlCommand
Me.cmdInsertOrder = New System.Data.SqlClient.SqlCommand
Me.cmdInsertPayment = New System.Data.SqlClient.SqlCommand
Me.cmdUpdateBasket = New System.Data.SqlClient.SqlCommand
Me.cmdUpdateShopper = New System.Data.SqlClient.SqlCommand

  • In the same procedure set up the connection string like the following.
  • You may need to adjust your workstation id, data source (server where SQL Server is located, and initial catalog (your starting location in SQL Server).

Me.SqlConnection1.ConnectionString = "workstation id=CISPC19;packet size=4096; _
    integrated security=SSPI;data source=""CIS" & "-W2KSERVER""; _
    persist security info=False;initial catalog=studentc"

  • Next, in the same section we manually set up the fields for the five SqlCommand objects. We set up each to use a stored proecedure, indicated by name in the CommandText line. We also specify the parameters for each stored procedure. You have to be sure that the names, types, and sizes for these parameters match what you specify in the stored procedures themselves.
  • Replace studentc, of course, with your ID.

'
'cmdBasketDetails
'
Me.cmdBasketDetails.CommandText = "studentc.[BasketQuantitySubTotal]"
Me.cmdBasketDetails.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdBasketDetails.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
Me.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))
'
'cmdInsertOrder
'
Me.cmdInsertOrder.CommandText = "studentc.[InsertOrder]"
Me.cmdInsertOrder.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsertOrder.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FirstName", _
    System.Data.SqlDbType.VarChar, 24))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LastName", _
    System.Data.SqlDbType.VarChar, 32))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Street", _
    System.Data.SqlDbType.VarChar, 32))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", _
    System.Data.SqlDbType.VarChar, 32))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _
    System.Data.SqlDbType.VarChar, 2))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZipCode", _
    System.Data.SqlDbType.VarChar, 15))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Phone", _
    System.Data.SqlDbType.VarChar, 30))
Me.cmdInsertOrder.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Email", _
    System.Data.SqlDbType.VarChar, 48))
'
'cmdInsertPayment
'
Me.cmdInsertPayment.CommandText = "studentc.[InsertPayment]"
Me.cmdInsertPayment.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdInsertPayment.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CardType", _
    System.Data.SqlDbType.VarChar, 16))
Me.cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CardNumber", _
    System.Data.SqlDbType.VarChar, 30))
Me.cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CardExpireDate", _
    System.Data.SqlDbType.VarChar, 24))
Me.cmdInsertPayment.Parameters.Add(New System.Data.SqlClient.SqlParameter("@NameOnCard", _
    System.Data.SqlDbType.VarChar, 64))
'
'cmdUpdateBasket
'
Me.cmdUpdateBasket.CommandText = "studentc.[UpdateBasket]"
Me.cmdUpdateBasket.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdUpdateBasket.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.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))
Me.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))
Me.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))
Me.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))
Me.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))
Me.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))
'
'cmdUpdateShopper
'
Me.cmdUpdateShopper.CommandText = "studentc.[UpdateShopper]"
Me.cmdUpdateShopper.CommandType = System.Data.CommandType.StoredProcedure
Me.cmdUpdateShopper.Connection = Me.SqlConnection1
Me.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))
Me.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))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FirstName", _
    System.Data.SqlDbType.VarChar, 24))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LastName", _
    System.Data.SqlDbType.VarChar, 32))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Street", _
    System.Data.SqlDbType.VarChar, 32))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", _
    System.Data.SqlDbType.VarChar, 32))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", _
    System.Data.SqlDbType.VarChar, 2))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZipCode", _
    System.Data.SqlDbType.VarChar, 15))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Phone", _
    System.Data.SqlDbType.VarChar, 30))
Me.cmdUpdateShopper.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Email", _
    System.Data.SqlDbType.VarChar, 48))

  • Now we are ready to fill in the code for the Page_Load procedure. This one is fairly lengthy, though there is more that we could add to it!
  • Adjust your Page_Load procedure to look like this:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.Load
   
    Dim Quantity As Integer
    Dim Subtotal, Total, Tax, Shipping As Decimal
    Dim Item As ListItem

    ErrorLabel.Visible = False

    If Not IsPostBack Then
        'Make sure the user got here from a refresh of this page or from the shipping page:
        Try
            If (LCase(Request.UrlReferrer.AbsoluteUri) <> _
                "http://cis3.stvincent.edu/carlsonz/cars2/shipping.aspx") _
                AndAlso (LCase(Request.UrlReferrer.AbsoluteUri) <> _
                "http://cis3.stvincent.edu/carlsonz/cars2/payment.aspx") Then
                Response.Redirect("cart.aspx")
            End If
        Catch exception As Exception
            Response.Redirect("cart.aspx")
        End Try

        Try
            cmdBasketDetails.Parameters("@TargetBasketNum").Value = Session("BasketNum")
            SqlConnection1.Open()
            cmdBasketDetails.ExecuteNonQuery()
            Subtotal = cmdBasketDetails.Parameters("@Subtotal").Value
            Quantity = cmdBasketDetails.Parameters("@Quantity").Value
            Session("Subtotal") = Subtotal
            Session("Quantity") = Quantity
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try

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

        Shipping = Quantity * 125
        Tax = 0.06 * Subtotal
        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
        'Single quotes (apostrophes) have to be doubled before storing the data in SQL Server:
        Session("FirstName") = Replace(Session("FirstName"), "'", "''")
        Session("LastName") = Replace(Session("LastName"), "'", "''")
        Session("Street") = Replace(Session("Street"), "'", "''")
        Session("City") = Replace(Session("City"), "'", "''")

        'Save the information that's on the page into session state.
        'Also double any single quotes in name and remove the - character from card number.
        Session("NameOnCard") = Replace(NameOnCardBox.Text, "'", "''")
        Session("CardNumber") = Replace(CardNumberBox.Text, "-", "")
        Session("CardType") = CardTypeList.SelectedItem.Value
        Session("CardExpireMonth") = CardExpireMonthList.SelectedItem.Value
        Session("CardExpireYear") = CardExpireYearList.SelectedItem.Value

        'Use InsertOrder stored procedure to insert relevant data as new row in Order table:
        Try
            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")
            SqlConnection1.Open()
            Session("OrderNum") = cmdInsertOrder.ExecuteScalar
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try

        'Use InsertPayment stored procedure to insert relevant data as new row in Payment table:
        Try
            cmdInsertPayment.Parameters("@OrderNum").Value = Session("OrderNum")
            cmdInsertPayment.Parameters("@CardType").Value = Session("CardType")
            cmdInsertPayment.Parameters("@CardNumber").Value = Session("CardNumber")
            cmdInsertPayment.Parameters("@CardExpireDate").Value = Session("CardExpireMonth") _
                & "/" & Session("CardExpireYear")
            cmdInsertPayment.Parameters("@NameOnCard").Value = Session("NameOnCard")
            SqlConnection1.Open()
            cmdInsertPayment.ExecuteNonQuery()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try

        'Use UpdateBasket stored procedure to update relevant data in Basket table:
        Try
            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")
            SqlConnection1.Open()
            cmdUpdateBasket.ExecuteNonQuery()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        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.
        Try
            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")
            SqlConnection1.Open()
            cmdUpdateShopper.ExecuteNonQuery()
        Catch exception As Exception
            ErrorLabel.Visible = True
        Finally
            SqlConnection1.Close()
        End Try

        'The next step is typically to send an email confirmation to the customer.
        'We skip this in part to keep the example shorter.

        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. As the comment in the code says, any single quote (probably used as an apostrophe in a name) needs to be replaced by 2 single quotes before storing the data in SQL Server. Also, 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.
  • Next, we 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 the session state.

CREATE PROCEDURE 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
GO

  • 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 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)
GO

  • 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.

CREATE PROCEDURE 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
GO

  • Page_Load then calls its last stored procedure, UpdateShopper.
  • You may recall that up until this point there is little 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 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
GO

  • As mentioned before, we intent 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 order is probably cleared periodically of old data.
  • As noted in the comment in the code, it is very common at this point to have our web app send an email confirmation of this order to the shopper. We skip this here to keep our example shorter.
  • Our Page_Load procedure simply ends by redirecting to an order confirmation page.

The Confirmation Page

  • Add a web form named confirm.aspx to your project.
  • Change its title to "Confirm, CarCrazyUsa.com".
  • In HTML view, include the usual header.txt and footer.txt files.
  • We want the confirmation page to display an order confirmation message so that the shopper knows that the order has been placed and can record the order number (in case the need arises to contact the company about this order).
  • Also refer to this screen shot of the confirm.aspx form in Visual Studio.
  • The text shown in the labels is pretty simple. The large one says "Order Confirmation".
  • The ones in small font contain the following:
    • Your order has been received.
    • It will be processed when your credit card payment clears.
    • Order number:
    • Save your order number and refer to it if you need to contact us with questions.
    • Thank you for shopping with us!
  • The "Order number:" label is followed by another label with ID OrderNumLabel and no inital text. The correct order number will be filled in as the text by our VB code.

The Code-Behind File for the Confirmation Page

  • There is little that needs to be done in the confirm.aspx.vb code-behind file.
  • We only need a few lines of code in the Page_Load procedure. This code is shown below.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles MyBase.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

    OrderNumLabel.Text = OrderNum
    Session.Abandon() 'Clear out all session information.
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 might confuse things if the shopper goes to the brands or search page and starts to try to start a new shopping attempt.

Finishing Off the Web App



Checking the Tables

  • Once you have your web app completed, try it out by 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 at this point.)
  • Below is some sample data from the Basket table:
BasketNum ShopperNum BasketNumItems BasketOrderPlaced
113 220 3 1
114 221 1 1
 
BasketSubtotal BasketTotal BasketShipping BasketTax
102500 109025 375 6150
36500 38815 125 2190

  • From the above data you can tell that shopper number 220 ordered 3 items, while shopper 221 orered just one.
  • Next, let's look at the relevant rows of the BasketItem table to see what items the above 2 shoppers ordered.
BasketItemNum BasketNum ProdNum BasketItemPrice
186 113 7 36500
187 113 6 33000
188 114 7 36500
 
BasketItemProdName BasketItemQuantity BasketItemAttributes
Bolt 1 Color,1,Silver,2
Lightning 2 Color,1,Purple,8;Seat Covering,2,Cloth,11
Bolt 1 Color,1,Cream,4

  • By combining information from the 2 tables examined so far, we can tell that shopper number 220 purchased basket number 113 for $109,025 and that this basket contained 1 silver Bolt and 2 identical purple Lightnings with cloth seat covers.
  • In addition, shopper number 221 purchased basket number 114 for $38,815 with just one car in it: a cream-colored Bolt.
  • Next, we look at a few rows of the Order table:
OrderNum ShopperNum BasketNum OrderFirstName OrderLastName OrderStreet
8 220 113 Sally Wiggins 234 E. Main St.
9 221 114 David Carlson 1234 Main St.

OrderCity OrderState OrderZipCode OrderPhone OrderEmail OrderDateOrdered
Greensburg PA 15601 412-345-6789 sw@msn.com 4/14/2005 8:36:16 PM
Henry AR 34566 345-9876 dc@msn.com 4/14/2005 8:40:56 PM

  • From the above, we now know that shopper number 220, the one who purchased basket number 113, is named Sally Wiggins. We also have her contact information, order number (8), and the date the order was placed.
  • We also see that shopper number 221, the one who purchased basket number 114, is named David Carlson. We again have the contact information for the shopper and the order date.
  • Next, we look at a portion of the Shopper table:
ShopperNum ShopperFirstName ShopperLastName ShopperStreet ShopperCity
220 Sally Wiggins 234 E. Main St. Greensburg
221 David Carlson 1234 Main St. Henry

ShopperState ShopperZipCode ShopperPhone ShopperEmail
PA 15601 412-345-6789 sw@msn.com
AR 34566 345-9876 dc@msn.com

  • The shopper table is the one we said that we could keep long-term so as to implement shopper profiles and to enable us to send promotions to our customers.
  • Finally we look at a few rows of data from the Payment table:
PaymentNum OrderNum PaymentCardType
8 8 AmericanExpress
9 9 Visa
 
PaymentCardNumber PaymentExpirationDate PaymentCardOwnerName
5555888866662222 10/2009 Sally T. Wiggins
1234123412341234 1/2006 David E. Carlson

  • Here we can read the credit card informtion for order number 8, which we know from the other tables is for shopper number 220, who bought 3 cars, etc.
  • Similarly, we see the credit card informtion for order number 9, which we know from the other tables is for shopper number 221, who bought the one cream-colored Bolt, etc.
  • As noted earlier we would keep each row of data of the Payment table no longer than necessary to verify payment for the order.
  • Before going on, make sure that the data in your tables from a few shopping attempts makes sense like the above sample data.

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.
  • As usual, use customErrors mode="RemoteOnly" since that will prevent would-be attackers from getting detailed error messages should they find a way to crash your app.
  • Also check that you have trace enabled="false". We don't want trace logging in a completed app.

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 credit-card information, fulfill orders, make changes to the database (because orders were fulfilled, new products were added to the store, etc.)
  • There are also a number of desirable features that we did not include in our e-commerce app, including having separate shipping and billing addresses, implementing user profiles, and sending an email confirmation message to each shopper. No doubt you can think of other improvements as well.

Back to the main page for ASP .NET Web Apps



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