{"id":56193,"date":"2008-02-15T23:00:00","date_gmt":"2008-02-15T23:00:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2008\/02\/15\/hey-scripting-guy-how-can-i-use-a-disconnected-recordset-in-windows-powershell\/"},"modified":"2008-02-15T23:00:00","modified_gmt":"2008-02-15T23:00:00","slug":"hey-scripting-guy-how-can-i-use-a-disconnected-recordset-in-windows-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/hey-scripting-guy-how-can-i-use-a-disconnected-recordset-in-windows-powershell\/","title":{"rendered":"Hey, Scripting Guy! How Can I Use a Disconnected Recordset in Windows PowerShell?"},"content":{"rendered":"

\"Hey, <\/p>\n

Hey, Scripting Guy! How can I use a disconnected recordset in Windows PowerShell? I\u2019m able to create the recordset without any problem, but I can\u2019t figure out how to reference the fields and values in that recordset.<\/p>\n

— JVDL<\/p>\n

\"Spacer\"\"Hey,\"Script<\/a> <\/p>\n

Hey, JVDL. Well, we must say, we\u2019re impressed by how cool, calm, and collected you are. After all, today is the opening day of the 2008 Winter Scripting Games<\/b><\/a>, and while everyone else is frantically reading the event instructions and downloading the Competitors Pack<\/b><\/a> you\u2019re 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?<\/p>\n

Of course, maybe \u2013 also like James Bond \u2013 you\u2019ve uncovered a closely-guarded secret: in one of the Windows PowerShell Scripting Games events the Scripting Guys actually used a disconnected recordset. Is that<\/i> why you\u2019re 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<\/i> your nefarious plan?<\/p>\n

Well, if so, it was a pretty good plan: after all, we are<\/i> going to tell you how to use a disconnected recordset in Windows PowerShell.<\/p>\n

But before you ask, no, we won\u2019t tell you is which<\/i> Scripting Games event we used this disconnected recordset in. And there\u2019s no point in threatening to torture us; we aren\u2019t talking. <\/p>\n

Besides, we\u2019ve already read all of Scripting Guy Jean Ross\u2019 Sesame Script<\/b><\/a> columns. After that, well, it\u2019s hard to take any other threats of torture very seriously.<\/p>\n

Actually, one thing we will tell you is that we didn\u2019t necessarily need<\/i> 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.) <\/p>\n

That, by the way, has always been another closely-guarded secret: when the Scripting Guys write their solutions for the Scripting Games we\u2019re rarely aiming for the \u201coptimal\u201d solution. Instead, we\u2019re 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.<\/p>\n

Perhaps more important, it also gave us an answer to JVDL\u2019s question. You want to know how to use a disconnected recordset in Windows PowerShell? Here\u2019s how:<\/p>\n

$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)<\/pre>\n
Let\u2019s see if we can figure out how this thing works. To begin with, we assign the value 5 to a variable named $adDouble; we\u2019ll 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\u2019s why we chose to use a numeric field in our recordset. Of course, more often than not you\u2019ll probably want to create a variant field. In that case, you\u2019d define and use a variable named $adVarChar:<\/p>\n
$adVarChar = 200<\/pre>\n
After defining the variable $adDouble we define another variable \u2013 $adFldIsNullable \u2013 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\u2019t have<\/i> to enter any data. That\u2019s irrelevant for our sample script, but it can be useful in other cases; therefore, we thought we\u2019d show you how to use it.<\/p>\n
After all, there are no secrets between friends, right?<\/p>\n
Well, except for the one about which Scripting Games event uses a disconnected recordset. But other than that \u2026.<\/p>\n
After taking care of the two variables we next use the New-Object<\/b> cmdlet to create an instance of the ADOR.Recordset<\/b> object:<\/p>\n
$objRecordset = New-Object -com \"ADOR.Recordset\"<\/pre>\n
Note that we need to add the \u2013com<\/b> parameter here. That\u2019s because we\u2019re dealing with a COM object rather than a .NET Framework class.<\/p>\n
Once we have a recordset object we can then add a field to the recordset by using code like this:<\/p>\n
$objRecordset.Fields.Append(\"Score\", $adDouble, $Null, $AdFldIsNullable)<\/pre>\n
As you can see, all we\u2019re doing is calling the Append<\/b> method followed by four parameters:<\/p>\n\n\n\n\n\n\n
\u2022<\/td>\n\n
Score<\/b>. This is the name we\u2019re giving to the field.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
$adDouble<\/b>. This is the data type of the field. Here we\u2019re using the variable $adDouble to indicate that we want the double data type.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
$Null<\/b>. Parameter 3 is used to indicate the maximum number of characters that can be stored in the field. That\u2019s irrelevant for a numeric field, but PowerShell requires us to put something<\/i> in this slot. Hence we use a null value ($Null), which causes the script to leave this parameter as-is.<\/p>\n<\/td>\n<\/tr>\n
\u2022<\/td>\n\n
$adFldIsNullable<\/b>. Like we said, not required for this script. But this parameter enables us to indicate that the Score field does not have<\/i> to contain data. That could be useful in a recordset with more fields. For example, you might have personnel information that includes a field for the user\u2019s cell phone number. You might want to make that field \u201cnullable\u201d in case \u2013 hard as this might be to believe \u2013 you have an employee who doesn\u2019t have a cell phone.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n<\/thead>\n\n\n
\n
True story<\/b>. To attend the TechEd IT Forum<\/b><\/a> conference in Barcelona you had to include emergency contact information. Furthermore, you had to include a cell phone number for this emergency contact. You say your spouse, mother, or best friend doesn\u2019t have a cell phone? Then you can\u2019t go to TechEd IT Forum.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
<\/div>\n
For our sample script we\u2019re adding just one field; if we wanted to add additional fields, however, we\u2019d simply call the Append method as many times as needed, each time specifying the appropriate information. Once we\u2019ve defined all the fields we then call the Open<\/b> method to make our disconnected recordset operational:<\/p>\n
$objRecordset.Open()<\/pre>\n
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:<\/p>\n
for ($i = 1; $i -le 10; $i++)<\/pre>\n
Inside this loop we call the AddNew<\/b> record to add a new record to the recordset:<\/p>\n
$objRecordset.AddNew()<\/pre>\n
That brings us to this line of code:<\/p>\n
$objRecordset.Fields.Item(\"Score\") = $i<\/pre>\n
That\u2019s<\/i> how you refer to a field in a disconnected recordset, at least in Windows PowerShell. In VBScript we\u2019d write this same line of code like this:<\/p>\n
objRecordset(\"Score\") = i<\/pre>\n
As JVDL discovered, however, this doesn\u2019t work in PowerShell. Instead, you need to specify the complete object path: $objRecordset.Fields.Item(“Score”)<\/b>. 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.<\/p>\n
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:<\/p>\n
$objRecordset.Sort = \"Score DESC\"$objRecordset.MoveFirst();<\/pre>\n
And no, we didn\u2019t have<\/i> to do this. However, by sorting the recordset in descending order we\u2019ll be able to demonstrate that we really can<\/i> work with numeric data.<\/p>\n
Which, again, is what JVDL was trying to do in the first place.<\/p>\n
So how do<\/i> 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\u2019s EOF<\/b> (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<\/b>. Once that\u2019s done we call the MoveNext<\/b> method and repeat the process with the next record in the recordset.<\/p>\n
Doing all that requires a chunk of code no more complicated than this:<\/p>\n
do     {        $objRecordset.Fields.Item(\"Score\").Value        $objRecordset.MoveNext()    }until ($objRecordset.EOF)<\/pre>\n
And that\u2019s<\/i> how you use a disconnected recordset in Windows PowerShell.<\/p>\n
That should do it for now. We won\u2019t 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<\/b><\/a> (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 20th<\/sup>. Not sure you can finish your events by then? That\u2019s fine; just do Events 3 and 4 instead. (Those aren\u2019t due until Friday, February 22nd<\/sup>.) Or if you\u2019re really<\/i> strapped for time just do Event 9, which isn\u2019t due until Monday, March 3rd<\/sup>. Granted it will be hard to get a perfect score if you complete only one event. However, enter just one event and you will<\/i> be eligible to win one the many fantastic prizes<\/b><\/a> being given away as part of the 2008 Scripting Games. Even better, you don\u2019t even have to successfully complete the event. Just send in an entry of some<\/i> kind and you\u2019ll be eligible for a prize.<\/p>\n
No, don\u2019t bother to thank us. That\u2019s just our way of making up for the fact that we wouldn\u2019t tell you that we used a disconnected recordset in Event 2<\/b><\/a> of the Advanced Division.<\/p>\n\n<\/thead>\n\n\n
\n
Note<\/b>. Shoot; we sort of spilled the beans there, didn\u2019t we? Oh, well; easy come, easy go. Remember, though, that you don\u2019t have<\/i> to use a disconnected recordset in Event 2, and we would never suggest that using such a thing is the best way to complete the event. As it is, we went that route mainly because we just wanted to try using a disconnected recordset in Windows PowerShell. But if you can\u2019t think of any other way to deal with the data in Event 2, well \u2026.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I use a disconnected recordset in Windows PowerShell? I\u2019m able to create the recordset without any problem, but I can\u2019t figure out how to reference the fields and values in that recordset. — JVDL Hey, JVDL. Well, we must say, we\u2019re impressed by how cool, calm, and collected you are. […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[19,146,3,45],"class_list":["post-56193","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-windows-powershell"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I use a disconnected recordset in Windows PowerShell? I\u2019m able to create the recordset without any problem, but I can\u2019t figure out how to reference the fields and values in that recordset. — JVDL Hey, JVDL. Well, we must say, we\u2019re impressed by how cool, calm, and collected you are. […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56193","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=56193"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/56193\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=56193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=56193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=56193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}