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!
