Manipulating ADO Recordsets - Sorting the recordset (Page 2 of 5 )
Sorting is almost an unconscious activity in daily life. We try to put things in compartments. In ADO you often sort as well. A familiar sorted list is the names of the states that you often come across when entering your address information on the Internet. If it is not sorted, you are at a loss to choose yours easily. On the other hand if it is alphabetically sorted, you just scroll down until you find yours.
In a recordset you may want to sort on any column name, or a set of column names in a chosen order. For example you may want to sort by "first name" and then you may want sort by the "last name" in the list that was sorted. When you call this property, a temporary index is created for each column specified, and the list is ordered efficiently. If the field is already indexed, it is that much more efficient since the engine does not have to create one. However for this to work you need to open the recordset after setting the CursorLocation to adUseClient. This is because Microsoft Jet 4.0 OleDB provider does not support the IViewSort Interface needed by ADO to do the sorting.
Also, if you want to revert to the original unsorted list, you may provide an empty string for the Sort property as you will see in the example. Of course it goes without saying that you should avoid using the keywords for field names. The syntax for this property usage is:
RecordSet.Sort "fieldName1 asc (or desc), fieldName2 asc
(or desc),.."In order to illustrate the "Sort" property, create a new form in design and add a couple of controls as shown. Some cosmetic changes have been made, and the RowSourceType of the list has been set to "ValueList" as we will be assigning the return value string to this property. If this is left as the default, Table/Query, then you will not see any display because the string is neither a table nor a query.
Basically a recordset is retrieved from the database using the code that we previously discussed in other tutorials. Each of the command buttons (Unsorted, Sorted: Last Name Ascending, Sorted: LastName Ascending and Hire Date descending) can sort the list (or revert to the original in one case) using the code in their click events as shown in the snippet that follows the picture.
Option Compare Database
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command0_Click()
'don't sort the recordset
rst.Sort = ""
Call poplist
End Sub
Private Sub Command1_Click()
'Sort the recordset with Last Name ascending
rst.Sort = "LastName asc"
Call poplist
End Sub
Private Sub Command2_Click()
'sort with Last Name ascending and Hire Date descending
rst.Sort = "LastName asc, HireDate desc"
Call poplist
End Sub
Private Sub Form_Load()
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJay _
My DocumentsRetrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
strSql = "Select LastName, FirstName, City,
HireDate from Employees"
rst.Open strSql, cn, adOpenStatic
MsgBox (rst.Fields.Count)
Set Me.Recordset = rst
End Sub
Sub poplist()
Dim strg
strg = ""
While Not rst.EOF
strg = strg & rst.Fields.Item(0).Value & ":" & _
rst.Fields.Item(1).Value & ":" & _
rst.Fields.Item(2).Value & ":" & _
"rst.Fields.Item(3).Value & ";"
rst.MoveNext
Wend
Me.List11.RowSource = strg
End Sub
The recordset opens with the Form's Load() event. Observe that the recordset is opened after setting the CursorLocation property to adUseClient. Some of the message boxes in this and other snippets are left in place, so that you can see what is happening. Command0's click will revert the record set to its original state. Clicking Command1 will sort the list so that the LastName will be in ascending order (it is actually the default). Command2 button's click will not only sort by LastName, it will also sort by HireDate (see Buchanan's information). The next picture shows what it looks like when the list is sorted by LastName in ascending order. Each of the click events also call the procedure poplist(), which populates the ListBox.
This next picture shows sorting by LastName ascending and HireDate descending.
Next: Filtering the Recordset >>
More Database Articles
More By Jayaram Krishnaswamy
