When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
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!

    - continued -

    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>
    '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")%>">
      </A> on
      <%=FormatDateTime(objRS("PublishedDate"), 2)%></I>)<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>]
    '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
    <% 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
    <% 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)
    -- We don't want to return the # of rows inserted
    -- into our temporary table, so turn 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
    -- 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

    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

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article