ADO - ADO Sort

1. What is ADO Sort?

The Sort property of an ADO Recordset allows you to sort rows after you’ve retrieved data from the database, without running another SQL query.
It rearranges the recordset in ascending or descending order based on one or more fields.

2. Syntax

Recordset.Sort = "FieldName [ASC|DESC]"

  • FieldName → The column you want to sort by.

  • ASC → Sorts in ascending order (default).

  • DESC → Sorts in descending order.

3. Important Points

  • Sort works only on client-side cursors → You must set the CursorLocation to adUseClient.

  • Sorting affects only the recordset, not the database table.

  • Multiple fields can be sorted at once.

4. Example: Sorting a Single Field (VB)

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open connection
conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

' Set cursor location to client for sorting
rs.CursorLocation = 3   ' adUseClient

' Open recordset
rs.Open "SELECT FirstName, LastName, Salary FROM Employees", conn, 1, 3

' Sort by salary descending
rs.Sort = "Salary DESC"

' Display sorted results
Do While Not rs.EOF
    Debug.Print rs("FirstName") & " " & rs("LastName") & " - " & rs("Salary")
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

5. Example: Sorting Multiple Fields

rs.Sort = "Department ASC, Salary DESC"

  • Sorts Department alphabetically (ascending).

  • Within each department, sorts Salary in descending order.

6. Example: ADO Sort in ASP Classic

<%
Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.Open "Provider=SQLOLEDB;Data Source=MYPC\SQLEXPRESS;Initial Catalog=EmployeeDB;User ID=sa;Password=1234;"

rs.CursorLocation = 3 ' adUseClient
rs.Open "SELECT FirstName, LastName, Salary FROM Employees", conn, 1, 3

' Sort by LastName ascending
rs.Sort = "LastName ASC"

Do While Not rs.EOF
    Response.Write rs("FirstName") & " " & rs("LastName") & " - " & rs("Salary") & "<br>"
    rs.MoveNext
Loop

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>

7. Sorting vs. SQL ORDER BY

Feature ADO Recordset Sort SQL ORDER BY
Where sorting happens In memory, after fetching data At the database level
Performance Slower for large data Faster and more efficient
Database load Fetches all rows first Returns only sorted rows
Recommended for Small datasets Large datasets

Best Practice:

  • If possible, use SQL's ORDER BY for efficiency.

  • Use Recordset.Sort only when you want dynamic sorting after data is already fetched.

8. Example: Combining SQL ORDER BY and ADO Sort

' Get data ordered by Department
rs.Open "SELECT FirstName, LastName, Salary, Department FROM Employees ORDER BY Department", conn, 1, 3

' Then sort locally by Salary
rs.Sort = "Salary DESC"