15.3.3- Managing Items for Sale

by NT Community Manager.
Last Updated by Jim Minatel.

Public Categorized as 15. Writing an Application.

Not tagged.

Managing Items for Sale

Once a user is registered, they can start posting items for sale. When posting an item for sale, the user must provide some information about it. The information involved for each sale item is:

A descriptive name of the item
A long description of the item
The desired price for the item
The price at which the system should notify you
The date after which the item will no longer be for sale

Some of this information will be displayed to potential buyers, and some is used internally in the system. And there's more to managing the 'for sale' items than just adding items to a list – we need a mechanism that allows the seller to edit the details or to remove an item from listings. We'll cover these interfaces in the following Try It Out's.

Viewing one's Own Sale Items

First, we'll write a page called ViewMySaleItems.asp – this page displays a list of the items that the current user has already made available for sale.

Try It Out – Displaying the Items that a User Has for Sale

1. Create a new ASP file; enter the following code into your editor:

If Session("PersonID") = "" Then

Response.Redirect "Login.asp"

End if

<HTML>

<HEAD>

<TITLE>Wrox Classifieds</TITLE>

</HEAD>

<H1>Wrox Classifieds<BR>Selling Items</H1>

<H3>Welcome <%= Session("GivenName") %></H3>

</CENTER>

<P>You currently have the following items for sale:</P>

Dim rsItems

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

rsItems.Filter = "SellerID = " & Session("PersonID")

rsItems.Open "Item", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

If Not rsItems.EOF Then ' current user has items for sale

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

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

"</TR>"

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

<HR>

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

<TD WIDTH=33%><A href="Item.asp?Action=AddNew">Add Sale Items</A></TD>

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

</TR>

</TABLE>

</BODY>

</HTML>

2. Save the file as ViewMySaleItems.asp, in the folder that contains your other.asp files.

3. From the registered users' home page (MenuForRegisteredUsers.asp), click on the List/Edit Sale Items hyperlink to view the 'Items for Sale' page. It looks fairly dull at the moment because we haven't added any items to the sale listings, but it will look a bit livelier later in the chapter.

Chapter15_image020

How It Works

ViewMySaleItems.asp is used to display the items that the logged-in user currently has for sale. In order to retrieve this information, we query the database's Item table for all records where the SellerID field is the same as the PersonID of the current user. Then we display this information in a table for the user to see.

Since this page performs database queries, we include the Clssfd.asp file (which, as we've already seen, contains the necessary code for connecting to the database). Since this should only be visible to registered users (who are also logged in), we check whether the user is logged in – and if not, we redirect them to the login page:

If Session("PersonID") = "" Then

Response.Redirect "Login.asp"

End If

We use a recordset to query the database for the list of items for sale; we populate the recordset with the entire contents of the Item table. However, we'll only need the records pertaining to the current user, so we'll apply a filter. The filter hides any record that doesn't match the criterion of the filter. In this case, we tell the recordset to hide those records whose SellerID value doesn't match the PersonID of the current user (this value is stored in a session-level variable). Then we use the EOF property to see if there are any records that match the filter:

Dim rsItems

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

rsItems.Filter = "SellerID = " & Session("PersonID")

rsItems.Open "Item", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

If Not rsItems.EOF Then ' current user has items for sale

If the recordset contains any records that match our filter, we display some of the data contained in those records in a table. The Do ... While statement loops through each of the rows in the recordset, and creates a new table row for each. When all the information for one record is formatted within a table row, we use the recordset's MoveNext method to move on to the next record. When we've gone through all the records, we can finish the table and close the recordset:

If Not rsItems.EOF Then ' current user has items for sale

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

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

"</TR>"

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

Note that the first column, which contains the ItemID, is a hyperlink: when clicked, it takes the user to a page called Item.asp, which displays details specific to that item (and allows the user to edit these details). Note that we use the FormatCurrency and FormatDateTime functions to format the values of the AskingPrice and ListingDate fields (FormatDateTime presents the date in its short format). Here's the code for all that:

We present a set of navigation controls across the bottom of the page. There's a new control on this page: it allows the user to add a new sale item:

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

<TD WIDTH=33%><A href="Item.asp?Action=AddNew">Add Sale Items</A></TD>

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

</TR>

</TABLE>

Adding and Editing Sale Items

Now, we'll build AddItem.asp and Item.asp. These pages will allow the logged-in user to add a new item to the sale listings, and edit the details of his existing sale items.

Try It Out – Adding and Editing Items

1. Create a new ASP file, and add in the following code:

<HTML>

<HEAD>

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

</HEAD>

Dim blnNew

' detect whether the user is adding a new item or editing an existing one

Select Case Request.QueryString("Action")

Case "AddNew"

blnNew = True

Case "Edit"

blnNew = False

End Select

If blnNew Then

Response.Write _

"<CENTER><H1>Wrox Classifieds<BR>Add New Sale Item</H1></CENTER>" & _

"<P>Please add the following information for the item you have for sale. "

Else

Response.Write _

"<CENTER><H1>Wrox Classifieds<BR>Edit Sale Item</H1></CENTER>" & _

"<P>Please edit the information for this item currently for sale. "

Dim rsItem

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

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

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

End If

%>
<FORM ACTION="addItem.asp" METHOD="POST">

If blnNew Then %>

<INPUT TYPE="Hidden" NAME="ItemID" VALUE=""> <%

Else %>

<INPUT TYPE="Hidden" NAME="ItemID" VALUE="<%= Request("Item") %>"> <%

End If

<TR>

<TD><INPUT TYPE="Text" NAME="ItemName"

VALUE="<% If Not blnNew Then Response.Write rsItem("ItemName") End If%>"

SIZE="40" MAXLENGTH="75"></TD>

</TR>

<TR>

<TD>Description:</TD>

<% If Not blnNew Then Response.Write rsItem("Description") End If %>

</TEXTAREA></TD>

</TR>

<TR>

<TD>Asking Price:</TD>

<TD><INPUT TYPE="Text" NAME="AskingPrice"

VALUE="<% If Not blnNew Then Response.Write rsItem("AskingPrice") End If%>"

SIZE="40"></TD>

</TR>

<TR>

<TD>Notify Price:</TD>

<TD><INPUT TYPE="Text" NAME="NotifyPrice"

VALUE="<% If Not blnNew Then Response.Write rsItem("NotifyPrice") End If%>"

SIZE="40"></TD>

</TR>

<TR>

<TD>Sale Expiration Date:</TD>

<TD><INPUT TYPE="Text" NAME="ExpirationDate"

VALUE="<% If Not blnNew Then

Response.Write FormatDateTime(rsItem("ExpirationDate"),2)

End If %>"

SIZE="40"></TD>

</TR>

<TR>

<TD ALIGN=CENTER COLSPAN=2><BR><%

If Not blnNew Then %>

<INPUT TYPE="Submit" NAME="Delete" VALUE="Delete Item"><%

End If %>

<INPUT TYPE="Submit"

VALUE="<% If blnNew Then %>Add New Item<% Else %>Update Item<% End If %>">

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

</HTML>

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

3. Create another new file and enter the following code:

Dim rsItem

Dim blnNew

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

rsItem.Open "Item", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

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

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

If Not rsItem.EOF Then rsItem.Delete

Else ' user wishes to edit/add an item

If Request("ItemID") = "" Then

blnNew = True

rsItem.AddNew

Else

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

blnNew = False

End If

rsItem("ItemName") = Request.Form("ItemName")

rsItem("Description") = Request.Form("Description")

rsItem("AskingPrice") = Request.Form("AskingPrice")

rsItem("NotifyPrice") = Request.Form("NotifyPrice")

rsItem("SellerID") = Session("PersonID")

rsItem("ItemStatus") = "Active"

If blnNew = True Then

rsItem("ListingDate") = Now

End If

If Left(Request.Form("ExpirationDate"),1) = "+" Then

Dim datDelta

datDelta = _

Mid(Request.Form("ExpirationDate"),2,len(Request.Form("ExpirationDate")))

rsItem("ExpirationDate") = DateAdd("d", datDelta, Now)

Else

rsItem("ExpirationDate") = Request.Form("ExpirationDate")

End If

rsItem.Update

If blnNew Then

Dim rsSeller

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

rsSeller.Open "Seller", objConn, _

adOpenForwardOnly, adLockOptimistic, adCmdTable

rsSeller.Filter = "PersonID = " & Session("PersonID")

If rsSeller.EOF Then

rsSeller.AddNew

rsSeller("PersonID") = Session("PersonID")

rsSeller("ItemsListed") = 0

rsSeller("ItemsActive") = 0

End If

rsSeller("ItemsListed") = rsSeller("ItemsListed") + 1

rsSeller("ItemsActive") = rsSeller("ItemsActive") + 1

rsSeller("LastActivity") = Now

rsSeller.Update

rsSeller.Close

Set rsSeller = Nothing

End If

rsItem.Close

Set rsItem = Nothing

Response.Redirect "ViewMySaleItems.asp"

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

5. Go back to your browser. From the 'Selling Items' page (ViewMySaleItems.asp), click on the Add Sale Items button, and insert sale details for some item that you wish to sell:

Chapter15_image021

In the last field, be sure to enter the sale expiration date – not the current date!

6. Now click on the Add New Item button. This will return you to the 'Selling Items' page (ViewMySaleItems.asp). We promised that this page would become more exciting: it now sumarizes the items that the user has for sale:

Chapter15_image022

7. Click on the ItemID of an item that you wish to edit (there's only one here at the moment) – this takes you to the URL Item.asp?Action=Edit. Alternatively, you can click on the Add Sale Items hyperlink at the bottom to add more new entries, via the URL Item.asp?Action=AddNew.

How It Works

Item.asp is dual-purpose: it allows a user to add new sale items to the listings, and to edit the details of his existing sale items. In this application, every link to Item.asp also provides a querystring called Action, with a value of Edit or AddNew – this value will determine the function that the page will need to perform:

If the user arrived at this page via Item.asp?Action=AddNew, then the page is being used to add a new sale item
If the user arrived at this page via Item.asp?Action=Edit, then the user intends to edit the details of an existing sale item

We use a Select Case statement (at the top of Item.asp) to evaluate the value of the Action querystring, and assign a value to a Boolean flag called blnNew accordingly:

Dim blnNew

' detect whether the user is adding a new item or editing an existing one

Select Case Request.QueryString("Action")

Case "AddNew"

blnNew = True ' user is adding a new item

Case "Edit"

blnNew = False ' user is editing an existing item

End Select

We use blnNew throughout the rest of this page to determine the content of the page dynamically. First, we'll use it to determine the precise wording of the header information. In addition, in the case blnNew=False it's also a good idea to retrieve the existing data on that item from the database, so that we can display them for the user's convenience. To do this, we create a recordset, and use If to query records from the Item table whose ItemID value is the same as that of the item we want to edit:

If blnNew Then

Response.Write _

"<CENTER><H1>Wrox Classifieds<BR>Add New Sale Item</H1></CENTER>" & _

"<P>Please add the following information for the item you have for sale. "

Else

Response.Write _

"<CENTER><H1>Wrox Classifieds<BR>Edit Sale Item</H1></CENTER>" & _

"<P>Please edit the information for this item currently for sale. "

Dim rsItem

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

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

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

End If

Now we can start to build the HTML form, which allows the user to modify the item's details. When the user submits the information, the data in the form will be processed by AddItem.asp. If the user is editing an existing item, then we'll need to pass the item's ItemID to AddItem.asp (which we do using a hidden FORM INPUT field). If the user is creating a new item, then the item's ID is still to be determined – so we pass a blank value to the AddItem.asp script.

If blnNew Then %>

<INPUT TYPE="Hidden" NAME="ItemID" VALUE=""> <%

Else %>

<INPUT TYPE="Hidden" NAME="ItemID" VALUE="<%= Request("Item") %>"> <%

End If

Note that we've coded the <INPUT> tag as straight HTML here, inside of the ASP page. This illustrates an important point, that we've seen elsewhere in the book: namely, that flow control statements like Select Case...End Select and If...Then can be used to control the flow of both ASP script and HTML to the page. The HTML code in the fragment above is only displayed when the user is adding a new item.

The remainder of the form is nicely formatted using an HTML table. In the case that an existing item's details are being edited (blnNew="False"), we need to display the existing item information in the appropriate fields. We do that within each field by checking the value of blnNew, and (if Not blnNew) we take the appropriate value from the recordset and display it in the form, using INPUT element's VALUE property. In the case of the Description field we've used a TEXTAREA element, so the existing data goes within the <TEXTAREA></TEXTAREA> tags:

<TR>

<TD><INPUT TYPE="Text" NAME="ItemName"

VALUE="<% If Not blnNew Then Response.Write rsItem("ItemName") End If%>"

SIZE="40" MAXLENGTH="75"></TD>

</TR>

<TR>

<TD>Description:</TD>

<% If Not blnNew Then Response.Write rsItem("Description") End If %>

</TEXTAREA></TD>

</TR>

...

When the form is complete, the user can select one of three buttons:

<TD ALIGN=CENTER COLSPAN=2><BR><%

If Not blnNew Then %>

<INPUT TYPE="Submit" NAME="Delete" VALUE="Delete Item"><%

End If %>

<INPUT TYPE="Submit"

VALUE="<% If blnNew Then %>Add New Item<% Else %>Update Item<% End If %>">

</TR>

</TABLE>

</FORM>

Here, the user can choose to:

Delete the item from the database. This is only relevant when the user is editing an existing item, so this button is only displayed when the value of blnNew is False. In addition to having a VALUE parameter (which controls the button caption), the Delete button also has a NAME parameter. In a moment, we'll see how the NAME parameter is used within AddItem.asp to determine which of the buttons was clicked by the user.
Submit the information on the form. Again, we use the value of blnNew to control the label on the Submit button dynamically.
Reset the information in the form and start over.

That's it for the Item.asp code. In AddItem.asp, we interact with the Item table via a recordset that is populated with the contents of the table. We set the locking parameter to adLockOptimistic – this will allow us to change the information in the table.

Dim rsItem

Dim blnNew

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

rsItem.Open "Item", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable

We break the logic in this page into two parts: deleting an item, and adding/editing an item. First, if the user wants to delete the selected item then they will have selected the Delete button on Item.asp. In this case, the contents of the button's NAME parameter are placed in the Delete element of the Request object's Form collection. Thus, we can check the value of Request.Form("Delete") – if it's not empty then we know the user wishes to delete this item:

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

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

If Not rsItem.EOF Then rsItem.Delete

To perform the deletion, we set the recordset's Filter property to search for the ItemID of the item in question. There should be exactly one record detected by the filter – just before we delete the record, we use EOF to ensure that there really is a record there to delete.

The second part to handle is that of adding/editing an item. Naturally, we break this into two subcases – adding and editing. We can determine which we're dealing with by checking the value supplied in the ItemID element of the Request object's QueryString collection:

Else ' user wishes to edit/add an item

If Request("ItemID") = "" Then

blnNew = True

rsItem.AddNew

Else

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

blnNew = False

End If

If the user is adding a new item, then we call the recordset's AddNew method to create a new record. If we are editing an existing item, then we set the Filter property to so that the recordset exposes only the item that we're interested in.

We've used a little programmer's shortcut here – by using the expression Request("ItemID") instead of Request.QueryString("ItemID"). We saw this originally in Chapter 7 – if we don't specify which of the Request object's collections we mean here, then the collections are searched in this order: QueryString, Form, Cookies, ClientCertificate, ServerVariables. If the same name appears in multiple collections, then the value returned will come from the first collection in which it is detected.

Whether we're adding an item or editing one, we're now in a position to add the details to the fields of the record:

rsItem("ItemName") = Request.Form("ItemName")

rsItem("Description") = Request.Form("Description")

rsItem("AskingPrice") = Request.Form("AskingPrice")

rsItem("NotifyPrice") = Request.Form("NotifyPrice")

rsItem("SellerID") = Session("PersonID")

rsItem("ItemStatus") = "Active"

If blnNew = True Then

rsItem("ListingDate") = Now

End If

If Left(Request.Form("ExpirationDate"),1) = "+" Then

Dim datDelta

datDelta = _

Mid(Request.Form("ExpirationDate"),2,len(Request.Form("ExpirationDate")))

rsItem("ExpirationDate") = DateAdd("d", datDelta, Now)

Else

rsItem("ExpirationDate") = Request.Form("ExpirationDate")

End If

rsItem.Update

In the case of editing an existing record, we only need to update those fields that the user was able to edit. Therefore, we only populate the ListingDate field if we're adding a new record (and we use the value of blnNew again to detect that dynamically).

We'll allow our user interface to support a handy feature here: namely, the ability to enter the sale expiration date as a number of days. Thus, if the user wants to enter a sale period of three weeks, then they simply enter +21 – the system will figure out what the expiration date should be. We use the DateAdd method to add the number of days specified to the current date.

Once all of the changes have been made, the Update method of the recordset is called and the changes are written to the database.

If we're adding a new item for sale, then we also need to update the Seller table – which contains statistical information about an individual seller. To update this information, we create a new recordset, which we populate with the contents of the Seller table. We apply a Filter so that the recordset exposes only the record for the current user. We can use the EOF property to discover whether the recordset is exposing any records. If EOF is True, then the recordset is exposing no records. This means that we need to add a new record to the Seller table for this user. To do this, we call the AddNew method, and then update the information in the record. If we're adding a record to the Seller table, then we initialize the ItemsListed and ItemsActive fields to 0:

If blnNew Then

Dim rsSeller

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

rsSeller.Open "Seller", objConn, _

adOpenForwardOnly, adLockOptimistic, adCmdTable

rsSeller.Filter = "PersonID = " & Session("PersonID")

If rsSeller.EOF Then

rsSeller.AddNew

rsSeller("PersonID") = Session("PersonID")

rsSeller("ItemsListed") = 0

rsSeller("ItemsActive") = 0

End If

rsSeller("ItemsListed") = rsSeller("ItemsListed") + 1

rsSeller("ItemsActive") = rsSeller("ItemsActive") + 1

rsSeller("LastActivity") = Now

rsSeller.Update

rsSeller.Close

Set rsSeller = Nothing

End If

Since the user has just added a new item for sale, we add 1 to the values of the ItemsListed and ItemsActive fields. (This is the reason for initializing these values in the case of adding a new record: if the field contains a non-numeric value, and we try to add 1, an error would result.) Finally, we set the LastActivity field to today, then update and close the recordset.

Once all of the information has been updated in the database, we can close and release the rsItem recordset, and redirect the browser back to ViewMySaleItems.asp:

rsItem.Close

Set rsItem = Nothing

Response.Redirect "ViewMySaleItems.asp"

Of course, ViewMySaleItems.asp is generated dynamically – so it will immediately display the new or updated item information in its table.

<< 15.3.2- User Registration and Login

Chapter15

15.3.4- Browsing and Bidding >>