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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, General


Question:

How can I show a table of records from a recordset and let my users choose which field (column) to sort on?


[Print this FAQ]

Answer: 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 junco@premier1.net.

<%
' 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
' self-adaptive...
'
%>

<HTML>
<HEAD>
<TITLE>Demo of Generic Server-Side Table Sorting</TITLE>
</HEAD>

<BODY bgColor="lightgrey">
<%
' 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 = ""
    Else
        ' 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
    End If
End If

' 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")
Conn.Open ConnectionString

' and we get our recordset!
Set RS = Conn.Execute( SQL )
If RS.EOF Then
    Response.Write "Sorry, No data from query [" & SQL & "]"
    Response.End
End If

' 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 %>">
</FORM>


<SCRIPT Language="JavaScript">
function goSort( fldName )
{
    theForm = document.HiddenForm;
    theForm.SortBy.value = fldName;
    theForm.submit( );
}
</SCRIPT>

<CENTER>
<H3>Demo of table <% = (TableName & sortMsg) %></H3>
<P>
<FONT Size='-1'>
Click on a column title to sort by that column; click again to reverse the sort.
</FONT>
<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>" _        
         & "<A HREF=""javascript:goSort('" & Server.URLEncode(fname) & "');"">" _
         & fname _
         & "</A></TH>" & vbNewLine
Next
row = row & "</TR>" & vbNewline
Response.Write row

' 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
    Next
    row = row & "</TR>" & vbNewLine
    Response.Write row
    RS.MoveNext
Loop
' and that's it!
%>

</TABLE>
</BODY>
</HTML>


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.

To see how I took that basic generic page and converted it into a page designed for a specific database, you can look at the pages that I "borrow" from my son's web site. There are links on that start page to two very different ways of sorting on a user-specified column. The first is, indeed, an adaptation of the generic code, shown above. The second shows how to do the same thing using client-side JavaScript! That is, all the sorting work is done in the browser! This removes the load of repeated database queries from your ASP server, a big performance advantage! However, it is significantly more complicated to code and understand, so tackle it only after you understand and have used the ASP-only approach.

Good luck!

(For a 4Guys article on sorting HTML table columns check out: Creating an HTML Table with Orderable Columns.)


FAQ posted by Bill Wilkinson at 3/4/2001 1:03:44 AM to the Databases, General category. This FAQ has been viewed 66,963 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


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