Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBScript FAQ

File and Data Processing

Using disconnected recordsets to sort in VBScript by dilettante
Posted: 23 Mar 03 (Edited 26 Apr 04)

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:
  • Binary I/O (sorry FSO, I know you mean well...),
  • Data access to CSV or fixed-field text files,
... 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:
  • 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.
  • Declarative reference definition - I can declare a reference to a type library and get access to things like the built-in constants.
Ok, enough of that.  Here we go:
ADO Sort.wsf


  <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
      End With
    End Sub
    Sub InsertRow(sData)
      'Add data to a new row in the recordset
      oRS.Fields.Item("MyStuff").Value = sData
    End Sub
    Function ConcatRows()
      With oRS
        ConcatRows = ""
          ConcatRows = ConcatRows _
            & .Fields.Item("MyStuff").Value _
            & vbNewLine
        Loop Until .EOF
      End With
    End Function
    Dim lItem, sResults
    For lItem = 1 To 5
      InsertRow(CStr(Rnd)) 'Just some random data.
    oRS.Sort = "MyStuff ASC"
    sResults = ConcatRows()
    MsgBox sResults, vbOkOnly

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:

ADO 2 Col Sort.wsf


  <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
        End With
      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")

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

    oRS.Sort = "Col0 ASC, Col2 ASC"

    'Show results and finish.
    strResults = oRS.GetString(, , " ", vbCrLf, "-")
    MsgBox strResults, vbOkOnly

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:

Happy scripting.

Back to VBScript FAQ Index
Back to VBScript Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close