Let Users Sort Displayed Records

It’s one thing to list your records in ascending or descending order on your web page, but you can also allow your users to sort records the way they want.  Let’s say you are displaying a list of users on your page and you want to display all of the first and last names.

To do this, create a database called MyData and create a table called tblUsers with these fields:
ID – autonumber
FirstName – text field
LastName – text field

Then, start adding some names to your database.  Next, create a page called /UserSort.asp and copy the below code into your page:

<!--#INCLUDE VIRTUAL="/includes/connection.asp" -->

<p>
First Name <a href="usersort.asp?OB=f_a">ASC</a> sort <a href="usersort.asp?OB=f_d">DESC</a><br>
Last Name <a href="usersort.asp?OBt=l_a">ASC</a> sort <a href="usersort.asp?OB=l_d">DESC</a>
</p>

<%
DIM strOrderBy, tmpOrderBy
strOrderBy = Request.QueryString("OB")
Select Case strOrderBy
Case "f_a"
tmpOrderBy = "ORDER BY FirstName ASC"
Case "fn_d"
tmpOrderBy = "ORDER BY FirstName DESC"
Case "l_a"
tmpOrderBy = "ORDER BY LastName ASC"
Case "l_d"
tmpOrderBy = "ORDER BY LastName DESC"
Case Else
tmpOrderBy = "ORDER BY FirstName DESC"
End Select

DIM mySQL, objRS
mySQL = "Select FirstName, LastName FROM tblUsers " & tmpOrderBy
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open mySQL, objConn

DO WHILE NOT objRS.EOF
%>

<%=objRS("FirstName")%> <%=objRS("LastName")%><br>

<%
objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

This will display all of the users in your database in descending order by first name by default.  Then, when the user clicks on either ASC or DESC next to First Name or Last Name, they will be able to change the sort order the way they want.  Enjoy!

< Back to ASP Scripts