| 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.)
|
