Start New Discussion within our Software Development Community

Sometimes sorting data can be easy and sometimes it can be difficult. If you have data in a listview you can spend a great deal of time writing ICompare functions. This is appropriate for non-trivial applications but can be a little intimidating for inexperienced programmers. Also, it is a problem where the format of the input data changes. Detached recordsets provide a method which allows ad hoc sorting of data. With a little planning, they can be used to provide sorting for data where the number of fields is not known until runtime. Once you get the "messy" stuff out of the way (defining the fields), the actual operation is relatively simple. Also, sorting of columns with multiple criteria is trivial. Another benefit is that sorting can be done without having to write custom sort code. The following code requires a form with three buttons (named btnByName, btnByDate and btnBySalary), and one ListView (named ListView1). ListView1 should be set to Details view and have three columns with the header text "Name", "Date", and "Salary".

Sorting of the data in the recordset is done by assigning a string to the Sort property. Sorts can be in ascending (ASC) or descending (DESC) order. A separate order can be given for each field. You give the sort string as follows

recordset.Sort = "fldname direction, fldname direction, etc"

You can specify one field or multiple fields. The default sort order is ASC so the following

recordset.Sort = "fldname, fldname DESC"

Sorts in ascending order on the first field, then in descending order on the second field.

Sample data in file defined by TESTDATA

Jim,1953-12-22,47000
George,1957-12-01,38000
Maureen,1956-06-19,72000
Harold,1983-02-28,42000
Fred,1974-09-28,38000

Comments
Good tutorial
'                                                                               
'  Name:                                                                        
'                                                                               
'    Detached                                                                   
'                                                                               
'  Description:                                                                 
'                                                                               
'    Sample code that shows how to use detached recordsets to do ad hoc sorts   
'    on multiple records of data not associated with a traditional database.    
'                                                                               
'  Audit:                                                                       
'                                                                               
'    2012-07-21  Reverend Jim  Original code                                    
'                                                                               

Imports ADODB   'add reference to adodb under .NET tab of "Add Reference"       

Public Class Form1

    Const TESTDATA = "D:\temp\test.txt"

    Private mydata As ADODB.Recordset

    Private Sub Form1_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        mydata.Close()
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        'Create a recordset object. Cursors can be server side or client side.  
        'Because the recordset will not connect to a source (it is detached) we 
        'have to specify a client side cursor (and no active connection).       

        mydata = New ADODB.Recordset
        mydata.CursorLocation = CursorLocationEnum.adUseClient
        mydata.LockType = LockTypeEnum.adLockBatchOptimistic
        mydata.CursorType = CursorTypeEnum.adOpenStatic
        mydata.ActiveConnection = Nothing

        'Add one field for each field of data in the text file

        mydata.Fields.Append("Name", DataTypeEnum.adVarChar, 32)    '32 is max length
        mydata.Fields.Append("BirthDate", DataTypeEnum.adDate)
        mydata.Fields.Append("Salary", DataTypeEnum.adInteger)

        mydata.Open()

        For Each line As String In System.IO.File.ReadAllLines(TESTDATA)

            'Split the input line into fields at the comma separator. Note that 
            'this sample code does no error checking to ensure that the number  
            'of fields is correct and that the fields contain valid data.       

            Dim flds() As String = line.Split(",")

            'Add a new record and populate the fields

            mydata.AddNew()

            'mydata.Fields("Name").Value = flds(0)
            'mydata.Fields("BirthDate").Value = flds(1)
            'mydata.Fields("Salary").Value = flds(2)

            For i As Integer = 0 To UBound(flds)
                mydata(i).Value = flds(i)
            Next

            mydata.Update()

        Next

    End Sub

    Private Sub btnByName_Click(sender As System.Object, e As System.EventArgs) Handles btnByName.Click

        'Sort the records in ascending order by name

        mydata.Sort = "Name ASC"
        PopulateListView()

    End Sub

    Private Sub btnByDate_Click(sender As System.Object, e As System.EventArgs) Handles btnByDate.Click

        'Sort the records in descending order by birth date

        mydata.Sort = "BirthDate DESC"
        PopulateListView()

    End Sub

    Private Sub btnBySalary_Click(sender As System.Object, e As System.EventArgs) Handles btnBySalary.Click

        'Sort the data on two columns - first by salary in ascending order, then by
        'name in descending order.

        mydata.Sort = "Salary ASC, Name DESC"
        PopulateListView()

    End Sub

    Private Sub PopulateListView()

        'Clear out the existing listview and repopulate it with records from the
        'recordset.

        ListView1.Items.Clear()
        mydata.MoveFirst()

        Do Until mydata.EOF
            Dim item As New ListViewItem()
            item.Text = mydata("Name").Value
            item.SubItems.Add(mydata("BirthDate").Value)
            item.SubItems.Add(mydata("Salary").Value)
            ListView1.Items.Add(item)
            mydata.MoveNext()
        Loop

    End Sub

End Class

Computer languages in which I have developed applications

Assembler (DEC, Data General, 8080, GE, SEL, IBM 360)
WATFOR (Waterloo FORTRAN)
FORTRAN (SEL)
APL (IBM 360, IBM VSAPL)
PL/1
C/C++
Borland Paradox
VB.net
vbScript

In the above code, fields can be added by name (lines 60-62 commented out) or by zero-relative field number (loop lines 64-66)

The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.