15.3.5- Completing a Sale

by NT Community Manager.
Last Updated by Sarah Welna.

Public Categorized as 15. Writing an Application.

Not tagged.

Completing a Sale

The last section of this chapter explains how we'll tie up a sale – by having the seller accept a bid, and then having the successful bidder acknowledge the acceptance and thus complete the sale.

Accepting a Bid

First, we'll look at how we can allow the seller to accept a bid. When this happens, the buyer is notified, and can accept (or reject) the deal at that point. When the deal is accepted by both parties, the information is logged to the database.

Try It Out – Accepting a Bid

1. Open the Item.asp file.

2. Add the following highlighted code, between the end </TABLE> and end </FORM> tags:

...

</TR>

</TABLE>

If Request.QueryString("Action") = "Edit" Then

Dim rsBids

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

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

" ORDER BY TimeStamp DESC;"

rsBids.Open strSQL, objConn

If rsBids.EOF Then

Response.Write "<HR><P>No bids currently placed"

Else

Response.Write "<HR><P>Bid History (Newest to Oldest)<P>" & _

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

" <TR>" & _

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

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

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

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

" </TR>"

Dim blnFirst

blnFirst = True

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>"

If blnFirst Then

Response.Write _

"<TD ALIGN=RIGHT><A href=""SaleDetailsForSeller.asp?Item=" & _

Request("Item") & "&BidID=" & rsBids("BidID") & "">" & _

"Click to sell to this bidder</A></TD>"

blnFirst=False

End If

Response.Write "</TR>"

rsBids.MoveNext

Loop

rsBids.Close

Response.Write "</TABLE>"

End If

</FORM>

...

3. Don't forget to save your changes to this file, with the same filename. Alternatively, you'll find these changes contained in the file Item_v2.asp, in the source code files that accompany this book.

4. From the Selling Items page (ViewMySaleItems.asp), click on the ItemID of an item that has a current bid on it. That will take you to the updated Item.asp page. In this screenshot, we're looking at the details and bids that have been made on our Toshiba laptop:

Chapter15_image029

5. Now, create a new ASP file and enter the following code:

<HTML>

<HEAD>

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

</HEAD>

<CENTER><H1>Wrox Classifieds<BR>Item Sale</H1></CENTER>

<P>

Dim rsBid

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

strSQL = "SELECT BidderID, FamilyName, GivenName, EMailAddress, " & _

"BidAmount, ItemName, AskingPrice FROM Person " & _

"INNER JOIN (Item INNER JOIN Bid ON Item.ItemID = Bid.ItemID) " & _

"ON Person.PersonID = Bid.BidderID " & _

"WHERE (((Bid.BidID)=" & Request.QueryString("BidID") & "));"

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

<TR>

</TR>

<TR>

<TD>Asking Price:</TD>

</TR>

<TR>

<TD>Bid Amount:</TD>

</TR>

<TR>

<TD>Bidder:</TD>

<%= rsBid("FamilyName") & ", " & rsBid("GivenName") %></TD>

</TR>

<TR>

</TR>

</TABLE>

Dim objCmd

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

strSQL = "UPDATE Item SET ItemStatus = 'Pending' " & _

"WHERE (((Item.ItemID)=" & Request.QueryString("Item") & "));"

objCmd.CommandText = strSQL

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

objCmd.Execute

strSQL = "INSERT INTO Sale (ItemID, BuyerID, WinningBid, SellerApproval) " & _

"VALUES (" & Request.QueryString("Item") & ", " & _

rsBid("BidderID") & ", " & rsBid("BidAmount") & ", Yes);"

objCmd.CommandText = strSQL

objCmd.Execute

Response.Write "<P>" & _

"<CENTER>Sale Completed - The purchaser will now be notified</CENTER>"

<HR><P>

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

</HTML>

6. Save the file as SaleDetailsForSeller.asp, in the usual directory.

7. Click on the Click to sell to this bidder hyperlink to begin the sale process.

Chapter15_image030

How It Works

To begin the sales process, we need to add the bid history to Item.asp. We do this in much the same way that we added the bid history to BrowseListings.asp and ViewMySaleItems.asp. The primary difference here is that we'll retrieve all the bids, not just the highest.

To retrieve the bids from the database, we use a SQL query to generate a recordset. This query will return all of the bids for the current item, ready-sorted into order from newest to oldest:

If Request.QueryString("Action") = "Edit" Then

Dim rsBids

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

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

" ORDER BY TimeStamp DESC;"

rsBids.Open strSQL, objConn

We're going to display these bids in a table – with each row corresponding to one bid. Before we go through the list of bids, we will create a local variable called blnFirst and set it to True. This will be used to identify the first time through the loop:

Dim blnFirst

blnFirst = True

Do While Not rsBids.EOF

Now we can generate the table rows:

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>"

If blnFirst Then

Response.Write _

"<TD ALIGN=RIGHT><A href=""SaleDetailsForSeller.asp?Item=" & _

Request("Item") & "&BidID=" & rsBids("BidID") & "">" & _

"Click to sell to this bidder</A></TD>"

blnFirst=False

End If

Response.Write "</TR>"

rsBids.MoveNext

Loop

Because the SQL query specified ORDER BY TimeStamp DESC, and because we know each successive bid was higher than the last, we know that the first iteration through this loop corresponds to the highest bid. We make the assumption that, if the seller wishes to sell the item, then they'll want to sell it to the highest bidder. So, when the blnFirst value is True we display the Click to sell to this bidder hyperlink – by clicking on this link, the seller agrees to sell the item to the highest bidder. After we've displayed the hyperlink, we set blnFirst to False – so it only appears once on the page.

So what happens when the Click to sell to this bidder link is clicked? The link is an anchor tag that directs the user to SaleDetailsForSeller.asp. This page updates the database tables and displays information about the sale. The information displayed here comes from data contained in three different tables:

The Bid table has the ID of the winning bidder and the amount that they bid.
The Item table has the name of the item and its asking price.
The Person table has the name and e-mail address of the bidder.

To retrieve all of this information at once, we will be creating a SQL statement known as a JOIN:

Dim rsBid

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

strSQL = "SELECT BidderID, FamilyName, GivenName, EMailAddress, " & _

"BidAmount, ItemName, AskingPrice FROM Person " & _

"INNER JOIN (Item INNER JOIN Bid ON Item.ItemID = Bid.ItemID) " & _

"ON Person.PersonID = Bid.BidderID " & _

"WHERE (((Bid.BidID)=" & Request.QueryString("BidID") & "));"

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

At the beginning of this chapter, we discussed primary keys and foreign keys. Well, this is where they really come into their own. The Bid table contains foreign keys for both the Item table and the Person table. To create the join, we use the value of these foreign keys to pull information from the other tables.

To better examine how this SQL statement works, let's break it down into pieces. The first piece is the WHERE clause:

strSQL = "SELECT BidderID, FamilyName, GivenName, EMailAddress, " & _

"BidAmount, ItemName, AskingPrice FROM Person " & _

"INNER JOIN (Item INNER JOIN Bid ON Item.ItemID = Bid.ItemID) " & _

"ON Person.PersonID = Bid.BidderID " & _

"WHERE (((Bid.BidID)=" & Request.QueryString("BidID") & "));"

This will retrieve every record from the Bid table, whose BidID value is equal to the value passed into this page in the querystring. Since the BidID is a primary key of the Bid table, this WHERE clause can return at most one record.

Next, we will look at the SELECT portion of the SQL statement:

strSQL = "SELECT BidderID, FamilyName, GivenName, EMailAddress, " & _

"BidAmount, ItemName, AskingPrice FROM Person " & _

"INNER JOIN (Item INNER JOIN Bid ON Item.ItemID = Bid.ItemID) " & _

"ON Person.PersonID = Bid.BidderID " & _

"WHERE (((Bid.BidID)=" & Request.QueryString("BidID") & "));"

The SELECT statement is the same as other select statements except in one detail: namely, that the fields that we have named are coming from different tables. The BidderID and BidAmount fields are in the Bid table, while FamilyName, GivenName, and EMailAddress are from Person, and ItemName and AskingPrice are from Item.

It is in the FROM clause that the tables are tied together:

strSQL = "SELECT BidderID, FamilyName, GivenName, EMailAddress, " & _

"BidAmount, ItemName, AskingPrice FROM Person " & _

"INNER JOIN (Item INNER JOIN Bid ON Item.ItemID = Bid.ItemID) " & _

"ON Person.PersonID = Bid.BidderID " & _

"WHERE (((Bid.BidID)=" & Request.QueryString("BidID") & "));"

Starting from the inside, we have joined Item and Bid together using an INNER JOIN for those records where the value of ItemID in the Item table matches the value of ItemID in the Bid table (that is, on Item.ItemID = Bid.ItemID). An INNER JOIN means that we will retrieve only the records that match on both sides. Then, moving outward, we join the results of the first join with the Person table, where the PersonID in the Person table matches the BidderID in the Bid table (that is, on Person.PersonID = Bid.BidderID). All of these records will be combined together and returned as a single recordset, rsBid, which we use to display the appropriate information to the seller.

Once that's done, we can update the database tables to begin recording the sale. We use an ADO Command object called objCmd to interact with the database:

Dim objCmd

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

This interaction will be in the form of SQL statements. We've already seen some SELECT and DELETE SQL statements in this chapter; now, we'll use a couple of new SQL commands. Here's the first:

strSQL = "UPDATE Item SET ItemStatus = 'Pending' " & _

"WHERE (((Item.ItemID)=" & Request.QueryString("Item") & "));"

objCmd.CommandText = strSQL

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

objCmd.Execute

The UPDATE SQL statement is used to change the value of fields in an existing record. In our example, we will be changing the value of the ItemStatus field to Pending, in the record that corresponds to the item being sold. In this case, we're updating only one record.

Using the SQL UPDATE statement is probably overkill – we could have just used a recordset. But, for updating multiple records at once, nothing can beat the UPDATE statement as it is much faster.

Here's the second new SQL statement:

strSQL = "INSERT INTO Sale (ItemID, BuyerID, WinningBid, SellerApproval) " & _

"VALUES (" & Request.QueryString("Item") & ", " & _

rsBid("BidderID") & ", " & rsBid("BidAmount") & ", Yes);"

objCmd.CommandText = strSQL

objCmd.Execute

The INSERT SQL statement is used to add new records to an existing table. This is very similar to opening a recordset and then using the AddNew method. Again, either method could be used in this case. We just wanted to look at something new. The INSERT statement has three separate parts:

The target table: First, we have to identify that table into which we want to insert the record. In this example, we will be adding a record to the Sale table.
The target fields: Second, we list the fields to which we will be adding data. By default, you add data to all the fields. In this case, we only want to add data to four fields, so we list them (enclosed in parentheses) after the name of the table.
The values to insert: Finally, we identify the actual values that will be inserted into the specified fields. It's important to list these values in the order that corresponds to the list of fields that we've already specified.

When we use the Command object's Execute method to run the SQL statement, we create a new record in the Sale table, whose fields will be populated with the specified values.

Notifying the Bidder

Now that all of the database tables have been updated, there's just one last step: to notify the buyer that their bid has been accepted.

Try It Out – Notifying the Buyer

1. Open the MenuForRegisteredUsers.asp file.

2. Add the following highlighted line at the very beginning of MenuForRegisteredUsers.asp:

If Session("PersonID") = "" then

Response.Redirect "Login.asp"

End If

3. Also, add the following highlighted code to the body of MenuForRegisteredUsers.asp:

...

<UL>

<LI>Browse for items on sale

<LI>Bid for items on sale

<LI>Sell your own items on these pages

</UL>

Dim rsPendingSales

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

strSQL = "SELECT SaleID, WinningBid, ItemName " & _

"FROM Sale INNER JOIN Item ON Sale.ItemID = Item.ItemID " & _

"WHERE BuyerID=" & Session("PersonID") & " AND BuyerAcceptance=FALSE;"

rsPendingSales.Open strSQL, objConn

If Not rsPendingSales.EOF Then

Response.Write "You have placed the winning bid on these items:<P>" & _

"<TABLE cellpadding=3 border=1>" & _

"<TR>" & _

"<TH>Item Name<BR><FONT SIZE=-1>Click to Complete Purchase</FONT></TH>" & _

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

"</TR>"

Do While Not rsPendingSales.EOF

Response.Write _

"<TR>" & _

" <TD ALIGN=CENTER><A href=""BuyerAcceptance.asp?SaleID=" & _

rsPendingSales("SaleID") & "">" & _

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

" <TD ALIGN=RIGHT>" & _

FormatCurrency(rsPendingSales("WinningBid")) & "</TD>" & _

"</TR>"

rsPendingSales.MoveNext

Loop

Response.Write "</TABLE><P>"

End If

<HR>

...

4. Now save the changes that you've made to MenuForRegisteredUsers.asp. (Alternatively, use the code contained in MenuForRegisteredUsers_v2.asp – available as part of the source code that supports this chapter, at the WROX website http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764543636,descCd-download_code.html.)

5. Now create a new ASP file, and type in the following code:

Dim rsSale, strItemID

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

rsSale.Open "Sale", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

rsSale.Filter = "SaleID = " & Request("SaleID")

rsSale("BuyerAcceptance") = True

rsSale("CompletionDate") = Now

strItemID = rsSale("ItemID")

rsSale.Update

rsSale.Close

Set rsSale = Nothing

Dim objCmd

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

strSQL = "UPDATE Item SET ItemStatus = 'Sold' " & _

"WHERE (((Item.ItemID)=" & strItemID & "));"

objCmd.CommandText = strSQL

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

objCmd.Execute

Set objCmd = Nothing

Response.Redirect "MenuForRegisteredUsers.asp"

6. Save the file as BuyerAcceptance.asp, in the usual directory.

7. View the Registered User Home page by logging into the system again – a successful login takes you to MenuForRegisteredUsers.asp. If you've had any bids accepted, then you'll be notified of it here.

In this screenshot, that's exactly what's happened. David's seller details page is a little different than before, because we just accepted his bid on the Toshiba:

Chapter15_image031

8. Click on the hyperlinked item name to acknowledge the bid acceptance. This will update the database accordingly, and return you to MenuForRegisteredUsers.asp.

How It Works

Any user who has placed a bid on an item will want to be informed if their bid is accepted by the seller. We designed our application so that the Registered Users home page is displayed when a user logs into the system – so this is an excellent opportunity to notify the user of any successful bids that they made.

To do this, we've added some code to MenuForRegisteredUsers.asp, which checks the Sale table for any pending sales associated with the current user. We create a recordset and populate it by executing a SQL query which pulls information from both the Sale table and the Item table:

Dim rsPendingSales

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

strSQL = "SELECT SaleID, WinningBid, ItemName " & _

"FROM Sale INNER JOIN Item ON Sale.ItemID = Item.ItemID " & _

"WHERE BuyerID=" & Session("PersonID") & " AND BuyerAcceptance=FALSE;"

rsPendingSales.Open strSQL, objConn

Again, we've used a JOIN to pull information from the two tables – joining the Sale table to the Item table using the ItemID field that exists in each table. We retrieve only those Sale records whose BuyerID field matches the current user's PersonID, and whose BuyerAcceptance field is FALSE (indicating that the bidder has not yet acknowledged the successful sale).

If there are any pending sales for this user to acknowledge, then the rsPendingSales recordset will be non-empty – the cursor will be on the first record in the recordset and therefore EOF will be false. Using this, we can display the details of each pending sale in the rows of a table:

If Not rsPendingSales.EOF Then

Response.Write "You have placed the winning bid on these items:<P>" & _

"<TABLE cellpadding=3 border=1>" & _

"<TR>" & _

"<TH>Item Name<BR><FONT SIZE=-1>Click to Complete Purchase</FONT></TH>" & _

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

"</TR>"

Do While Not rsPendingSales.EOF

Response.Write _

"<TR>" & _

" <TD ALIGN=CENTER><A href=""BuyerAcceptance.asp?SaleID=" & _

rsPendingSales("SaleID") & "">" & _

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

" <TD ALIGN=RIGHT>" & _

FormatCurrency(rsPendingSales("WinningBid")) & "</TD>" & _

"</TR>"

rsPendingSales.MoveNext

Loop

Response.Write "</TABLE><P>"

End If

In the first column, the name of the item is displayed as a hyperlink. The user can click on the hyperlink to indicate that they approve the purchase of the item. This loads the BuyerAcceptance.asp script.

BuyerAcceptance.asp updates the information in the Sale table, to take account of the bidder's acknowledgement. Again, we use an ADO Recordset object. We're interested in one specific record of the Sale table, which we retrieve by setting a Filter on the recordset:

Dim rsSale, strItemID

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

rsSale.Open "Sale", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

rsSale.Filter = "SaleID = " & Request("SaleID")

This places the recordset's cursor at the record we want to change. Now we just set the BuyerAcceptance field to True and set the sale CompletionDate to the current date and time. Also, we take the ItemID associated with the sale and copy it into a temporary local variable, strItemID, because we'll need it to update the Item table. Once the fields are updated, we can write the changes to the database and then close the recordset:

rsSale("BuyerAcceptance") = True

rsSale("CompletionDate") = Now

strItemID = rsSale("ItemID")

rsSale.Update

rsSale.Close

Set rsSale = Nothing

The final step is to update the ItemStatus field in the Item table:

Dim objCmd

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

strSQL = "UPDATE Item SET ItemStatus = 'Sold' " & _

"WHERE (((Item.ItemID)=" & strItemID & "));"

objCmd.CommandText = strSQL

objCmd.CommandType = adCmdText

Set objCmd.ActiveConnection = objConn

objCmd.Execute

Set objCmd = Nothing

Response.Redirect "MenuForRegisteredUsers.asp"

Here, we've adapted code that we first saw in SaleDetailsForSeller.asp (where it was used to change the ItemStatus value to Pending). Here, we need to make one further change – to update the ItemStatus value to Sold. To find the right record, we use the ItemID value we stored in the local strItemID variable a moment ago. Once we've made this change, we can send the user back to the Registered Users home page – which is generated dynamically, and will display the most up-to-date list of pending sales.