ADO, VBS and ASP classic: Create dummy recordset.

[Guest an0_nym0u5]

Hi,I've spent quite a some time, when trying to figure out how to create a temporary recordset. Of course I could have created a table, but I've found ADO recordsets to have many features, which makes programming easier: .Filter, .RecordCount and .Sort to name a few. Here's my suggestion for an example to demonstrate a temporary recordset:

' Create dummy recordset. ' This demonstrastes: ' 1) How to append new field to a empty recordset. ' 2) How to add new values to those fields. ' 3) How to save the recordset as a xml file. ' ' Main sectionConst adVarChar = 200, adDate = 7' Other ADO Data Types [url="http://www.w3schools.com/ADO/ado_datatypes.asp"]http://www.w3schools.com/ADO/ado_datatypes.asp[/url]Const adFldIsNullable = 32' adFldIsNullable hex value 20 = decimal value 32' You can define field attributes, for example you can enable null value in a field. ' More on field attributes:' [url="http://www.devguru.com/Technologies/ado/quickref/record_fieldscollection.html"]http://www.devguru.com/Technologies/ado/qu...collection.html[/url]' [url="http://msdn.microsoft.com/en-us/library/ms676553(v=VS.85).aspx"]http://msdn.microsoft.com/en-us/library/ms...3(v=VS.85).aspx[/url]' ' Create empty recordset. Set objRecordSet = NothingSet objRecordSet = CreateObject( "ADODB.Recordset" )' Create fields in recordset. ' syntax: {object}.Fields.Append Name, Type, DefineSize, Attrib, FieldValue ' [url="http://www.devguru.com/Technologies/ado/quickref/record_fieldscollection.html"]http://www.devguru.com/Technologies/ado/qu...collection.html[/url]objRecordSet.Fields.Append "MyField1", adVarChar, 20  ' Creating 20 characters long field. objRecordSet.Fields.Append "MyField2", adVarChar, 20objRecordSet.Fields.Append "MyDate1", adDate, ,adFldIsNullable' ' Open recordset. objRecordSet.Open' ' Fill in some values to the fields. For i = 1 to 3    objRecordSet.AddNew                                      ' Add new record    objRecordSet( "MyField1" ) = i & ". value in MyField1"   ' Add values to the fields in the record.    objRecordSet( "MyField2" ) = i & ". value in MyField2"    objRecordSet( "MyDate1" ) = null   objRecordSet.UpdateNext' ' Pass the recordset to function, which shows the values of the fields of each record in the recordset. If ShowRecords(objRecordSet) Then    '    ' Save the recordset as a xml file.    objRecordSet.Save "dummy_recordset.xml", 1Else    Wscript.Echo "Empty recordset. " & objRecordSet.RecordCountEnd If' End of main sectionFunction ShowRecords(objRecordSet)ShowRecords = FalseIf objRecordSet.RecordCount > 0 Then   Wscript.Echo "Recordcount: " & objRecordSet.RecordCount   objRecordSet.MoveFirst   Do Until objRecordSet.EOF      For Each strField in objRecordSet.Fields           Wscript.Echo Trim(strField.Name) & "=" & strField.Value      Next      Wscript.Echo String(30, "-")      objRecordSet.MoveNext      ShowRecords = True   LoopEnd IfEnd Function

Edited April 23, 2010 by an0_nym0u5

[Head]

This is a very clean little piece of programming. I agree that the .sort function is very useful. However, I tend to get very lazy. I tend to use tables or even DOS to throw up information like that, usually while playing video games and listening to music on my headphones. Maybe I should just learn more about recordset.

Edited May 2, 2011 by Head

[an0_nym0u5]

The code seems to be broken, so here it is one again with updated links:

' Create dummy recordset. ' This demonstrastes: ' 1) How to append new field to a empty recordset. ' 2) How to add new values to those fields. ' 3) How to save the recordset as a xml file. ' ' Main sectionConst adVarChar = 200, adDate = 7' Other ADO Data Types http://www.w3schools.com/asp/ado_datatypes.aspConst adFldIsNullable = 32' adFldIsNullable hex value 20 = decimal value 32' You can define field attributes, for example you can enable null value in a field. ' More on field attributes:' http://www.devguru.com/technologies/ado/8763' http://www.devguru.com/technologies/ado/8601' https://msdn.microsoft.com/en-us/library/ms676553(v=VS.85).aspx' ' Create empty recordset. Set objRecordSet = NothingSet objRecordSet = CreateObject( "ADODB.Recordset" )' Create fields in recordset. ' syntax: {object}.Fields.Append Name, Type, DefineSize, Attrib, FieldValue ' http://www.devguru.com/Technologies/ado/qu...collection.htmlobjRecordSet.Fields.Append "MyField1", adVarChar, 20  ' Creating 20 characters long field. objRecordSet.Fields.Append "MyField2", adVarChar, 20objRecordSet.Fields.Append "MyDate1", adDate, ,adFldIsNullable' ' Open recordset. objRecordSet.Open' ' Fill in some values to the fields. For i = 1 to 3    objRecordSet.AddNew                                      ' Add new record    objRecordSet( "MyField1" ) = i & ". value in MyField1"   ' Add values to the fields in the record.    objRecordSet( "MyField2" ) = i & ". value in MyField2"'    objRecordSet( "MyDate1" ) = null    objRecordSet( "MyDate1" ) = Now()   objRecordSet.UpdateNext' ' Pass the recordset to function, which shows the values of the fields of each record in the recordset. If ShowRecords(objRecordSet) Then    '    ' Save the recordset as a xml file.    objRecordSet.Save "dummy_recordset.xml", 1Else    Wscript.Echo "Empty recordset. " & objRecordSet.RecordCountEnd If' End of main sectionFunction ShowRecords(objRecordSet)ShowRecords = FalseIf objRecordSet.RecordCount > 0 Then   Wscript.Echo "Recordcount: " & objRecordSet.RecordCount   objRecordSet.MoveFirst   Do Until objRecordSet.EOF      For Each strField in objRecordSet.Fields           Wscript.Echo Trim(strField.Name) & "=" & strField.Value      Next      Wscript.Echo String(30, "-")      objRecordSet.MoveNext      ShowRecords = True   LoopEnd IfEnd Function

Edited July 19, 2015 by an0_nym0u5