15.3.4- Browsing and Bidding

by NT Community Manager.
Last Updated by Sarah Welna.

Public Categorized as 15. Writing an Application.

Not tagged.

Browsing and Bidding

OK, now any registered user can place items in the sale listings. The next step is to allow users to browse the listings and to bid for the items that are on sale there. For these tasks, we'll write three more new pages: BrowseListings.asp, Bid.asp and AddBid.asp.

Browsing the Listings

The next step is to allow users (whether registered or not) to browse all of the items our users have placed for sale. Our system provides a very simple interface for doing this – all of the items are presented in a list. This leaves the door open for later enhancement, such as providing other ways of viewing the items that are for sale.

Try It Out – Browsing the Items for Sale

1. Create a new text file and key up the following code:

<HTML>

<HEAD>

<TITLE>Wrox Classifieds</TITLE>

</HEAD>

<CENTER><H1>Wrox Classifieds<BR>Items for sale</H1>

If Session("PersonID") <> "" Then

Response.Write "<H3>Welcome " & Session("GivenName") & "</H3>"

End If

</CENTER>

<P>Here's a list of all the items that our users have made available for purchase:

</P>

Dim rsItems

strSQL = "SELECT * FROM Item " & _

"WHERE ExpirationDate > #" & FormatDateTime(Now,2) & "# " & _

"AND ItemStatus = 'Active';"

Set rsItems = Server.CreateObject("ADODB.Recordset")

rsItems.Open strSQL, objConn

If Not rsItems.EOF Then

Response.Write _

"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">" & _

" <TR>" & _

" <TH>Item ID"

If Session("PersonID") <> "" Then

Response.Write "<BR><FONT SIZE=""-1"">Click to Bid</FONT>"

End If

Response.Write "</TH>" & _

" <TH>Name</TH>" & _

" <TH>Asking Price</TH>" & _

" <TH>Listing Date</TH>" & _

" <TH>Current Bid</TH>" & _

" <TH>Bid Time</TH>" & _

" </TR>"

Do While Not rsItems.EOF

Response.Write "<TR ALIGN=CENTER>"

If Session("PersonID") <> "" Then

Response.Write _

"<TD><A href="Bid.asp?Item=" & rsItems("ItemID") & ">" & _

rsItems("ItemID") & "</A></TD>"

Else

Response.Write "<TD>" & rsItems("ItemID") & "</TD>"

End If

Response.Write _

"<TD>" & rsItems("ItemName") & "</TD>" & _

"<TD>" & FormatCurrency(rsItems("AskingPrice")) & "</TD>" & _

"<TD>" & FormatDateTime(rsItems("ListingDate"),2) & "</TD>" & _

"</TR>"

rsItems.MoveNext

Loop

Response.Write "</TABLE>"

rsItems.close

Set rsItems = Nothing

Else

Response.Write "<CENTER><H2>No items currently for sale</H2></CENTER>"

End If

<P>

<HR>

<% If Session("PersonID") <> "" Then %>

<TD WIDTH=33%>Browse the listings</TD>

<TD WIDTH=33%><A href="ViewMySaleItems.asp">List/Edit Sale Items</a></TD>

Edit Registration Info</A></TD>

<% Else %>

<TD WIDTH=33%>Browse the listings</TD>

<TD WIDTH=33%><A href="Login.asp">Login</A></TD>

<% End If %>

</TR>

</TABLE>

</BODY>

</HTML>

2. Save the file as BrowseListings.asp, in the same folder as your other ASP files.

3. From the Selling Items page (ViewMySaleItems.asp), click on Browse the listings. Don't click to bid on anything yet; we haven't covered the pages for that yet. The following screenshot shows what David Sussman sees when he's logged on and browsing the listings:

Chapter15_image023

As you can see, I've added a couple of other users who are also selling items. Our Toshiba has some competition in the marketplace!

How It Works

The technique used to display these items is very similar to that used in the List/Edit Sale Items page. The main difference is that this page displays all items for sale by all users (while the List/Edit Sale Items page displays only those items being sold by the current user).

As we mentioned before, any users can view this page – whether they're registered with the system or not. If the user has already logged-in, then we'll give a visual indication that the system recognizes who they are. To do this, we'll check the value of the session-level PersonID variable – if it is populated then we know that we're dealing with a currently logged-in user, and in this case we can respond by displaying a special greeting on the page:

If Session("PersonID") <> "" Then

Response.Write "<H3>Welcome " & Session("GivenName") & "</H3>"

End If

As we've already seen, all of the information about the user is stored in session-level variables, so retrieving it is quick and easy.

We use a recordset to hold the details of the items that are currently for sale. This recordset is populated from the results of a SQL query. The query is contained in the strSQL string, and is executed when we open the recordset against the database:

Dim rsItems

strSQL = "SELECT * FROM Item " & _

"WHERE ExpirationDate > #" & FormatDateTime(Now,2) & "# " & _

"AND ItemStatus = 'Active';"

Set rsItems = Server.CreateObject("ADODB.Recordset")

rsItems.Open strSQL, objConn

This query retrieves all of the records from the Item table whose ItemStatus value is Active, and whose ExpirationDate has not yet passed. We can check the EOF property of the new recordset to determine if there are any items for sale:

If Not rsItems.EOF Then

If the database contains items for sale, then the recordset will be non-empty. In this case we display the data for the 'for sale' items in a table. To do this, we loop through each record in the recordset, creating one table row for each item and populating the cells of the table with the data from the record. We create a hyperlink around the Item ID – this is to allow the user to bid on that item. However, we'll only make this hyperlink available to registered logged-in users – so again, we check against the value contained in the session-level PersonID variable to establish whether the current user is logged-in. Apart from this small subtlety, the display is very similar to the display used in the List/Edit Sale Items page:

Do While Not rsItems.EOF

Response.Write "<TR ALIGN=CENTER>"

If Session("PersonID") <> "" Then

Response.Write _

"<TD><A href=""Bid.asp?Item=" & rsItems("ItemID") & "">" & _

rsItems("ItemID") & "</A></TD>"

Else

Response.Write "<TD>" & rsItems("ItemID") & "</TD>"

End If

Response.Write _

"<TD>" & rsItems("ItemName") & "</TD>" & _

"<TD>" & FormatCurrency(rsItems("AskingPrice")) & "</TD>" & _

"<TD>" & FormatDateTime(rsItems("ListingDate"),2) & "</TD>" & _

"</TR>"

rsItems.MoveNext

Loop

If there are no items for sale, then the rsItems recordset will be empty. In this case, we just display a message to the user to indicate that there are no items currently for sale:

Else

Response.Write "<CENTER><H2>No items currently for sale</H2></CENTER>"

End If

You may have noticed the Current Bid and Bid Time columns in the screenshot above. These will be used later on, to display any current bid information about each item. Our next task is to talk about how to bid for an item; after that, we'll return to this page and show you how to display the bid information.

Bidding for an Item

The user uses BrowseListings.asp to browse the list of items for sale, and selects an item that they'd like to purchase. When this happens, the user can place a bid on that item, by clicking on the hyperlinked ItemID. This will take them to a page called Bid.asp, where they can enter their bid.

Try It Out – Bidding for an Item

1. Create a new ASP file, and enter the following. Don't forget that the JavaScript is case-sensitive:

<HTML>

<HEAD>

<TITLE>Wrox Classifieds - Item Bid</TITLE>

</HEAD>

Dim rsItem, strItemName, strDescription

Set rsItem = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM Item WHERE ItemID =" & Request("Item")

rsItem.Open strSQL, objConn

strItemName = rsItem("ItemName")

strDescription = rsItem("Description")

rsItem.Close

Set rsItem = Nothing

Dim rsBids

Set rsBids = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM Bid WHERE ItemID =" & Request("Item") & _

" ORDER BY TimeStamp DESC;"

rsBids.Open strSQL, objConn

<CENTER><H1>Wrox Classifieds<BR>Bidding for <%= strItemName %></H1></CENTER>

<P>

Dim varHighBid

varHighBid = 0

If rsBids.EOF Then

Response.Write "So far, no bids have been placed on this item"

Else

Response.Write "Bid History (Highest bid first)" & _

"<TABLE BORDER=""2"" CELLSPACING=""3"" CELLPADDING=""3"">" & _

" <TR>" & _

" <TH>Bidder ID</TH>" & _

" <TH>Timestamp</TH>" & _

" <TH>Amount Bid</TH>" & _

" <TH>Last Change</TH>" & _

" </TR>"

Do While Not rsBids.EOF

Response.Write _

" <TR>" & _

" <TD>" & rsBids("BidderID") & "</TD>" & _

" <TD>" & rsBids("Timestamp") & "</TD>" & _

" <TD ALIGN=RIGHT>" & FormatCurrency(rsBids("BidAmount")) & "</TD>" & _

" <TD ALIGN=RIGHT>" & FormatCurrency(rsBids("BidChange")) & "</TD>" & _

" </TR>"

If varhighBid = 0 Then varHighBid = rsBids("BidAmount")

rsBids.MoveNext

Loop

rsBids.Close

Response.Write "</TABLE>"

End If

<FORM NAME="frmBid" ACTION="AddBid.asp" METHOD="POST"

onSubmit="return VerifyData()">

<P>

<TR>

</TR>

<TR>

<TD>Description:</TD>

</TR>

<TR>

</TR>

<TR>

</TR>

</TABLE>

</FORM>

<HR>

<TD WIDTH=33%><A href="BrowseListings.asp">Browse the listings</A></TD>

<TD WIDTH=33%>Add Sale Items</TD>

<TD WIDTH=33%><A href="Register.asp?update=True">Edit Registration Info</A></TD>

</TR>

</TABLE>

</BODY>

<!--

function VerifyData()

{

if (document.frmBid.Bid.value <= <%= varHighBid %>)

{

alert ("You must bid higher than the previous bid of " +

"<%= FormatCurrency(varHighBid) %>.");

return false;

}

else

return true;

}

-->

</SCRIPT>

</HTML>

2. Save the file as Bid.asp, in the same folder as your other ASP files.

3. Create another blank file in your editor and enter the following code:

Dim objCmd, rsHighBid, varHighBid

Set objCmd = Server.CreateObject("ADODB.Command")

Set objCmd.ActiveConnection = objConn

strSQL = "SELECT Max(BidAmount) AS MaxBidAmount FROM Bid " & _

"WHERE ItemID = " & Request("ItemID") & ";"

objCmd.CommandType = adCmdText

objCmd.CommandText = strSQL

Set rsHighBid = objCmd.Execute

If IsNull( rsHighBid("MaxBidAmount") ) Then

varHighBid = 0

Else

varHighBid = rsHighBid("MaxBidAmount")

End If

rsHighBid.Close

Set rsHighBid = Nothing

Dim rsBid

Set rsBid = Server.CreateObject("ADODB.Recordset")

rsBid.Open "Bid", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

rsBid.AddNew

rsBid("ItemID") = Request.Form("ItemID")

rsBid("BidderID") = Session("PersonID")

rsBid("BidAmount") = CCur(Request.Form("Bid"))

rsBid("BidChange") = CCur(Request.Form("Bid")) - varHighBid

rsBid.Update

Response.Redirect "BrowseListings.asp"

4. Save the file as AddBid.asp, in the same folder as your other ASP files.

5. From the Browse page (BrowseListings.asp), click on the ItemID of an item that you want to bid on:

Chapter15_image024

I've added a couple of 'previous bids' here, so you can see what they look like. The first time you make a bid, you'll receive the message "No bids currently placed" instead of the 'Bid History' table.

6. Insert your bid, and hit the Bid on Item button. This will process the bid and return you to BrowseListings.asp. There's still nothing in the Current Bid and Bid Time columns yet, but we'll fix that in a few pages time:

Chapter15_image025

How It Works

Bid.asp is the page on which a user can place a bid. There are two parts to this page. First, we display a summary of all previous bids on that item; and second, we provide a form that allows the user to place a new bid.

The first step is to retrieve the descriptive information about the item. This information is stored in the Item table. We create a recordset called rsItem, and populate it using a SQL query to retrieve the data from the Item table. Then we copy the item's name and description in local variables for later use (and so that we can close and release the recordset):

Dim rsItem, strItemName, strDescription

Set rsItem = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM Item WHERE ItemID =" & Request("Item")

rsItem.Open strSQL, objConn

strItemName = rsItem("ItemName")

strDescription = rsItem("Description")

rsItem.Close

Set rsItem = Nothing

Next, we will turn our attention to building the list of previous bids for the item. The information about bids is stored in the Bid table. We create a new recordset called rsBids, and write a SQL query (contained in strSQL) to retrieve all of the bids for the current item. We populate rsBids by executing its Open method, which executes the strSQL query against the database:

Dim rsBids

Set rsBids = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT * FROM Bid WHERE ItemID =" & Request("Item") & _

" ORDER BY TimeStamp DESC;"

rsBids.Open strSQL, objConn

Note that the SQL query uses the ORDER BY clause to arrange the bids according to the time that they were placed. The DESC modifier ensures that, within the recordset, the records are ordered in reverse order – that is, from newest to oldest.

One important piece of information that we'll need is the value of the highest bid to date. As we'll see shortly, any bid placed must be higher than any other bids that have been placed on that item. This means that the highest bid on an item is also the most recent bid on that item. Therefore, since we have retrieved our records in reverse order of date/time, the previous highest bid corresponds to the first record in the recordset. We'll store the value of this previous highest bid in the local variable varHighBid. We initialize this to 0, and we'll capture the highest bid value in a moment:

Dim varHighBid

varHighBid = 0

If there are no previous bids then rsBids will be empty, and the recordset's EOF file will immediately be True. In this case, we display an appropriate message:

If rsBids.EOF Then

Response.Write "So far, no bids have been placed on this item"

Otherwise, we loop through all of the bids and display each bid on a new row of an HTML table. Notice that the value of varHighBid will only be 0 on the first iteration of the loop. We use that fact to copy the highest bid value into our varHighBid variable:

Else

Response.Write "Bid History (Highest bid first)" & _

"<TABLE BORDER=""2"" CELLSPACING=""3"" CELLPADDING=""3"">" & _

" <TR>" & _

" <TH>Bidder ID</TH>" & _

" <TH>Timestamp</TH>" & _

" <TH>Amount Bid</TH>" & _

" <TH>Last Change</TH>" & _

" </TR>"

Do While Not rsBids.EOF

Response.Write _

" <TR>" & _

" <TD>" & rsBids("BidderID") & "</TD>" & _

" <TD>" & rsBids("Timestamp") & "</TD>" & _

" <TD ALIGN=RIGHT>" & FormatCurrency(rsBids("BidAmount")) & "</TD>" & _

" <TD ALIGN=RIGHT>" & FormatCurrency(rsBids("BidChange")) & "</TD>" & _

" </TR>"

If varhighBid = 0 Then varHighBid = rsBids("BidAmount")

rsBids.MoveNext

Loop

rsBids.Close

Response.Write "</TABLE>"

End If

Notice that we've used the FormatCurrency function, which helps us to display the amount of each bid and the change since the last bid in a currency format.

Having displayed the bid history, we can display the form – this will allow the user to enter the amount that they wish to bid. This form will send its contents to the AddBid.asp script file for processing:

<FORM NAME="frmBid" ACTION="AddBid.asp" METHOD="POST"

onSubmit="return VerifyData()">

The rest of the form is quite simple – there's nothing new there so we won't discuss it here. The last thing to note about Bid.asp is that we've included a little client-side validation script, to parse the value of the bid being submitted. This involves including an onSubmit parameter to the <FORM> tag, which tells the browser to execute the VerifyData() method when the form's submit button is pressed, but before the form is actually submitted to the web server.

The VerifyData() function executes client-side to ensure that the bid being submitted is higher than the previous highest bid. By running the function client-side, we can perform this check without making a round trip to the server:

<!--

function VerifyData()

{

if (document.frmBid.Bid.value <= <%= varHighBid %>)

{

alert ("You must bid higher than the previous bid of " +

"<%= FormatCurrency(varHighBid) %>.");

return false;

}

else

return true;

}

-->

</SCRIPT>

If the bid is verified as being higher than the previous highest bid, then the form will continue to be submitted. Otherwise, the form will not be submitted and we display an error message box for the user:

Chapter15_image026

When the form is successfully submitted for processing, the processing is performed by the AddBid.asp script file. AddBid.asp is like some of the other files we've seen in this application, in that it has no user interface. It is simply responsible for adding the bid information to the database, and redirecting the user towards another page.

The first task in AddBid.asp is to determine the item's previous high bid. To do this, we first create an ADO Command object called objCmd – we will use it to execute a SQL statement against the database. We must set three properties of the Command object, before it can function properly:

The ActiveConnection property should specify our database connection. Here, we reference objConn – the same ADO Connection object that we've used for a database connection throughout this application.
We're going to use a SQL query to retrieve information, so we need to set the CommandType property to adCmdText. This tells the Command object that we will be passing a text command, rather than a table name, for it to process.
The CommandText property should be assigned the SQL statement that we have created in the strSQL variable.

Here's the code for all that:

Set objCmd = Server.CreateObject("ADODB.Command")

Set objCmd.ActiveConnection = objConn

strSQL = "SELECT Max(BidAmount) AS MaxBidAmount FROM Bid " & _

"WHERE ItemID = " & Request("ItemID") & ";"

objCmd.CommandType = adCmdText

objCmd.CommandText = strSQL

Then we call the Command object's Execute method. This executes the query against the database, and returns a recordset full of data. We assign the resulting recordset to a variable called rsHighBid:

Set rsHighBid = objCmd.Execute

We've used the Command object here to demonstrate that there's more than one way to populate a recordset! We could have avoided the Command object altogether, and populated the recordset by using its Open method – as we've done elsewhere in this application.

Before we go on, note that our SQL statement performs a calculation as it retrieves information from the Bid table. We're only interested in the time and value of the previous highest bid, so we use the Max() function inside of the SQL statement. This function will be performed on each record that matches the WHERE clause – of all those records, only the record with the highest BidAmount value will be returned.

We have also added an AS statement to the SQL query, which renames the results of the Max() functions within the recordset. This allows us to access the recordset's field as MaxBidAmount, instead of Max(BidAmount).

Since our SQL query asks for the highest bid value, there will always be a result returned. That means we can't use the EOF property to determine whether or not any information is present. Instead, we must check whether each value is Null. A Null value implies that there are no previous bids; and therefore the high bid is 0:

If IsNull( rsHighBid("MaxBidAmount") ) Then

varHighBid = 0

Else

varHighBid = rsHighBid("MaxBidAmount")

End If

Once we have retrieved the high bid information, we can close this recordset and release its resources:

rsHighBid.Close

Set rsHighBid = Nothing

Now, we can add the new bid to the Bid table. To do this, we create a recordset (called rsBid) and populate it with the contents of the Bid table. We open it using the adLockOptimistic parameter, so that we can write the new bid to the table:

Dim rsBid

Set rsBid = Server.CreateObject("ADODB.Recordset")

rsBid.Open "Bid", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

Now we add a new record to the recordset and insert all the new bid details. The value of the BidChange field is calculated by subtracting the previous highest bid from the new bid. Once all of the data has been entered, the Update method is called to write the changes to the database:

rsBid.AddNew

rsBid("ItemID") = Request.Form("ItemID")

rsBid("BidderID") = Session("PersonID")

rsBid("BidAmount") = CCur(Request.Form("Bid"))

rsBid("BidChange") = CCur(Request.Form("Bid")) - varHighBid

rsBid.Update

Finally, we can close and release the recordset, and redirect the user back to the browse page:

rsBid.Close

Set rsBid = Nothing

Response.Redirect "BrowseListings.asp"

Updating the Listings to Show the Bid History

Now that we know how to retrieve bid information from the database, we can go back to some of the previous screens and add in the code to display the bid history.

Try It Out – Adding the Bid History Display

We will be adding bid history information to the BrowseListings.asp and Item.asp pages. We'll also need to add some additional code to the item deletion script in AddItem.asp.

1. Open the BrowseListings.asp file for editing.

2. Amend the BrowseListings.asp file as shown by the following highlighted fragments:

...

Response.Write _

"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">" & _

" <TR>" & _

" <TH>Item ID"

If Session("PersonID") <> "" Then

Response.Write "<BR><FONT SIZE=""-1"">Click to Bid</FONT>"

End If

Response.Write "</TH>" & _

" <TH>Name</TH>" & _

" <TH>Asking Price</TH>" & _

" <TH>Listing Date</TH>" & _

" <TH>Current Bid</TH>" & _

" <TH>Bid Time</TH>" & _

" </TR>"

Dim objCmd, rsBid

Set objCmd = Server.CreateObject("ADODB.Command")

Set objCmd.ActiveConnection = objConn

strSQL = "SELECT Max(BidAmount) AS MaxBidAmount, " & _

"Max(TimeStamp) AS LastBidTime FROM Bid"

objCmd.CommandType = adCmdText

Do While Not rsItems.EOF

Response.Write "<TR ALIGN=CENTER>"

If Session("PersonID") <> "" Then

Response.Write _

"<TD><A href=""Bid.asp?Item=" & rsItems("ItemID") & "">" & _

rsItems("ItemID") & "</A></TD>"

Else

Response.Write "<TD>" & rsItems("ItemID") & "</TD>"

End If

Response.Write _

"<TD>" & rsItems("ItemName") & "</TD>" & _

"<TD>" & FormatCurrency(rsItems("AskingPrice")) & "</TD>" & _

"<TD>" & FormatDateTime(rsItems("ListingDate"),2) & "</TD>"

objCmd.CommandText = strSQL & " WHERE ItemID = " & rsItems("ItemID") & ";"

Set rsBid = objCmd.Execute

If IsNull( rsBid("MaxBidAmount") ) Then

Response.Write _

"<TD COLSPAN=2><FONT SIZE=""-1"">No bids placed</FONT></TD>"

Else

Response.Write _

"<TD>" & FormatCurrency(rsBid("MaxBidAmount")) & "</TD>" & _

"<TD>" & rsBid("LastBidTime") & "</TD>"

Dim strSQL2, rsHighBidder

strSQL2 = "SELECT BidderID FROM Bid " & _

"WHERE ItemID = " & rsItems("ItemID") & _

" ORDER BY TimeStamp DESC"

objCmd.CommandType = adCmdText

objCmd.CommandText = strSQL2

Set rsHighBidder = objCmd.Execute

If rsHighBidder("BidderID") = Session("PersonID") Then

Response.Write "<TD><FONT size=""-1"" COLOR=""Red"">" & _

"You are the current high bidder</FONT></TD>"

End If

rsHighBidder.Close

Set rsHighBidder = Nothing

End If

rsBid.Close

Set rsBid = Nothing

Response.Write "</TR>"

rsItems.MoveNext

Loop

Response.Write "</TABLE>"

rsItems.close

Set rsItems = Nothing

3. Save these changes to the BrowseListings.asp file. (Alternatively, replace BrowseListings.asp with the contents of the BrowseListings_v2.asp, which you'll find with the source code that accompanies this book at the WROX websitehttp://www.wrox.com/WileyCDA/WroxTitle/productCd-0764543636,descCd-download_code.html.)

4. Click on the Browse the listings hyperlink to view the revised browse page:

Chapter15_image027

5. Next, open the ViewMySaleItems.asp file in your editor.

6. Make the following changes to the body of the ViewMySaleItems.asp file:

...

Response.Write _

"<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"">" & _

"<TR>" & _

" <TH>Item ID<BR><FONT SIZE=""-1"">Click to <BR>Edit/Delete</FONT></TH>" & _

" <TH>Name</TH>" & _

" <TH>Asking Price</TH>" & _

" <TH>Listing Date</TH>" & _

" <TH>Current Bid</TH>" & _

" <TH>Bid Time</TH>" & _

"</TR>"

Dim objCmd, rsBid

Set objCmd = Server.CreateObject("ADODB.Command")

Set objCmd.ActiveConnection = objConn

strSQL = "SELECT Max(BidAmount) AS MaxBidAmount, " & _

"Max(TimeStamp) AS LastBidTime FROM Bid"

objCmd.CommandType = adCmdText

Do While Not rsItems.EOF

Response.Write _

"<TR ALIGN=CENTER>" & _

" <TD><A href=""item.asp?Action=Edit&Item=" & rsItems("ItemID") & "">" & _

rsItems("ItemID") & "</A></TD>" & _

" <TD>" & rsItems("ItemName") & "</TD>" & _

" <TD>" & FormatCurrency(rsItems("AskingPrice")) & "</TD>" & _

" <TD>" & FormatDateTime(rsItems("ListingDate"),2) & "</TD>"

objCmd.CommandText = strSQL & " WHERE ItemID = " & rsItems("ItemID") & ";"

Set rsBid = objCmd.Execute

If IsNull( rsBid("MaxBidAmount") ) Then

Response.Write _

" <TD></TD><TD></TD>" & _

"</TR>"

Else

Response.Write _

" <TD>" & FormatCurrency(rsBid("MaxBidAmount")) & "</TD>" & _

" <TD>" & rsBid("LastBidTime") & "</TD>" & _

"</TR>"

End If

rsBid.Close

Set rsBid = Nothing

rsItems.MoveNext

Loop

Response.Write "</TABLE>"

Else ' current user has no items for sale

Response.Write "<CENTER><H2>No items currently for sale</H2></CENTER>"

End If

rsItems.close

7. Save these changes to the ViewMySaleItems.asp file. (Alternatively, replace ViewMySaleItems.asp with the contents of ViewMySaleItems_v2.asp – again, this is available as part of the source code that accompanies this book, at http://www.wrox.com.)

8. Click on the List/Edit Sale Items hyperlink to view the changes to the page:

Chapter15_image028

9. Now open the AddItem.asp file and add the following highlighted code:

If Request.Form("Delete") <> "" Then ' user wishes to delete an item

rsItem.Filter = "ItemID = " & Request("ItemID")

If Not rsItem.EOF Then rsItem.Delete

Dim rsBids

Set rsBids = Server.CreateObject("ADODB.Recordset")

strSQL = "DELETE FROM Bid WHERE ItemID = " & Request("ItemID")

rsBids.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText

Set rsBids = Nothing

Else ' user wishes to edit/add an item

10. Save these changes to the AddItem.asp file. (Alternatively, replace AddItem.asp with the contents of AddItem_v2.asp – available at http://www.wrox.com.)

How It Works

Now that we know how to retrieve information about bids from the database, it is relatively simple to add that information wherever we display information about items.

In the Browse the Listings page (BrowseListings.asp), we can add two new pieces of information concerning bids – the time and value of the most recent bid. Further, if the current user is currently the highest bidder on an item, then we can provide a visual indicator of that status.

So, in BrowseListings.asp we first need to need to prepare our ADO Command object. We'll only need one Command object in this page, but we'll use it repeatedly – once for each item – to retrieve the last bid and timestamp for each item. The SELECT and FROM portions of the SQL statement will remain the same, so we store those in a variable called strSQL:

Dim objCmd, rsBid

set objCmd = Server.CreateObject("ADODB.Command")

set objCmd.ActiveConnection = objConn

strSQL = "SELECT Max(BidAmount) AS MaxBidAmount, " & _

"Max(TimeStamp) AS LastBidTime FROM Bid"

objCmd.CommandType = adCmdText

In order to generate the HTML table, we loop through each item in the Item table (just as we did previously). But in our first version of this page we only displayed data relating to the item; in this new version, we'll also display the latest bid information. So for each item in the list, we do this by querying the Bid table using the SELECT and FROM clauses in strSQL, and appending a WHERE clause to specify the item that we're currently dealing with. We call the Execute method of the Command object, and populate the rsBid recordset:

objCmd.CommandText = strSQL & " WHERE ItemID = " & rsItems("ItemID") & ";"

Set rsBid = objCmd.Execute

We're retrieving computed information, rather than information from a record itself – so the rsBid recordset will always contain exactly one record. If the value of MaxBidAmount (within the recordset) is not null, then we know that we have a previous bid – so we can display the amount along with the timestamp for the user:

If IsNull( rsBid("MaxBidAmount") ) Then

Response.Write _

"<TD COLSPAN=2><FONT SIZE=""-1"">No bids placed</FONT></TD>"

Else

Response.Write _

"<TD>" & FormatCurrency(rsBid("MaxBidAmount")) & "</TD>" & _

"<TD>" & rsBid("LastBidTime") & "</TD>"

The final new part of BrowseListings.asp determines whether it's the current user that placed the current highest bid. Ideally, we'd use a single SQL query to retrieve both the high bid amount and the highest bid PersonID – but the use of the Max() function does not permit that. However, we do benefit from the reuse of the Command object – we can just create a new SQL query to retrieve the BidderID from the bid record containing the latest bid for this item. If this BidderID value matches the value currently stored in the session-level PersonID variable, then we know that the current user is also the high bidder – and so we can display a message stating so:

Dim strSQL2, rsHighBidder

strSQL2 = "SELECT BidderID FROM Bid " & _

"WHERE ItemID = " & rsItems("ItemID") & _

" ORDER BY TimeStamp DESC"

objCmd.CommandType = adCmdText

objCmd.CommandText = strSQL2

Set rsHighBidder = objCmd.Execute

If rsHighBidder("BidderID") = Session("PersonID") Then

Response.Write "<TD><FONT size=""-1"" COLOR=""Red"">" & _

"You are the current high bidder</FONT></TD>"

End If

rsHighBidder.Close

Set rsHighBidder = Nothing

The changes to ViewMySaleItems.asp are very nearly the same as those that we've made to BrowseListings.asp. The only significant difference is that we don't need the additional column for the You are the current high bidder message.

Finally, we need to look at one part of the AddItem.asp script file. You'll recall that the purpose of this script is to add, edit, or delete items in the Item table of the database. When we delete an item, it's also a good idea to clean up all of the bids that are associated with that item. To do this, we create a new recordset object, called rsBids, which issues a SQL query. This SQL query is a bit different from those that we've seen before: instead of using a SELECT statement to retrieve information from a table, we use a DELETE statement:

Dim rsBids

Set rsBids = Server.CreateObject("ADODB.Recordset")

strSQL = "DELETE FROM Bid WHERE ItemID = " & Request("ItemID")

rsBids.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic, adCmdText

Set rsBids = Nothing

This will delete every record in the Bid table whose ItemID value matches the value of Request("ItemID"). When we use a DELETE SQL query to open a recordset, the deletion is carried out and then the recordset is automatically closed.

<< 15.3.3- Managing Items for Sale

Chapter15

15.3.5- Completing a Sale >>