GETTING ORDERS ON THE WEB 1065

Is It a Grid, or a Table?

If you look at the source code of either page you created in the last section, you’ll realize that it’s plain HTML. There’s no DataGrid or DataList control in the page sent down to the client—just a long HTML table. The Web controls are design-time tools. You see them on the designer’s surface; you know what they will look like when rendered on the client; you can set their properties visually; but they don’t make it past the compiler. When the compiler sees a DataGrid control, it knows that there’s no HTML equivalent and tries to build something that looks like a grid on the client, but it’s really a table.

ASP.NET is nothing more than a way to program the Web with VB and a visual designer. It allows you to design your pages visually and write VB code behind them. The controls you place on your Web forms are translated into HTML. The VB code stays at the server, and it’s executed there in response to events that originate at the client but are transmitted to the server. However, the magic grows thin real quick, because you must understand how the Web works and learn to program in a disconnected environment. Is ASP.NET a revolutionary new technology? In my view, it’s a better way of living with HTML. A technology that would allow us to design forms that can be used in both Windows and Web application—yes, that would be revolutionary. ASP.NET will buy us another couple of years, until we figure out a way to get rid of HTML.

Getting Orders on the Web

This is a rather ambitious project for this book, but it demonstrates a lot of interesting topics. It also shows you how to build a very practical Web application, and you can use the information presented in this chapter as your starting point for similar projects. Let me start with a short description of the ProductSearch project you’ll build.

Our application starts with a page that lets users select products and place them in their virtual shopping basket. A real application that’s driven by a real database might have to display thousands of products, and we simply can’t afford to download all the products to the client. We prompt users to specify the products they’re interested in, and then the program downloads only a small segment of the database. There are many ways to search the database, from very simple SELECT statements to complicated ones. The one used here is as simple as it gets (users must supply the name of the product, or a word that appears in the product’s name). You can design an interface that displays all categories and downloads the products of the selected category, use price ranges, and so on. Search operations are implemented with SELECT statements that accept one or more parameters, and you know how to build those. Figure 24.10 shows the first form on the application.

The last column contains hyperlinks that place the corresponding products in the user’s basket. Every time a link is clicked, another item of the product is added to the basket. If the item exists in the basket already, its quantity is increased by one.

At any point, users can view their basket’s contents by clicking the My Basket button. This button opens the form shown in Figure 24.11, which displays the basket (the products, their prices, subtotals, and the order total). The basket’s contents are displayed on a table, which is constructed from within the page’s code. You could have used a DataGrid to display the items ordered, or any other control.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1066 Chapter 24 ACCESSING DATA ON THE WEB

Figure 24.10

Adding selected products to one’s basket

Figure 24.11

Reviewing the basket’s contents

Once the user reviews the basket’s contents, they can click the Proceed To Checkout button

to actually place the order. On the last form of the application they must provide shipping information, as shown in Figure 24.12. The user is required to log into the database by supplying a UserID, which is the CustomerID field of the Customers table. You can use any authentication technique you deem appropriate, but this is a very simple one that doesn’t require additional code. The ID is in the database, and you can easily identify the user.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

GETTING ORDERS ON THE WEB 1067

Figure 24.12

Finalizing an online order

The Forms of the ProductSearch Application

Now we’ll discuss the design process and the code behind the pages of the application. Start a new ASP.NET Web application project and add two more Web forms to it. The ProductSearch application’s Web forms are called OrderForm, BasketForm, and BuyForm.

The OrderForm Web Form

This is the application’s startup page (shown in Figure 24.10), and it contains a TextBox (where users enter the search criteria), two buttons, and a DataGrid control, where the selected products are displayed. This form must retrieve data from the Products table, so we must create a DataSet where the selected products will reside. Drop the Products table onto the design surface, configure the DataAdapter object, and then create a DataSet object as usual. The SELECT statement of the DataAdapter object is shown next:

SELECT TOP 100 ProductID, ProductName, QuantityPerUnit, UnitPrice

FROM dbo.Products

WHERE (ProductName LIKE ‘%’ + @name + ‘%’)

In effect, we search for product names that contain the string enter by the user on the TextBox. The clause TOP 100 limits the size of the DataSet, because if the TextBox is blank, then the query will return all rows in the table. We don’t want this to happen in a real application that could download 100,000 rows to the client.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1068 Chapter 24 ACCESSING DATA ON THE WEB

With the DataSet object in place, you can configure the DataGrid control. Start by setting the following properties to these values:

Property	Setting
DataSource	DSPrducts1
DataMember	Products
DataKeyField	ProductID

The DataKeyField is the primary key (a value that’s unique among all the rows of the control). The DataGrid will automatically generate one grid column for each column in the table. We want to create the structure of the DataGrid ourselves, so set the control’s AutoGenerateColumns property to False.

Then right-click the DataGrid control on the form and select Property Builder. You will see the control’s Properties dialog box, where you can specify the appearance of the control. On the General tab of the dialog box, you set the data-binding properties. We’ve already specified the setting of these properties in the Properties window, so the dialog box should look like Figure 24.13.

Figure 24.13

The General tab of the DataGrid control’s Properties dialog box

Switch to the Columns tab, where you specify the columns to be displayed on the control, as shown in Figure 24.14. The option Create Columns Automatically At Runtime must be cleared. Select the table columns you want to display on your control from the Available Columns list, and move them to the Selected Columns list by clicking the arrow button. As you add each table column, set the header of the equivalent grid column to the string you want to display at runtime.

Add all the fields and then locate the item Hyperlink Column in the list of available columns. The new column will contain a hyperlink for each row in the table, and you’ll be able to program this hyperlink. Set the hyperlink’s Text property to “Buy.” You could also place a button in the column by adding the Select button item from the Available Columns list to the Selected Columns list.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

GETTING ORDERS ON THE WEB 1069

Figure 24.14

The Columns tab of the DataGrid control’s Properties dialog box

On the next tab of the dialog box, Paging (Figure 24.15), you can specify how the DataGrid will handle multiple pages of data (instead of displaying too many rows at once). You can enable paging and can set the page’s size (number of rows per page), the navigational buttons, and the navigational mode (whether there will be only Next and Previous buttons or a list of page numbers). You can also customize the appearance of the paging by supplying your own code. We’ll discuss the topic of paging briefly in the following section.

Figure 24.15

The Paging tab of the DataGrid control’s Properties dialog box

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1070 Chapter 24 ACCESSING DATA ON THE WEB

On the other two tabs of the Properties dialog box, the Format and Borders tabs, you can customize the appearance of the control by setting its font, colors, borders, and so on. There are many options here, but it’s relatively easy to figure out what they do. Experiment with the settings on these two tabs to get the hang of customizing your DataGrid controls.

The Format tab, shown in Figure 24.16, allows you to set the appearance of normal items and alternating items. Normal items are the odd-numbered rows and alternating items are the evennumbered rows. We usually set the background color of the two types of items to different values. The selected and edit mode items are discussed briefly in the following section.

Figure 24.16

Setting the appearance of a column’s cells on the DataGrid control

Now we must add some code behind the Product Search button. When this button is clicked, we pick the user-supplied search argument from the TextBox control and use it with the DAProducts DataAdapter’s SelectCommand. We must execute the SELECT statement that retrieves the qualifying rows and then bind the DataGrid control to the DataSet. Listing 24.9 shows the code behind the Product Search button.

Listing 24.11: Displaying the Qualifying Rows from the Products Table on a DataGrid

Private Sub ProductSearch(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles bttnSearch.Click

DAProducts.SelectCommand.Parameters(“@name”).Value = TextBox1.Text

DAProducts.Fill(DSProducts1, “Products”)

DataGrid1.DataSource = DSProducts1.Products

DataGrid1.DataBind()

DataGrid1.Visible = True

End Sub

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

GETTING ORDERS ON THE WEB 1071

Every time the user clicks one of the Buy hyperlinks, we must extract the ID of the selected product and add it to the basket. The basket in this application is stored in the Session object. We create a new Session variable for each item added to the basket, set the name of the variable to the ID of the product, and set its value to the number of items ordered. If the user clicks multiple times on the same link, the quantity of the specific product in the basket increases.

How do we intercept the clicking of the Buy link in our code? Every time a link is clicked on the page, the ItemCommand event is raised in the application. Open the ItemCommand event handler for the DataGrid1 object and enter the code shown in Listing 24.12.

Listing 24.12: Handling the Click of the Buy Links

Private Sub DataGrid1_ItemCommand(ByVal source As Object, _

ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) _ Handles DataGrid1.ItemCommand

Dim ItemID As Integer

If e.Item.ItemType = ListItemType.Pager Then Exit Sub ItemID = e.Item.Cells(0).Text

Dim sItemID As String = ItemID

If Session(sItemID) Is Nothing Then Session(sItemID) = 1

Else

Session(sItemID) = Session(sItemID) + 1 End If

End Sub

Notice that the second argument carries information about the link that was clicked. Because the page numbers displayed at the bottom of the page also raise the same event, we examine the type of the item that raised the event. If the item was a pager item, we exit the subroutine.

If not, we add another item to the basket (or increase the quantity of the items ordered if the product exists in the basket). This application uses the Session object to store the IDs and quantities of the ordered products. You’ll see how this information is used in the subsequent page. I suggest you modify the application so that it uses cookies instead (use the Request.Cookies collection to read the cookies and the Response.Cookies collection to add a new cookie or change the value of an existing cookie).

The other button on the form redirects the user to the BasketForm page, where the ordered items are displayed, and its code is shown next:

Private Sub bttnBasket_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles bttnBasket.Click

Response.Redirect(“BasketForm.aspx”)

End Sub

Run the project and check out how the OrderForm page works. Add items to the basket, bring in a different DataSet, and keep adding to the basket.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1072 Chapter 24 ACCESSING DATA ON THE WEB

The BasketForm Web Form

The BasketForm page doesn’t interact with the user; it simply displays the basket’s contents on a table. The user can click the Back button to jump back to the OrderForm page, or click the Proceed To Checkout button to place the order. All the action on the page takes place in its Load event handler. Before you can write any code, you must add the appropriate objects for accessing the database. When this page is invoked, all the information we have at hand are the IDs of the products in the basket and their matching quantities. We must retrieve all the product prices from the Products table, multiply them with the corresponding quantities, calculate each line’s subtotal as well as the order’s total, and create the table.

We’re going to build a SELECT statement to retrieve the products ordered from the Products table in our code. Open the Server Explorer and drop the Products table onto the design surface. You need not configure the DataAdapter object; just clear its SelectCommand.CommandText property in the Properties window. Then enter the code of Listing 24.13 in the page’s Load event handler.

Listing 24.13: Displaying the Basket’s Contents

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 ck As Object, cmd As String

cmd = “SELECT ProductID, ProductName, UnitPrice “ & _ “FROM Products WHERE ProductID IN (“

For Each ck In Session

cmd = cmd & ck.ToString & “, “ Next

cmd = cmd.Substring(0, cmd.Length - 2) & “)” SqlConnection1.Open() DASelectedProducts.SelectCommand.CommandText = cmd DASelectedProducts.SelectCommand.CommandType = CommandType.Text Dim Reader As Data.SqlClient.SqlDataReader = _

DASelectedProducts.SelectCommand.ExecuteReader Dim prodName As String, prodID As Integer, prodPrice As Decimal Dim subTotal As Decimal, table As String

table = “<TABLE BORDER=’1’><TR>”

table = table & “<TD><B>Product Name</B></TD><TD><B>Quantity</B></TD>” table = table & “<TD><B>Price</B></TD><TD><B>Subtotal</B></TD></TR>” While Reader.Read

table = table & “<TR><TD>”

prodName = Reader.Item(“ProductName”) prodID = Reader.Item(“ProductID”) prodPrice = Reader.Item(“UnitPrice”) table = table & prodName & “</TD>”

table = table & “<TD ALIGN=’right’>” & Session(CStr(prodID)) & “</TD>” table = table & “<TD ALIGN=’right’>” & prodPrice & “</TD>”

subTotal = prodPrice * Session(CStr(prodID))

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

GETTING ORDERS ON THE WEB 1073

table = table & “<TD ALIGN=’right’>” & subtotal & “</TD></TR>” total = total + subTotal

End While

table = table & “</TABLE>”

table = table & “<P><B>Your total is “ & total.ToString & “</B></P>” lblBasket.Text = table

SqlConnection1.Close() End Sub

The code builds a SELECT statement with the IN keyword, by reading the IDs of the selected products from the Session object, one at a time. If the basket contains three IDs, the corresponding SQL statement will be something like:

SELECT ProductID, ProductName, UnitPrice

FROM Products

WHERE ProductID IN (32, 40, 8)

When this statement is executed against the database, it will retrieve the IDs, names, and prices of the products in the user’s basket. These rows aren’t stored in a DataSet. In this page, I’ve used a DataReader object to go through the rows returned by the query and create the table with the basket’s items on-the-fly. The products are returned in the same order as their IDs appear in the Session object. The first product’s quantity is given by the expression: Session(CStr(prodID)). The prodID variable is numeric, so we must convert it to a string before using it as Session variable name. If the ID of the first selected product is 14, its quantity is Session(“14”). The code shown here will work with nonnumeric product keys as well.

The table is built one row at time, as we go through the rows of the DataReader object. The three fields of the current row are stored in the variables prodID, prodName, and prodPrice, and these variables are used to build the table displayed on a Label control.

The Proceed To Checkout button redirects the user to the third page of the application, where he’s asked to supply shipping and billing information to finalize the order. How do we redirect the user to the last page of the application? The Redirect method will work, but this time we want to pass some additional information to the page, which is the order’s total. The last page displays the order’s total on a Label control at the top, and there’s no reason to recalculate the total—it’s already been calculated on the current page. To pass the total as argument to the last page, we must append it to the URL of the destination. Instead of specifying the URL of the form, we can build a destination URL that includes a parameter, like the following:

BuyForm.aspx?Total=193.4

This is what takes place in the Click event handler of the Proceed to Checkout button, which is shown next:

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click

Response.Redirect(“BuyForm.aspx?Total=” & Server.UrlEncode(total))

End Sub

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1074 Chapter 24 ACCESSING DATA ON THE WEB

The code reads the value of the total variable (which is declared outside any procedure) and passes it to the BuyForm.aspx page. The UrlEncode method converts the value of the total variable to a URL-compliant format. On the last form of the application, we’ll use the UrlDecode method to convert the argument back to its original format.

The BuyForm Web Form

When the Login button is clicked on the BuyForm Web form, the code shown in Listing 24.14 is executed. This event handler attempts to read the row with the specified ID from the Customers table. If it succeeds, it displays the customer’s shipping address on a TextBox control and waits for the user to set the remaining fields and accept the order. If the specified ID doesn’t match one of the IDs in the database, a warning appears next to the Login button in red and no further action is taken. The user must either supply a valid ID or click the Back button to return the previous page of the application.

Listing 24.14: Verifying a User’s ID

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click DACustomers.SelectCommand.Parameters(“@CustID”).Value = TextBox1.Text DACustomers.Fill(DSCustomers1, “Customers”)

If DSCustomers1.Customers.Rows.Count <> 1 Then lblError.Visible = True

Else

lblError.Visible = False Dim Address As String

Address = DSCustomers1.Customers.Item(0).CompanyName & vbCrLf & _ DSCustomers1.Customers.Item(0).ContactName & vbCrLf & _ DSCustomers1.Customers.Item(0).Address & vbCrLf & _ DSCustomers1.Customers.Item(0).City & vbCrLf & _ DSCustomers1.Customers.Item(0).PostalCode & vbCrLf & _ DSCustomers1.Customers.Item(0).Country

txtAddress.Text = Address bttnShip.Visible =true

End If End Sub

This example doesn’t authenticate the user; it simply allows users to log in using an ID, which presumably isn’t given to other users. In a real application, you should prompt for the user’s e-mail or ID and a password.

The last piece of code in the form displays the order’s total on a Label control with the following statement, which is executed in the page’s Load event handler:

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

lblTotal.Text = “Your Order’s Total is “ & Request.QueryString(“Total”) End Sub

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com