×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

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>

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

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>

 
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.

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