Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using disconnected recordsets to sort in VBScript

File and Data Processing

Using disconnected recordsets to sort in VBScript

by  dilettante  Posted    (Edited  )
One of the tools most overlooked by desktop scripters (WSH, HTA, etc.) is ActiveX Data Objects (ADO). Not only does this provide you with things like database access and manipulation facilities, it also offers things like:
[ul][li]Binary I/O (sorry FSO, I know you mean well...),[/li]
[li]Data access to CSV or fixed-field text files,[/li][/ul]
... and loads of other good stuff.

In this case we're looking for a way to sort a list of information. ADO offers a handy feature called the disconnected recordset. While this seems to imply that the recordset was once connected to something, this doesn't have to be true at all. By using a recordset we get access to some powerful ADO capabilities including filtering and sorting!

Here is a brief WSH example. It is a WSF rather than a VBS file. This gives us a number of additional capabilities, but here I use two:
[ul][li]Declarative object definition - I don't need to call CreateObject( ) and I don't need to set the object reference to Nothing to clean it up. There are other subtle advantages as well.[/li]
[li]Declarative reference definition - I can declare a reference to a type library and get access to things like the built-in constants.[/li][/ul]
Ok, enough of that. Here we go:

[color blue]ADO Sort.wsf[/color][color brown]
Code: 
<job>
  <object id = oRS progid = "ADODB.Recordset"/>
  <reference object = "ADODB.Recordset"/>
  <script language = "VBScript">
    Option Explicit
    
    Sub DefineAndOpenRS()
      'Define and open the disconnected recordset
      
      With oRS
        .ActiveConnection = Nothing
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        With .Fields
          .Append "MyStuff", adVarChar, 255
        End With
        .Open
      End With
    End Sub
    
    Sub InsertRow(sData)
      'Add data to a new row in the recordset
      
      oRs.AddNew
      oRS.Fields.Item("MyStuff").Value = sData
    End Sub
    
    Function ConcatRows()
      With oRS
        .MoveFirst
        ConcatRows = ""
        Do
          ConcatRows = ConcatRows _
            & .Fields.Item("MyStuff").Value _
            & vbNewLine
          .MoveNext
        Loop Until .EOF
      End With
    End Function
    
    Dim lItem, sResults
    
    DefineAndOpenRS
    For lItem = 1 To 5
      InsertRow(CStr(Rnd)) 'Just some random data.
    Next
    oRS.Sort = "MyStuff ASC"
    sResults = ConcatRows()
    oRS.Close
    MsgBox sResults, vbOkOnly
  </script>
</job>
[/color]
This example creates a random list of data, sorts it, and displays it via MsgBox. It requires MDAC 2.1 or greater (2.7 SP 1 is the current release as I write this). I tested it with MDAC 2.5 myself.

While I created a recordset with only one field, I could easily have added several others if I had the need. Note the use of ADO constants without my having to declare them all as Consts.

Here is another example, showing a sort using two fields as sort keys:

[color blue]ADO 2 Col Sort.wsf[/color][color brown]
Code: 
<job>
  <object id = oRS progid = "ADODB.Recordset"/>
  <reference object = "ADODB.Recordset"/>
  <script language = "VBScript">
    'This script demonstrates the use of an ADO disconnected
    'Recordset object to sort data on multiple fields.
    '
    'Here I make heavy use of the Variant "array of arrays"
    'concept to keep this example short.  For example lngAryA
    'is Variant containing an Array of Arrays rather than a
    '2-dimensional array.

    Option Explicit
    
    Sub DefineAndOpenRS(strAryCols)
      'Define and open the disconnected recordset
      Dim strCol
      
      With oRS
        .ActiveConnection = Nothing
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockBatchOptimistic
        With .Fields
          For Each strCol In strAryCols
            .Append strCol, adInteger
          Next
        End With
        .Open
      End With
    End Sub
    
    Dim lngAryA, lngRow, strAryColNames, strResults
    
    lngAryA = Array(Array(1, 7, 0), _
                    Array(5, 9, 1), _
                    Array(1, 1, 2), _
                    Array(2, 0, 3), _
                    Array(2, 3, 1), _
                    Array(4, 2, 0), _
                    Array(3, 0, 0), _
                    Array(1, 0, 1))

    'Define a Recordset.
    strAryColNames = Array("Col0", "Col1", "Col2")
    DefineAndOpenRS(strAryColNames)

    'Load our array into the Recordset.
    For lngRow = 0 To UBound(lngAryA)
      oRS.AddNew strAryColNames, lngAryA(lngRow)
    Next

    oRS.Sort = "Col0 ASC, Col2 ASC"

    'Show results and finish.
    oRS.MoveFirst
    strResults = oRS.GetString(, , " ", vbCrLf, "-")
    oRS.Close
    MsgBox strResults, vbOkOnly
  </script>
</job>
[/color]

This example doesn't pull the data back out of the recordset into the array, but it could. Instead it demonstrates the useful GetString( ) method for building the response text.

The best reference I've found for ADO is a CHM file called "ADO Help" in the Microsoft Data Access SDK. The SDK is a free download you can find via MSDN Online:

http://msdn.microsoft.com/dataaccess

Happy scripting.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top