![]() |
|
|
Sorting Database RecordsOrdering the recordset returned from your Access database is very easy - simply specify the columns you wish to order using an ORDER BY clause. When using the ORDER BY clause, ordering will happen by default in Ascending order (lowest to highest value). To order from highest to lowest value all you do is simply add the DESC keyword. Some examples of ordering using the ORDER BY clause can be seen below. Below is a table called 'Members_tbl' with 3 rows of data.
SELECT * FROM Members_tbl ORDER BY ID
You will notice that from the table above (Diagram 2), all the members have joined on the same day, the only difference is the time that they have joined. If we were to do a SELECT * FROM Members_tbl ORDER By DateJoined this would return all the data in Ascending order based on the DateJoined field. (Remember ASC ascending is the default). In this scenario 'Michael Wall' would be the first returned as he has joined just 1 minute and 2 seconds before 'Fred Humphries'. If we were to do a SELECT * FROM Members_tbl ORDER By DateJoined DESC then this would return 'David Goose' first. Below is sample code using the table structure in Diagram 2; <%@ Language="VBScript" %>
<% Option Explicit %> <html> <head> <title>Order By</title> </head> <body> <% 'declare your variables Dim connection, recordset, sSQL, sConnString 'assign SQL statement that will query the database to variable sSQL="SELECT * FROM Members_tbl ORDER By DateJoined DESC" 'create an ADO connection and recordset object Set connection = Server.CreateObject("ADODB.connection") Set recordset = Server.CreateObject("ADODB.recordset") 'define the connection string, specify database 'driver and the location of database sConnString="PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("Members.mdb") 'Open the connection to the database Connection.Open sConnString 'Open the recordset object executing the SQL Recordset.Open sSQL, connection 'While there are records loop through them Do while not Recordset.Eof response.write Recordset("FirstName") & "<br>" response.write Recordset("SurName") & "<br>" response.write Recordset("DateJoined") & "<br><br>" 'Move on to the next record Recordset.MoveNext Loop 'Now close the connection and recordset objects recordset.Close Set recordset = Nothing connection.Close Set connection = Nothing %> </body> </html>
Site developed by Michael Wall - Web Design Belfast N.Ireland. |
|