To read the article online, visit http://www.4GuysFromRolla.com/webtech/020701-1.shtml

Creating an HTML Table with Orderable Columns

By Akhilesh and Scott Mitchell


One of the many challenges facing ASP developers designing data-driven Web sites is providing data from a database in a pleasant, easy-to-use format. What good is all that data if it cannot be conveyed to the user in an understandable and easy-to-read format? Commonly ASP developers use techniques to place data from a database table into an HTML table. This aids in the readability of the data by placing it in columns and rows in a spreadsheet-like view. (For more information on placing database information into an HTML table, be sure to read the FAQ: How can I display Recordset data in an HTML TABLE?)

While a simple HTML table view is nice, it doesn't give the user much flexibility on how the data is presented. For example, here on 4Guys there is a Sample Book Chapters section that lists brief summaries on ASP-related books that have free, on-line sample chapters somewhere on the Web. Note that on this page, the user can determine if she would like to view the sample chapter index sorted by publication date (the default), title, or publisher. If, for example, our user is interested in finding on-line chapters for books published by Wrox, this "dynamic sorting" customization allows her to quickly obtain a listing of those specific books.

In this article we will look at the code for the Sample Book Chapters section sorting, but, before we do, let's examine a generic implementation. The following code will create an HTML table with four columns, each of which contains a link in the column heading (in the TH tag) that, when clicked, will reload the page and redisplay the data sorted by the clicked column heading.

<%@ Language=VBScript %>
<HTML>
<BODY>
<%
'declare the variables
dim objCon
dim objRec
dim strSQL
dim strOrder 'this variable is INCASE you order

'even though there might not be a querystring value passed to this page
'there could be cases when it could. So I feel it would better to use a 
'variable than get the value from the request object if I need it. 
'To learn more on that you can read the ASP Coding tips:
'    http://www.4guysfromrolla.com/webtech/top10/int3.shtml
strOrder = Request.QueryString("Order")

'create the objects
set objCon = server.CreateObject("ADODB.Connection")
set objRec = server.CreateObject("ADODB.Recordset")

'open your connection
objCon.Open "DSN=DSNName"

'select the data from the table like how one noraly would.
'You can include the where clause or anything for a normal
'SQL query
strSQL = "Select data1, data2, cur, SomeText from TEST"

'if there is a request to order the rows. This is a parameter
'that is passed when the user wants to order the records
'based on a column. I have describled below how you pass 
'this parameter. 

'So you check for this parameter. If the parameter exists
'then the user is requesting you to order the page based on
'the parameter he passed.
if strOrder <> "" then
  strSQL = strSQL & " ORDER BY " & strOrder
end if

'open the recordset object
objRec.Open strSQL, objCon, 3


'check if the recordset object returned any records
if objRec.EOF then
  'if there are no records display a message and stop
  'processing the page
  Response.Write  "No records available"
  Response.End
end if

'start building the table
Response.Write "<Table width=100% align=center border=1>"
Response.Write "<tr>"

'here you create the links where you can order by the
'coloumn heading you click on. I pass the column name as 
'per the database as a parameter.
Response.Write "<td align=center><a href=""" & _
               "SortColumns.asp?Order=data1"">" & _
               "Heading 1</a></td>"
Response.Write "<td align=center><a href="""
               "SortColumns.asp?Order=data2"">" & _
               "Heading 2</a></td>"
Response.Write "<td align=center><a href=""" & _
               "SortColumns.asp?Order=cur"">" & _
               "Heading 3</a></td>"
Response.Write "<td align=center><a href=""" & _
               "SortColumns.asp?Order=SomeText"">" & _
               "Heading 4</a></td>"
Response.Write "</tr>"

'populate the date in the table normally. This data is not
'ordered the first time but will be ordered according to the
'column if user chose to order by something. Even then you
'do not need to play with the date since the query has already
'taken care of it
while not objRec.EOF 
  Response.Write "<tr>"
  Response.Write "<td>" & objRec("data1") & "</td>"
  Response.Write "<td>" & objRec("data2") & "</td>"
  Response.Write "<td>" & objRec("cur") & "</td>"
  Response.Write "<td>" & objRec("SomeText") & "</td>"
  Response.Write "</tr>"
  objRec.MoveNext
wend	
Response.Write "</table>"

'clean up
objRec.Close
set objRec = nothing

objCon.Close
set objCon = nothing
%>
</BODY>
</HTML>

While this example is great for ordering all of the data in a database table, what if we want to provide paging of the data and still let the user sort by a particular field or table column? Note that this is how the Sample Book Chapters section on 4Guys does this. In Part 2 we'll examine how to allow both paging and the user to choose the column to sort!

  • Read Part 2!


  • Article Information
    Article Title: Creating an HTML Table with Orderable Columns
    Article Author: Akhilesh
    Published Date: Wednesday, February 07, 2001
    Article URL: http://www.4GuysFromRolla.com/webtech/020701-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers