Using Detached Recordsets to Sort Data

Updated Reverend Jim 1 Tallied Votes 882 Views Share

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


Begginnerdev commented: Good tutorial +5
'  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
    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)


        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.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)



    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"

    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"

    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"

    End Sub

    Private Sub PopulateListView()

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


        Do Until mydata.EOF
            Dim item As New ListViewItem()
            item.Text = mydata("Name").Value

    End Sub

End Class
Reverend Jim 4,669 Hi, I'm Jim, one of DaniWeb's moderators. Moderator Featured Poster

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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.