{"id":66283,"date":"2006-10-11T11:45:00","date_gmt":"2006-10-11T11:45:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/10\/11\/how-can-i-delete-a-record-from-a-disconnected-recordset\/"},"modified":"2006-10-11T11:45:00","modified_gmt":"2006-10-11T11:45:00","slug":"how-can-i-delete-a-record-from-a-disconnected-recordset","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-delete-a-record-from-a-disconnected-recordset\/","title":{"rendered":"How Can I Delete a Record From a Disconnected Recordset?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I delete a record \u2013 or a set of records \u2013 from a disconnected recordset?

— SM<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

eHy sm.,. <\/P>\n

Sorry; let\u2019s try that again: <\/P>\n

Hey, SM. We apologize for the poor typing the first time around; the Scripting Guy who writes this column is trying to adjust to typing with just one hand and just one eye. Last night this Scripting Guy and his Scripting Son engaged in a \u2026 spirited \u2026 game of basketball, a game which ended with up with:<\/P>\n\n\n\n\n\n
\u2022<\/TD>\n\n

The Scripting Dad jamming his finger while blocking a shot. (To add insult to injury he did<\/I> block the shot, but the Scripting Son simply picked the ball up and made the basket anyway.)<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

The Scripting Son poking the Scripting Dad in the eye while trying to steal the ball.<\/P><\/TD><\/TR>\n

\u2022<\/TD>\n\n

The Scripting Son \u201caccidentally\u201d shoving the Scripting Dad headfirst into a wall.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

And that was just in the first 5 minutes. After that the game began to get a little rough.<\/P>\n

But do you think a few bumps and bruises would be enough to keep the Scripting Guy who writes this column from, well, writing this column? Well, to tell you the truth it would have. However, after seeing what passes for daytime television these days he decided he might as well come in to work and figure out how to delete a record from a disconnected recordset.<\/P>\n

Before we begin we should note that we aren\u2019t going to talk about disconnected recordsets in much detail today; for more information take a peek at the Microsoft Windows 2000 Scripting Guide<\/B><\/A> or at this Scripting Week 2 webcast<\/B><\/A>. For now we\u2019ll just mention the fact that a disconnected recordset is like a database table that exists only in memory; it\u2019s not tied to a real, live database file. <\/P>\n

Second, as far as we know, you\u2019re right, SM: you can\u2019t query a disconnected recordset, at least not by using a query like Select * From<\/I> or Delete * From. <\/I>As you noted, you tried different variations on these queries and none of them seemed to work. For better or worse, that\u2019s to be expected.<\/P>\n

So is that going to be a problem? Let\u2019s put it this way: after you\u2019ve been flung headfirst into a wall, deleting a record from a disconnected recordset hardly seems like a problem at all.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. In all fairness, the Scripting Son did not fling his father headfirst into the wall and then simply walk away without a word. Instead he turned to his dad and said, in all sincerity, \u201cYou traveled. My ball.\u201d<\/P>\n

Of course, had the circumstances been reversed, the Scripting Dad probably would have said the same thing.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Let\u2019s start out by taking a look at a script that creates a simple disconnected recordset and then deletes a record from that recordset. We\u2019ll explain how this sample script works, then show you a modified version that will prove that it really does delete a record from the disconnected recordset.<\/P>\n

Here\u2019s the script:<\/P>

Const adVarChar = 200\nConst MaxCharacters = 255\nConst adFldIsNullable = 32<\/p>\n
Set DataList = CreateObject(“ADOR.Recordset”)\nDataList.Fields.Append “Name”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Open<\/p>\n
arrItems = Array(“A”,”B”,”C”,”D”,”E”,”F”,”G”)<\/p>\n
For Each strItem in arrItems\n    DataList.AddNew\n    DataList(“Name”) = strItem\n    DataList.Update\nNext<\/p>\n
DataList.Filter = “Name = ‘B'”<\/p>\n
DataList.MoveFirst<\/p>\n
Do While Not DataList.EOF\n    DataList.Delete\n    DataList.MoveNext\nLoop<\/p>\n
DataList.Filter = “”\n<\/PRE>\n
Like we said, we don\u2019t have the time to discuss the ins and outs of disconnected recordsets in today\u2019s column. (Besides, you<\/I> try typing a detailed discussion of disconnected recordsets with just one hand!) Therefore we\u2019ll simply note that the following block of code creates \u2013 and then opens \u2013 a disconnected recordset consisting of a single field (Name<\/B>):<\/P>
Const adVarChar = 200\nConst MaxCharacters = 255\nConst adFldIsNullable = 32<\/p>\n
Set DataList = CreateObject(“ADOR.Recordset”)\nDataList.Fields.Append “Name”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Open\n<\/PRE>\n
Once we have a disconnected recordset our next step is to add some data to the thing. We said this was going to be simple, and we meant it: our recordset is going to consist of the letters A<\/I>, B<\/I>, C<\/I>, D<\/I>, E<\/I>, F<\/I>,and G<\/I>. In preparation for adding these records to the recordset we first store each of the letters in an array named arrItems:<\/P>
arrItems = Array(“A”,”B”,”C”,”D”,”E”,”F”,”G”)\n<\/PRE>\n
We then set up a For Each loop to loop through each of the items in the array. For each of these items we call the AddNew<\/B> method to create a new record in the disconnected recordset. We set the value of the Name field to the value of the array item, then call the Update<\/B> method to write the new record to the recordset. That\u2019s what we do in this block of code:<\/P>
For Each strItem in arrItems\n    DataList.AddNew\n    DataList(“Name”) = strItem\n    DataList.Update\nNext\n<\/PRE>\n
In turn, that gives us a disconnected recordset consisting of the following records:<\/P>
A\nB\nC\nD\nE\nF\nG\n<\/PRE>\n
Having worked so hard to add these records to the recordset we\u2019re now going to turn right around and delete one of these records. <\/P>\n
Good question: how are<\/I> we going to do that when we can\u2019t even query a disconnected recordset? Well, what we can<\/I> do is add a Filter<\/B> to the recordset. This will have the effect of temporarily filtering out all the records in the recordset except those we want to delete. We can then go ahead and delete those records and then remove the filter. <\/P>\n
Maybe this will make more sense as we walk you through it. First, take a look at the filter we apply to the recordset:<\/P>
DataList.Filter = “Name = ‘B'”\n<\/PRE>\n
There\u2019s really nothing magical about this filter; it simply says that, for the moment anyway, we want to work with only those records where the value of the Name field is equal to B<\/I>. Because we have only one record in the recordset that has the Name B<\/I> only one record will be deleted; if we had multiple records with the Name B<\/I> all of those records would be deleted.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Yes, if we had a \u201cfancier\u201d recordset we could apply a fancier filter, one that used an AND clause or an OR clause or, well, pretty much anything short of a Santa clause.<\/P>\n
Hey, come on, give us a break: after all, we did<\/I> get smashed headfirst into a wall last night.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
After applying the filter we use the MoveFirst<\/B> method to move to the first record in our recordset, a recordset which \u2013 thanks to the Filter \u2013 now consists of just a single record. We then use this block of code to loop through the recordset and delete each and every record in the filtered recordset (which, again, will just be the one record that has the Name B<\/I>):<\/P>
Do While Not DataList.EOF\n    DataList.Delete\n    DataList.MoveNext\nLoop\n<\/PRE>\n
When we\u2019re done we remove the filter by setting the Filter property to an empty string:<\/P>
DataList.Filter = “”\n<\/PRE>\n
And there you have it: we\u2019ve deleted a record from a disconnected recordset, despite having jammed our deleting finger.<\/P>\n
Of course, in the preceding script there\u2019s no way to verify that the record really did<\/I> get deleted. Because of that we put together a modified version of the script. In this script we echo back all the records in the recordset before we apply the filter. We apply the filter, delete the record, then remove the filter and echo back the recordset again. If all goes well, record B<\/I> should have disappeared.<\/P>\n
Here\u2019s the code:<\/P>
Const adVarChar = 200\nConst MaxCharacters = 255\nConst adFldIsNullable = 32<\/p>\n
Set DataList = CreateObject(“ADOR.Recordset”)\nDataList.Fields.Append “Name”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Open<\/p>\n
colItems = Array(“A”,”B”,”C”,”D”,”E”,”F”,”G”)<\/p>\n
For Each strItem in colItems\n    DataList.AddNew\n    DataList(“Name”) = strItem\n    DataList.Update\nNext<\/p>\n
DataList.MoveFirst<\/p>\n
Do While Not DataList.EOF\n    Wscript.Echo DataList.Fields.Item(“Name”)\n    DataList.MoveNext\nLoop<\/p>\n
Wscript.Echo<\/p>\n
DataList.Filter = “Name = ‘B'”<\/p>\n
DataList.MoveFirst<\/p>\n
Do While Not DataList.EOF\n    DataList.Delete\n    DataList.MoveNext\nLoop<\/p>\n
DataList.Filter = “”<\/p>\n
Do While Not DataList.EOF\n    Wscript.Echo DataList.Fields.Item(“Name”)\n    DataList.MoveNext\nLoop\n<\/PRE>\n
And here\u2019s the output:<\/P>
A\nB\nC\nD\nE\nF\nG<\/p>\n
A\nC\nD\nE\nF\nG\n<\/PRE>\n
Hopefully record B<\/I> is no longer in the recordset. (We can\u2019t verify that ourselves because our left eye is starting to water up again. But we\u2019re pretty sure it worked.)<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I delete a record \u2013 or a set of records \u2013 from a disconnected recordset?— SM eHy sm.,. Sorry; let\u2019s try that again: Hey, SM. We apologize for the poor typing the first time around; the Scripting Guy who writes this column is trying to adjust to typing with just […]<\/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,5],"class_list":["post-66283","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-activex-data-objects-ado","tag-databases","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I delete a record \u2013 or a set of records \u2013 from a disconnected recordset?— SM eHy sm.,. Sorry; let\u2019s try that again: Hey, SM. We apologize for the poor typing the first time around; the Scripting Guy who writes this column is trying to adjust to typing with just […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66283","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=66283"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/66283\/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=66283"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=66283"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=66283"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}