||The short answer is: By using an |
ORDER BY in your SQL
SELECT statement. That is, if you want the user to be able to sort by last name, you need to add
ORDER BY LastName to your SQL request that obtains the recordset.
The longer answer is: So how do you know which
ORDER BY clause to use? How do you let the user specify a particular field or column?
There are many ways. The most obvious would be to simply have multiple
SUBMIT buttons, each of which has a value that tells which field to sort on. If you give them all the same name, then only the one the user pushes will be "visible" on the next page. That is, if you put this in the
<FORM> on the page:
<INPUT Type=Submit Name="SortBy" Value="LastName">
<INPUT Type=Submit Name="SortBy" Value="LastPostingDate">
<INPUT Type=Submit Name="SortBy" Value="EMail">
then on the next page (or the same page, if the page submits to itself), you can do
SQL = "SELECT * FROM table ORDER BY " & Request("SortBy")
Really simple! Easy to code, easy for the user to understand.
But putting a button at the head of a table column doesn't look all that attractive, so you might prefer to use a hyperlink, instead. Perhaps something like this:
<A HREF="ShowOrderedTable.asp?SortBy=LastName">Last Name</A>
<A HREF="ShowOrderedTable.asp?SortBy=LastPostingDate">Last Date This User Posted</A>
<A HREF="ShowOrderedTable.asp?SortBy=EMail">User's EMail Address</A>
And the ASP code remains unchanged! If this is all you need, then you are ready to go code it!
But what about these considerations:
(1) The first time the user hits the page (especially if it is a page that links to itself to re-display in the user-requested order), no ordering will be specified. You need to either supply a default ordering or leave off the
ORDER BY altogether.
(2) How can you allow the user to request that a given column be sorted in reverse order? You could use separate buttons or links for forward and reverse, but I kind of like the way Outlook and Outlook Express do it. In those programs (and many others), you click on the name of the column once to sort in forward order and then click again to sort in reverse order.
Well, to support both those features, we need to add a bit more code, obviously. To demonstrate one way to do this, there follows a complete ASP page that will allow you to display any table from any database and request a sort on any column! All you need to do to make it work on your system is supply a
ConnectionString and a
TableName in the points indicated at the beginning of the code. Everything else is done for you. Please copy/paste this code to your own machine and try it out! If there are any bugs in it, feel free to email me at
' To adapt this code for your own database,
' you need to change the ConnectionString and
' TableName values, as given here:
ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);" _
& "DBQ=" & Server.MapPath("ClientDemo.mdb")
TableName = "Employees"
' Other than those two strings, the code is all
<TITLE>Demo of Generic Server-Side Table Sorting</TITLE>
' This page demonstrates one way to sort a page by a column
' selected by the user.
' See below for how we create hyperlinks that, in turn, invoke
' a form submission that provides us with a "SortBy" argument.
' (On the initial invocation of the page, no "SortBy" is given,
' so we provide a default, of course!)
ordering = "" & Request("SortBy") ' the & ensures this is a string!
priorOrder = "" & Request("PriorSortBy") ' what did we sort by last time?
sortMsg = "" ' by default, no sort specified.
If ordering <> "" Then
If ordering = priorOrder Then
' This is just for the header message:
sortMsg = " sorted <strong>in reverse</strong> by " & ordering
' if same field, then reverse the sort...
ordering = ordering & " DESC"
' and then say no prior sort, so that a third
' click on the column will go back to ascending!
priorOrder = ""
' set the priorOrder to the current one so that
' another click on the column will do a reverse sort
priorOrder = ordering
' and this is just for the header message:
sortMsg = " sorted by " & ordering
' Now connect to the DB and request the data, in the
' chosen ordering, if applicable.
' First, build the SQL statement:
SQL = "SELECT * FROM " & TableName
If ordering <> "" Then SQL = SQL & " ORDER BY " & ordering
' uncomment next two lines for debugging
' Response.Write "SQL is [" & SQL & "]<P>" & vbNewLine
' Response.Write "ConnectionString is [" & ConnectionString & "]<P>" & vbNewLine
'create and open the connection to the database
Set Conn = Server.CreateObject("ADODB.Connection")
' and we get our recordset!
Set RS = Conn.Execute( SQL )
If RS.EOF Then
Response.Write "Sorry, No data from query [" & SQL & "]"
' how many fields in the recordset?
MaxField = RS.Fields.Count - 1
' Now finally ready to create the HTML output...
' first the 100% hidden form, then the JS function to
' invoke that form, and finally the table display:
<FORM Name="HiddenForm" Method="Post">
<INPUT Type=Hidden Name="SortBy">
<INPUT Type=Hidden Name="PriorSortBy" Value="<% = priorOrder %>">
function goSort( fldName )
theForm = document.HiddenForm;
theForm.SortBy.value = fldName;
<H3>Demo of table <% = (TableName & sortMsg) %></H3>
Click on a column title to sort by that column; click again to reverse the sort.
<TABLE Width='100%' Border=1 CellPadding=3 bgColor=lightgreen>
' First, write out the column titles, turning each
' one into a hyper link that invokes that little JS function..
row = "<TR>" & vbNewLine
For fnum = 0 To MaxField
fname = RS.Fields(fnum).Name
row = row _
& " <TH>" _
& fname _
& "</A></TH>" & vbNewLine
row = row & "</TR>" & vbNewline
' Then, for each record, write out all the field values
Do Until RS.EOF
row = "<TR>" & vbNewLine
For fnum = 0 To MaxField
row = row _
& " <TD>" & RS(fnum) & "</TD>" & vbNewLine
row = row & "</TR>" & vbNewLine
' and that's it!
Finally, you may have noticed that the above code isn't terribly smart about displaying the data. If you have a field that should be shown as currency, it only shows as numbers, not even necessarily with a certain number of digits after the decimal point. Date/time fields will probably show in an ugly fashion, as well. Other fields may not display the way you would like them to. Well, what do you want for free? It is a generic and self-adapting program.
(For a 4Guys article on sorting HTML table columns check out: Creating an HTML Table with Orderable Columns.)