A First Look
We have done enough to be able to browse to the web site. Currently that's as
far as we can go, but you can confirm that the dynamic creation of the list of
categories in the home page and in the select tag are working.
Producing a Dynamic Product List
Our next task is to dynamically create a page displaying goods
contained within a particular category of music as shown above. We also need
to limit how many items are displayed per page; if our stock contains more than
can be displayed, then we need a facility to go to the next page and back to
the previous one. What we don't want is the customer being presented with a page
containing a hundred plus long list. They'll get fed up of scrolling down long
before they actually get to the end. Also a long page will be slow in downloading.
First, let's create the ListStockByCategory stored procedure following the same
steps as we did for ListCategories. It takes two input parameters, CatId and
StartItemId. To prevent the length of a page becoming unmanageable for the user
we only return the first 16 rows with the first item having an ItemId greater
then @StartItemId. We match item details and artist details using an inner join
of the Stock table and the Artist table.
CREATE PROCEDURE [ListStockByCategory] (@CatId int, @StartItemId int = -1) AS SELECT TOP 16 S.ItemId, ArtistName, Title, Price, QtyInStock FROM (Stock S JOIN Artist A ON S.ArtistId = A.ArtistId) WHERE S.CatId = @CatId AND S.ItemId >= @StartItemId ORDER BY ItemId
After you have completed the code click OK and close the dialog
box. As before you'll need to give MMCustomer Execute permissions for this stored
procedure.
Next we create the page itself.
The number of items per page is determined by the lMaxPerPage variable we define
in the server-side global include file. Its value has been set to 15 but you
may wish to vary this, though if you do so you'll also need to alter the ListStockByCategory
stored procedure which returns 16 records.
The ListStockByCategory stored procedure returns the next 16 records starting
with the StartItemId we pass it. We display the first 15 of them and use the
ItemId of the 16th as the StartItemId for our next 15. If we have a full page
then next and previous links are included which reload this page, but with the
StartItemId passed in the link's URL determining whether it's the next or previous
15 items that are displayed. A StartItemId of -1 retrieves the very first page
of stock.
Its time to take a look at the code. Having included our ServerSideGlobalDef.inc
file, we set the page to expire as soon as it has been written to the client's
browser. The list of items we produce also includes whether they are in stock.
By stopping the page from being cached we can ensure the user sees up-to-date
stock availability information every time they browse the stock.
Next we retrieve the data passed in the URL by using the Request.QueryString
collection. We can then use this information to create a new recordset and populate
it using our ListStockByCategory stored procedure.
<!--#include file="ServerSideGlobalDef.inc"--> <% Response.Expires = -1; %> <HTML> <% // Retrive the data we included in the link var scatTitle = unescape(Request.QueryString("Description")); var lCatId = Request.QueryString("CatId"); var lstartItemId = Request.QueryString("StartItemId"); var sRowBGColor = "oldlace"; var savailable; var lRecordCount = 0; var loRS; loRS = Server.CreateObject("ADODB.Recordset"); loRS.Open("Exec ListStockByCategory " + lCatId + _ "," + lstartItemId, sdbConnString); %>
Next we create our table tag and start looping through the
rows in the recordset. The background color for each row is alternated so it's
obvious where a product's row starts and ends, even if it has wrapped on to a
second line. A variable contains the color value and is simply alternated between
ivory and oldlace HTML colors.
Having dealt with the row color we need to deal with the stock availability by
creating a string containing either "In Stock" or "Unavailable" which we will
display in a table cell and the surlData variable to give the href value of the
Add to Basket hyperlink. If stock is available then its value will point to the
additem page with the item details added to the end of the URL. If no stock is
available, or cookies are not enabled, then the value will be a JavaScript line
which alerts the user that they can't proceed because either no stock exists
or cookies are disabled.
<BODY>
<H1 ALIGN="CENTER">
<FONT FACE="Comic Sans MS" color="Navy"><%=scatTitle%></FONT>
</H1>
<DIV align="center">
<TABLE>
<%
while (!(loRS.Eof))
{
// Alternate the row's background colour
sRowBGColor = (sRowBGColor == "ivory"? "oldlace" : "ivory");
lRecordCount++;
// Create the Item availability string
if (loRS("QtyInStock") > 0)
{
savailable = "In Stock";
}
else
{
savailable = "<FONT color='#808080'>Unavailable</FONT>";
}
// Determine what ACTION clicking a product's add item link
// does. If stock we want to add the item, if no stock or if
// cookies disabled then inform user
if (savailable == "In Stock")
{
surlData = "AddToBasket.asp?ID" + escape(loRS("ItemId") + _
"&1&" + loRS("ArtistName") + "&" + loRS("Title") + _
"&" + loRS("Price") + "£&");
}
else
{
surlData = "javascript:{alert('Sorry this item is currently _
out of stock')}";
}
%>
The creation of the individual item row occurs next with cells populated with our recordset row's values. Note the Add to Basket hyperlink has an onClick event handler defined which returns the value of the cookiesEnabled variable in the parent window MusicMad.htm page. If it's false, indicating that cookies are disabled, any attempts to add to the basket will be cancelled.
<TR bgcolor="<%= sRowBGColor %>">
<TD WIDTH="125">
<FONT FACE="Comic Sans MS" SIZE="-1"><%=loRS("ArtistName")%></FONT>
</TD>
<TD WIDTH="250">
<FONT FACE="Comic Sans MS" SIZE="-1"><%=loRS("Title")%></FONT>
</TD>
<TD WIDTH="45">
<FONT FACE="Comic Sans MS" SIZE="-1">£<%=loRS("Price")%></FONT>
</TD>
<TD WIDTH="85">
<FONT FACE="Comic Sans MS" SIZE="-1"><%=savailable%></FONT>
</TD>
<TD WIDTH="90">
<FONT FACE="Comic Sans MS" SIZE="-1">
<A HREF="<%=surlData%>" onClick="return parent.cookiesEnabled" >
Add to Basket
</A>
</FONT>
</TD>
</TR>
Having moved to the next record, we check to see if we have
come to the last record. If so, we can add a previous and next page hyperlink
if appropriate. If the lstartItemId was -1 then this was the first page requested
so there is no need to put a previous page link.
If we have gone past the maximum per page allowed then we don't display the next
record, but, instead, display a next page link with a startItemId of that last
record (so we know that if the user clicks the link the page will contain at
least one record), then we break out of the while loop.
<%
loRS.MoveNext();
// Has the end of the page been reached
if (lRecordCount == lMaxPerPage || loRS.Eof)
{
%>
<TR>
<%
// If this is not the first page then add previous link
if (lstartItemId != -1)
{
%>
<TD COLSPAN=4 ALIGN="RIGHT">
<A HREF="javascript:history.back();">Previous Page</A>
</TD>
<%
}
// If last item then add link to next page
if (!loRS.Eof)
{
%>
<TD COLSPAN=5 ALIGN="RIGHT">
<A HREF="Browse.asp?CatId=<%=lCatId %>& _
Description=<%=escape(scatTitle)%>& _
StartItemId=<%=loRS("ItemId")%>">
Next Page
</A>
</TD>
<%
}
%>
</TR>
<%
break;
}
}
loRS = null;
%>
</TABLE>
</DIV>
</BODY>
</HTML>
Save this page with your other files as browse.asp.
Testing times
You should now be able to browse to the home page and view a list of the products
available.
Comments