Hey, Scripting Guy! How can I use a disconnected recordset in Windows PowerShell? I’m able to create the recordset without any problem, but I can’t figure out how to reference the fields and values in that recordset.

— JVDL

Hey, JVDL. Well, we must say, we’re impressed by how cool, calm, and collected you are. After all, today is the opening day of the 2008 Winter Scripting Games, and while everyone else is frantically reading the event instructions and downloading the Competitors Pack you’re over here asking questions about using a disconnected recordset in Windows PowerShell. Like we said, your grace under fire is very impressive. You must be like the James Bond of scripting, eh?

Of course, maybe – also like James Bond – you’ve uncovered a closely-guarded secret: in one of the Windows PowerShell Scripting Games events the Scripting Guys actually used a disconnected recordset. Is that why you’re here, JVDL? Did you ask a seemingly-innocuous question in the hopes that the Scripting Guys would slip up and accidentally give you information that might prove invaluable for someone competing in the Winter Scripting Games? Is that your nefarious plan?

Well, if so, it was a pretty good plan: after all, we are going to tell you how to use a disconnected recordset in Windows PowerShell.

But before you ask, no, we won’t tell you is which Scripting Games event we used this disconnected recordset in. And there’s no point in threatening to torture us; we aren’t talking.

Besides, we’ve already read all of Scripting Guy Jean Ross’ Sesame Script columns. After that, well, it’s hard to take any other threats of torture very seriously.

Actually, one thing we will tell you is that we didn’t necessarily need to use a disconnected recordset to solve that Scripting Games event; we used it, in part, because it was easy and, in part, because we were curious whether or not a disconnected recordset could even be used in PowerShell. (Official Scripting Guys Finding: it can.)

That, by the way, has always been another closely-guarded secret: when the Scripting Guys write their solutions for the Scripting Games we’re rarely aiming for the “optimal” solution. Instead, we’re looking for two things: 1) solutions that are easy to explain; and, 2) the chance to try a few techniques we might not otherwise get to try. Hard as this might be to believe, during the course of day-to-day life the need to use a disconnected recordset in a Windows PowerShell script hardly ever comes up. The Scripting Games gave us a good excuse to give disconnected recordsets a try.

Perhaps more important, it also gave us an answer to JVDL’s question. You want to know how to use a disconnected recordset in Windows PowerShell? Here’s how:

$adDouble = 5$adFldIsNullable = 32$objRecordset = New-Object -com "ADOR.Recordset"$objRecordset.Fields.Append("Score", $adDouble, $Null, $AdFldIsNullable)$objRecordset.Open()for ($i = 1; $i -le 10; $i++)    {        $objRecordset.AddNew()        $objRecordset.Fields.Item("Score") = $i    }$objRecordset.Sort = "Score DESC"$objRecordset.MoveFirst();do     {        $objRecordset.Fields.Item("Score").Value        $objRecordset.MoveNext()    }until ($objRecordset.EOF)

Let’s see if we can figure out how this thing works. To begin with, we assign the value 5 to a variable named $adDouble; we’ll use this variable to create a numeric field for our disconnected recordset. (That is, a field with the double data type.) In case anyone is wondering, JVDL is looking at disconnected recordsets as a way to grab a bunch of data and then sort it on a numeric field; that’s why we chose to use a numeric field in our recordset. Of course, more often than not you’ll probably want to create a variant field. In that case, you’d define and use a variable named $adVarChar:

$adVarChar = 200

After defining the variable $adDouble we define another variable – $adFldIsNullable – and set the value to 32. This variable enables us to create a field that contains Null values; in other words, a field where we don’t have to enter any data. That’s irrelevant for our sample script, but it can be useful in other cases; therefore, we thought we’d show you how to use it.

After all, there are no secrets between friends, right?

Well, except for the one about which Scripting Games event uses a disconnected recordset. But other than that ….

After taking care of the two variables we next use the New-Object cmdlet to create an instance of the ADOR.Recordset object:

$objRecordset = New-Object -com "ADOR.Recordset"

Note that we need to add the –com parameter here. That’s because we’re dealing with a COM object rather than a .NET Framework class.

Once we have a recordset object we can then add a field to the recordset by using code like this:

$objRecordset.Fields.Append("Score", $adDouble, $Null, $AdFldIsNullable)

As you can see, all we’re doing is calling the Append method followed by four parameters:

For our sample script we’re adding just one field; if we wanted to add additional fields, however, we’d simply call the Append method as many times as needed, each time specifying the appropriate information. Once we’ve defined all the fields we then call the Open method to make our disconnected recordset operational:

$objRecordset.Open()

Now we come to the part where JVDL ran into trouble: actually working with the recordset. (More specifically, referring to the fields in the recordset.) To demonstrate how you can add data to a disconnected recordset we first use this line of code to set up a for loop that runs from 1 to 10:

for ($i = 1; $i -le 10; $i++)

Inside this loop we call the AddNew record to add a new record to the recordset:

$objRecordset.AddNew()

That brings us to this line of code:

$objRecordset.Fields.Item("Score") = $i

That’s how you refer to a field in a disconnected recordset, at least in Windows PowerShell. In VBScript we’d write this same line of code like this:

objRecordset("Score") = i

As JVDL discovered, however, this doesn’t work in PowerShell. Instead, you need to specify the complete object path: $objRecordset.Fields.Item(“Score”). That will get you where you need to go. And, in this case, it will set the value of the Score field for our new record to the value of the loop variable $i.

After we finish looping through the for loop we then use these two lines of code to sort the recordset in descending order and then move to the first record in that sorted set:

$objRecordset.Sort = "Score DESC"$objRecordset.MoveFirst();

And no, we didn’t have to do this. However, by sorting the recordset in descending order we’ll be able to demonstrate that we really can work with numeric data.

Which, again, is what JVDL was trying to do in the first place.

So how do we demonstrate that we can work with numeric data in our disconnected recordset? Well, for starters we set up a do loop that runs until we reach the end of the recordset; that is, until the recordset’s EOF (end-of-file) property is True. Inside the loop we echo back the value of the first record in the recordset, once again using the complete path to reference the Score field: $objRecordset.Fields.Item(“Score”).Value. Once that’s done we call the MoveNext method and repeat the process with the next record in the recordset.

Doing all that requires a chunk of code no more complicated than this:

do     {        $objRecordset.Fields.Item("Score").Value        $objRecordset.MoveNext()    }until ($objRecordset.EOF)

And that’s how you use a disconnected recordset in Windows PowerShell.

That should do it for now. We won’t keep you any longer; after all, we know that many of you are anxious to get started on your first set of Scripting Games events (at least one of which ought to be pretty easy now that you know how to use disconnected recordsets in Windows PowerShell). Remember, entries for Events 1 and 2 in all divisions (VBScript, Windows PowerShell, and Perl) are due on Wednesday, February 20^th. Not sure you can finish your events by then? That’s fine; just do Events 3 and 4 instead. (Those aren’t due until Friday, February 22^nd.) Or if you’re really strapped for time just do Event 9, which isn’t due until Monday, March 3^rd. Granted it will be hard to get a perfect score if you complete only one event. However, enter just one event and you will be eligible to win one the many fantastic prizes being given away as part of the 2008 Scripting Games. Even better, you don’t even have to successfully complete the event. Just send in an entry of some kind and you’ll be eligible for a prize.

No, don’t bother to thank us. That’s just our way of making up for the fact that we wouldn’t tell you that we used a disconnected recordset in Event 2 of the Advanced Division.