Thursday, March 20, 2008

Q: How do I sort data in an Array? (Part 2)

In my previous post, I walked you through a standard bubble sort method of sorting data in an array.  This method works and works fairly well but as I said earlier, there is another way to sort data using disconnected recordsets.

Disk on naked record sex?  What?  Disconnected recordsets are like ghostly databases.  Think of it as a database that lives solely in memory.  Once your program terminates (or the connection terminates), the information vanishes faster than plot lines on the new Knight Rider tv show.

Just like bubble sorting, a disconnected recordset can sort data in ascending and descending order.  But you may ask, why would I chose this method over good ol' reliable bubble sort?  Well, disconnected recordsets offer more than just sorting capability.  Remember, these are mini-databases so we can add and remove data on the fly (which is MUCH easier than dealing with resizing arrays). There are lots of other good reasons for using disconnected recordsets but I won't go into those now because we're talking about sorting arrays right?

 

Right.  So why are you holding back code like some trampy schoolgirl playing hard to get?  Fine, you wanna get right to it?  Here ya go:

 

Function DataListSort(arrData,strSort)
'Input: arrData = Array of data items, strings or integers
'Input: strSort = Sort method.  
'        Options: ASC (ascending) or DESC (descending)
'Output: Sorted Array
'Notes: This is currently only storing the first 10 characters.
'        Change MaxCharacters to increase this limit to allow more precise sorting.
'        This will also increase memory usage.
    
    DataListSort = ""
    strList = ""
    strSort = Trim(UCase(strSort))
    If Not strSort = "ASC" And Not strSort = "DESC" Then
        strSort = "ASC"
    End If 
    
    Const adVarChar = 200       ' Set the data type to variant.
    Const MaxCharacters = 10    ' Set the max num of characters to store
    
    'Setup Data object and connection
    Set DataList = CreateObject("ADOR.Recordset")
    DataList.Fields.Append "Items", adVarChar, MaxCharacters
    DataList.Open
    
    'Iterate through to add the array items to the record set
    For Each item In arrData
      DataList.AddNew
      DataList("Items") = item
      DataList.Update
    Next
    
    'Sort the record set
    DataList.Sort = "Items" & " " & strSort
    DataList.MoveFirst
    
    DataListSort = DataList.GetRows()
    DataList.Close
 
End Function 

 

 

This is a good reusable function that you can use and abuse.  First, you pass an array of data to it and the direction for sorting (ASC or DESC).  The function first creates a new recordset object, saves each element of the array to the database.  Then we do a very clean sort method and then read the sorted results back into a string with a comma between each element.   Since we're asking for an array back, we split the string by commas into an array and pass that as the return results of the function.  Thanks to a nifty suggestion from Paul Randall, we can completely bypass the old way of re-iterating through the rows by using the GetRows() method of the recordset.  This pulls all of the rows into an array.  Nice!

Sweet huh?  This is good example of using disconnected recordsets for sorting but you can use them for all kinds of cool stuff. For more information on disconnected recordsets, click here.

 

So there you have it.  You can now sort array data using old school bubble sort and disconnected recordsets.  Another adventure in vbscripting complete!  Next up, a little something for the network administrators out there: intelligent drive mapping!

 

Until next time, happy scripting!

Corey Thomas

 

Edit Notes: Updated the code to use the GetRows() method instead of iterating through the records.  Much faster.  Thanks for the suggestion Paul.  :)

2 comments:

Anonymous said...

I like using disconnected recordsets for sorting multidimensional arrays. It would be nice if your script mentioned how easy it is to sort multidimentsional arrays on any column with the recordset object. For one dimensional arrays, many users already have the dot net stuff installed to use a much smaller sorting script:

Function SortNet(a1DArray)
Dim i, DataList
Set DataList = CreateObject _
("System.Collections.ArrayList")
For i = 0 To UBound(a1DArray)
DataList.Add a1DArray(i)
Next
DataList.Sort()
SortNet = DataList.ToArray
End Function

It really gripes me that there is no easy way to add an entire single or multidimensional array to the dot net DataList or to a recordset. I think your script should have used the fast, easy way to return the sorted data as an array; the recordset's GetRows property/method returns an array.

-Paul Randall

Corey Thomas said...

That's a good point Paul. There is no real good way to add multi-dimensional arrays (or arrays within arrays).

Good tip on the GetRows method. I'll look at adding that in!