ADO Queries and Working with Recordsets - Sorting Records
(Page 3 of 5 )
It is possible to sort recordsets in ADO by using the SQL ORDER BY function. Here, we will sort our data by VictimName in ascending order:
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/chucknorrisvictims.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT VictimName FROM Victims ORDER BY VictimName"
rs.Open sql, conn
%>
<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
</tr>
<%loop
rs.close
conn.close
%>
</table>
</body>
</html>
In the above code we tell the program to select all of the data in the VictimName column and sort in ascending order by VictimName. The program then opens up the database connection, writes each field by looping through the values until it comes to the end of the file, and then closes the connection. The result would be:
VictimName |
Death |
Jackie Chan |
Ralph Macchio |
To sort by Descending, you would do the same thing, except you would add DESC to the end of the SQL Select statement:
<html>
<body>
<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/chucknorrisvictims.mdb"))
set rs = Server.CreateObject("ADODB.recordset")
sql="SELECT VictimName FROM Victims ORDER BY VictimName"
rs.Open sql, conn
%>
<table border="1" width="100%">
<tr>
<%for each x in rs.Fields
response.write("<th>" & x.name & "</th>")
next%>
</tr>
<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
rs.MoveNext%>
</tr>
<%loop
rs.close
conn.close
%>
</table>
</body>
</html>
Which of course would result in:
VictimName |
Ralph Macchio |
Jackie Chan |
Death |
We could also allow the user to select which column they would like to sort on as well:
<html>
<body>
<table border="1" width="100%">
<tr>
<th align="left">
<a href="sample.asp?sort=victimname">Victim Name</a>
</th>
<th align="left">
<a href="sample.asp?sort=victiminjury">Victim Injury</a>
</th>
</tr>
<%
if request.querystring("sort")<>"" then
sort=request.querystring("sort")
else
sort="victimname"
end if
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/chucknorrisvictims.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
sql="SELECT VictimName,VictimInjury FROM Victims ORDER BY " & sort
rs.Open sql,conn
do until rs.EOF
response.write("<tr>")
for each x in rs.Fields
response.write("<td>" & x.value & "</td>")
next
rs.MoveNext
response.write("</tr>")
loop
rs.close
conn.close
%>
</table>
</body>
</html>
The above code creates a table consisting of the VictimName and VictimInjury columns. Each column header has a link in it. If the user clicks on either link, it will sort the table by that column.
Next: How to Add a Record to Table >>
More BrainDump Articles
More By James Payne
