Published: Wednesday, February 07, 2001
Creating an HTML Table with Orderable Columns, Part 2
By Akhilesh and
Scott Mitchell
Read Part 1
In Part 1 we looked at how to allow the user to
sort database table information by clicking on a hyperlink in an HTML table. The script
assumed that all of the data would be dumped onto the screen in one shot, though. What if
you wanted to page information, like in the Sample Book Chapters
on 4Guys, and still allow the user to customize the ordering? We'll look at how to
accomplish this in this part!
The Sample Book Chapters section pages the various on-line
chapter samples (which are stored in a database table: tblBooks) eight records at
a time. It uses a modified version of the paging stored procedure presented in:
Paging Through Records Using a Stored Procedure. The
stored procedure is modified slightly, however, to allow for a third parameter to specify
the column to sort by.
Before we examine the stored procedure code, let's look at the code for our single
Sample Book Chapters ASP page. The following code
is not the complete source for the ASP page, but shows the relevant sections:
<!-- List the hyperlinks to alter the sort order... -->
<A HREF="/webtech/chapters/index.shtml?sort=1">Sort by Published Date</A>
<A HREF="/webtech/chapters/index.shtml?sort=2">Sort by Published Title</A>
<A HREF="/webtech/chapters/index.shtml?sort=3">Sort by Publisher</A>
<P>
<%
'Establish a connection to the database
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=DSNName"
Const iRecsPerPage = 8
'Get the page number and the column to sort by
Dim iSort, strSort, iPage
iSort = Request.QueryString("sort")
iPage = Request.QueryString("page")
'If these variables aren't passed through the QueryString assume
'we're on the first page and that we want to sort by PublishedDate
If Len(iSort) = 0 or Not IsNumeric(iSort) then iSort = 1
If Len(iPage) = 0 or Not IsNumeric(iPage) then iPage = 1
Select Case iSort
case 1:
strSort = "PublishedDate DESC"
case 2:
strSort = "Title, PublishedDate DESC"
case 3:
strSort = "P.Name, PublishedDate DESC"
End Select
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
'Call the sp_GetBooks sp
objRS.Open "sp_GetBooks " & iPage & "," & _
iRecsPerPage & ",'" & strSort & "'", objConn
'Do we have more records to display (another page?)
Dim iMore
iMore = objRS("MoreRecords")
Do While Not objRS.EOF %>
<FONT SIZE=+1><B><%=objRS("Title")%></B></FONT>
<FONT SIZE=2>(<I>Published by
<A HREF="<%=objRS("PublishersURL")%>">
<%=objRS("Publisher")%>
</A> on
<%=FormatDateTime(objRS("PublishedDate"), 2)%></I>)<BR>
<%=objRS("Description")%><BR>
[<A HREF="<%=objRS("SampleChapterURL")%>">
Read the Sample Chapter</A>] |
[<A HREF="<%=objRS("PurchaseURL")%>">Buy this Book!</A>] |
[<A HREF="<%=objRS("PublishersURL")%>">Visit
<%=objRS("Publisher")%>'s HomePage</A>]
</FONT>
<P>
<%
objRS.MoveNext
Loop
'Do we need to display next/prev?
If iPage > 1 then %>
[<A HREF="/webtech/chapters/index.shtml?page=<%=iPage-1%>&sort=<%=iSort%>">
Previous <%=iRecsPerPage%> Titles
</A>]
<% End If
If CInt(iMore) > 0 then
if iMore > iRecsPerPage then iMore = iRecsPerPage %>
[<A HREF="/webtech/chapters/index.shtml?page=<%=iPage+1%>&sort=<%=iSort%>">
Next <%=iMore%> Titles
</A>]
<% End If %>
|
Note that when a user clicks on one of the hyperlinks to change the sort order the
ASP page is reloaded and passed a value for sort through the QueryString (i.e.,
for sorting by the book's title, the user is sent to: /webtech/chapters/index.shtml?sort=2.
Also note how the stored procedure is called. It is passed, as the first parameter, the
"page" we are currently viewing; the second parameter specifies how many records per page we
wish to display; the last parameter is the name of the column we wish to sort by. The value
we pass to the stored procedure is based upon the value of sort specified in the
QueryString. (To learn more about this technique of running dynamic SQL statements in a
stored procedure be sure to read the 4Guys article:
Using Dynamic SQL Statements in Stored Procedures.) Since as the user pages through the results, we need to keep the sort order,
this value is continually passed through in the Next/Prev links.
The code for the paging stored procedure, sp_GetBooks, can be seen below. For
an explanation of how the paging works be sure to read the article
Paging Through Records Using a Stored Procedure.
The below stored procedure, however, has one slight enhancement over the more generic one in
Paging Through Records Using a Stored Procedure. Rather
than selecting all of the columns into our temporary table, #TempItems,
we INSERT only the IDs of the rows from the tblBooks table. Then, when selecting
the subset of rows from this temp. table that makeup the current "page" of data, we use an
INNER JOIN back into the tblBooks table to get all of the needed columns from
the tblBooks table.
Create Procedure sp_GetBooks
(
@Page int,
@RecsPerPage int,
@SortSQL varchar(100)
)
As
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
BookAutoID int IDENTITY,
BookID int
)
-- Insert the rows from tblItems into the temp. table
DECLARE @SearchSQL varchar(5000)
SELECT @SearchSQL = 'INSERT INTO #TempItems (BookID) SELECT BookID ' +
'FROM tblBooks B INNER JOIN tblPublishers P ON ' +
'P.PublisherID = B.PublisherID ORDER BY ' + @SortSQL
EXECUTE(@SearchSQL)
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT B.Title, B.PublishedDate, B.PurchaseURL, B.SampleChapterURL,
Publisher = P.Name, PublishersURL = P.URL,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.BookAutoID >= @LastRec
) , B.Description
FROM #TempItems T (nolock)
INNER JOIN tblBooks B (nolock) ON
B.BookID = T.BookID
INNER JOIN tblPublishers P (nolock) ON
P.PublisherID = B.PublisherID
WHERE BookAutoID > @FirstRec AND BookAutoID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
|
When the user selects to reorder the data in this paged solution, she is taken to
the first page of data for the newly ordered results. This occurs because the links to
reorder the data do not pass a page value through the QueryString, and a non-existent
page value defaults to 1, thereby showing the first page of data.
There you have it! In this article we looked at how to allow your users to sort database
table information for both paged and non-paged HTML table results.
Happy Programming!
By Akhilesh and
Scott Mitchell
Supplemental Articles
How can I display Recordset data in an HTML TABLE?
Sample Book Chapters Section
Paging through Records using a Stored Procedure
Scott Mitchell
