Using an ADO Standalone/Custom Recordset in VBScript

By Stephan Onisick

Ever since learning about databases, I've been fascinated with the variety of ways to retrieve stored data with recordset objects. The trouble has always been connecting to these databases. Whether its Access, SQL Server, or Oracle, connecting means traversing layers of expensive proprietary software and possibly some network before gaining access to their organizational magic. This article suggests a way of using standalone/custom recordsets for their ability to organize record data.

Arrays and Collections

Arrays and Collections, in contrast, are more available as programming constructs, at least, in Visual Basic or VBScript. The shortcoming of an array is that you need the index of an element to retrieve it. Not all real-world data can be expressed as a number, i.e., index. A VB collection, on the other hand, provides access by key; however, the simple act of listing the keys of the collection is a bit of a bugger. In short, you almost need a collection to keep track of significant array elements or possibly an array to keep track of collection keys.

And what about displaying an array or collections in a different "sort order" or only those elements that match certain criteria? This feat is not easily accomplished without a considerable investment in programming routines.

Active Data Objects

Then came the data-access method ADO 2.0 (ActiveX Data Objects from Microsoft) - better yet 2.1 or higher. A recordset can now exist without a connection object. In short, a connection to a database is not essential for a recordset. This paved the way for disconnected and standalone recordsets. With the advent of Web technology, disconnected recordsets are used routinely in Active Server Pages (ASP) to pass data between Web sites and browsers.

Standalone Recordset

The standalone/custom recordset acts identically to the disconnected recordset on the client side. A standalone recordset is simply a recordset used as an internal programming construct to organize data in much the same way as arrays and collections have been used.

The idea is to create a recordset and use its organizational ability to perform logical tasks with data without connecting to a database. The remainder of this article expounds on this concept by using a small application written using VBScript, ADO 2.1, and an Excel spreadsheet. The application was written to record and print computer expenses for tax preparations. (At least I've had a little fun preparing taxes this year). The application saves the standalone/custom recordset to a file in XML format. The XML-formatted file can be updated with new data simply by reopening it as a recordset and using normal recordset methods. For this reason, the ADO version must be at least 2.1 (ADO 2.0 did not allow a file to be saved in XML format).

VBScript

Why VBScript - instead of VB? Probably curiosity, first and foremost, to see how far I could get with a VBScript application. As a developer, I have been using VBScript extensively to test COM objects, and that VBScript can downloaded for free from Microsoft enchanted me.

See instructions at the end of this article for downloading VBScript 5.1 and ADO 2.1. All you need is a 32-bit operating system - Windows 95, Windows 98, Windows NT, Windows 2000 - and access to the Web, or a friend who has access and a floppy drive.

Main Programming Loop

The program is structured around a simple input loop based on the InputBox function:

Figure 1


Dim rc
RecordsetOpen=0
LoadMessages
OpenNeworSavedRecordset
Do
	rc=InputBox(InputMessage,"Enter Selection")
	If rc <> "" and Ucase(rc) <> "Q" then
		Select Case UCase(rc)
			Case "I"
				Call InputData()
			Case "S"
				Call SaveData()
			Case "R"
				Call DisplayReport()
			Case Else
			msgbox "Invalid Select-Please ReEnter A Letter"

		End Select	
	End If
Loop until rc="" or Ucase(rc)="Q"

Housekeeping Details

A few housekeeping routines are required before the main loop is entered:

First, the RecordsetOpen Flag is set to 0, indicating that a recordset is not open.

Second, Input Messages are loaded into variables in the LoadMessages subroutine:


Private Sub LoadMessages()

InputMessage="Please enter one of the following numbers:" & vbcrlf 
InputMessage=InputMessage & "I-Input Data" & vbcrlf 
InputMessage=InputMessage & "S-Save Recordset" & vbcrlf 
InputMessage=InputMessage & "R-Display Report" & vbcrlf
InputMessage=InputMessage & "Q-Quit" & vbcrlf

EnterDataMsg="Please enter the following required fields separated by commas:" & vbcrlf 
EnterDataMsg=EnterDataMsg & "ExpenseCategory " & vbcrlf 
EnterDataMsg=EnterDataMsg & "ExpenseDescription (REQUIRED)" & vbcrlf 
EnterDataMsg=EnterDataMsg & "PublisherorManufacturer" & vbcrlf 
EnterDataMsg=EnterDataMsg & "Vendor" & vbcrlf
EnterDataMsg=EnterDataMsg & "ExpenseAmount (REQUIRED)" & vbcrlf
EnterDataMsg=EnterDataMsg & "Q-Quit"
End Sub


Third, the OpenNeworSavedRecordset subroutine is performed:

Private Sub OpenNeworSavedRecordset()
Dim fso
set fso=CreateObject("Scripting.FilesystemObject")
FolderName=fso.getParentFolderName(wscript.scriptfullname)
SaveName=FolderName & "\expense.out"
If fso.fileexists(SaveName) then
	OpenSavedData
Else
	
	DefineData
	
End if

Opening an Existing XML File

This subroutine determines whether a recordset exists or it needs to be defined. If a file exists in the directory where the script is located and named "expense.out," the program assumes a valid XML file exists and proceeds to open it with the OpenSavedData subroutine:


Private Sub OpenSavedData()

set objrs=CreateObject("Adodb.Recordset")
objrs.CursorType=adOpenStatic
objrs.CursorLocation=adUseClient
objrs.Open SaveName
If Err.Number=0 then
	RecordsetOpen=1
End if

End Sub

The OpenSavedData subroutine first creates the ADO recordset object. Note the explicit use of adOpenStatic to create a static recordset and adUseClient to create a client-side cursor. These two options are essential to creating either a disconnected recordset or a standalone recordset.

Defining the Recordset

Next the global variable SaveName, previously set in the OpenNeworSavedRecordset subroutine, is used to open the file containing the XML recordset. The RecordsetOpen Flag is then set to 1, indicating that the recordset is now open. If the file "expense.out" does not exist in the directory of the script, it is necessary to define it and then open it. This is accomplished by the DefineData subroutine, which is called from the "Else" branch of the OpenNeworSavedRecordset subroutine:


Private Sub DefineData()

Set objrs = CreateObject("ADODB.Recordset")
objrs.Fields.Append "ExpenseCategory", adVarChar, ExpenseCategoryLength
objrs.Fields.Append "ExpenseDescription", adVarChar, ExpenseDescriptionLength
objrs.Fields.Append "PublisherorManufacturer", adVarChar, PublisherorManufacturerLength
objrs.Fields.Append "Vendor", adVarChar, VendorLength
objrs.Fields.Append "ExpenseAmount", adSingle
objrs.Open
RecordsetOpen=1

End Sub

At this juncture the recordset is defined. An ADO recordset object is first created; then five field objects are appended to the Recordset (ExpenseCategory, ExpenseDescription, PublisherorManufacturer, Vendor, and ExpenseAmount); finally, the recordset is opened and the RecordsetOpen Flag is set to 1.

Note the extensive use of constants to keep the code more readable. Many of these were copied from the adovbs.inc files, which can be found under the "Program Files\Common Files\System\ADO" directory after installing ADO on your machine. Constants are also included for the length of the four fields, which will hold string data: ExpenseCategory, ExpenseDescription, PublisherorManufacturer, and Vendor.

These constants and a few variables are declared at the beginning of the program:


Option Explicit
Const adVarChar = 200
Const adSingle =4
Const adPersistxml=1
Const adUseClient = 3
Const adOpenStatic = 3
Const ExpenseCategoryLength = 25
Const ExpenseDescriptionLength = 70
Const PublisherorManufacturerLength = 40
Const VendorLength = 40
Private objrs
Private RecordsetOpen
Private Savename
Private Foldername
Private InputMessage
Private EnterDataMsg

The ExpenseAmount is expected to be numeric so no length constant is defined.

Revisiting the Main Loop

At this juncture in our program we should be back to the main input loop with an open recordset. The recordset either previously existed in a file or is now newly defined. Our options from here are either to input data by entering an "I," save data by entering an "S," or to display the Excel Report by entering an "R."

Figure 2

Entering Data

Let's start with the "I" or input option to begin entering data. After entering either an "I" (upper or lower case) the following input menu is displayed:

Figure 3


Private Sub InputData()
Dim HoldString
Dim HoldData
If RecordsetOpen <> 1 Then Exit Sub

Do
    HoldData = InputBox(EnterDataMsg, "Enter Data Fields")
    If HoldData <> "" And Ucase(HoldData) <> "Q" Then
        HoldString = Split(HoldData, ",", -1, 1)
        If UBound(HoldString) <> 4 Then
            MsgBox "An incorrect number of fields were entered--Please reenter"
        Else
            
            If IsNumeric(HoldString(4)) Then
                objrs.AddNew

                If HoldString(0) <> "" Then
                    If Len(HoldString(0)) > ExpenseCategoryLength Then
                        objrs("ExpenseCategory") = Left(HoldString(0), ExpenseCategoryLength)
                    Else
                        objrs("ExpenseCategory") = HoldString(0)
                    End If
                Else
                    objrs("ExpenseCategory") = "Book"
                End If

                If Len(HoldString(1)) > ExpenseDescriptionLength Then
                    objrs("ExpenseDescription") = Left(HoldString(1), && ExpenseDescriptionLength)
                Else
                    objrs("ExpenseDescription") = HoldString(1)
                End If
                
                If HoldString(2) <> "" Then
                    If Len(HoldString(2)) > PublisherorManufacturerLength Then
                    objrs("PublisherorManufacturer") = Left(HoldString(2), && PulisherorManufacturerLength)
                    Else
                        objrs("PublisherorManufacturer") = HoldString(2)
                    End If
                Else
                    objrs("PublisherorManufacturer") = "Wrox Press" 
  'Only the best
                End If

                If HoldString(3) <> "" Then
                    If Len(HoldString(3)) > VendorLength Then
                        objrs("Vendor") = Left(HoldString(3), VendorLength)
                    Else
                        objrs("Vendor") = HoldString(3)
                    End If
                Else
                    objrs("Vendor") = "Barnes and Noble"
			  'Great Coffee and Good selection of Wrox Books
                End If
                objrs("ExpenseAmount") = HoldString(4)

                objrs.Update
            Else
                MsgBox "4th Element not numeric"
            End If
        End If
    End If
Loop Until HoldData = "" Or Ucase(HoldData) = "Q"


End Sub


[Please Note: Some of the code displayed on multiple lines reside on a single line. I have included the "&&" to indicate this. Please remove these and join the lines when actually testing the code.] The subroutine simply displays the InputBox function and processes input. Five fields must be entered wth separating commas. For those fields for which the "(REQUIRED)" indicator is not displayed in the Entry Message, a comma will suffice to allow the default value to be substituted. The fields are separated into array elements by the Split Command. The array size is checked by the Ubound function to ensure five fields are entered. The ExpenseAmount field is specifically checked to be numeric. Finally the remaining fields are either sized or a default is substituted. Thus the Input line:

	,VBScript Programmers Reference,,,29.99

would actually be processed like the following line with the substitutions

	Book,VBScript Programmers Reference,Wrox Press,Barnes and Noble,29.99

[Note: Do not enter any single quotes since XML uses them to delimit strings. ]

Figure 4

Saving Data to an XML File

After entering a modest amount of data we may desire to save the data which has been entered. To do this we need to quit the input screen by entering a "Q" followed by the Enter key or hit just the Enter key by itself. The program immediately returns to the main loop, from which we enter an "S." This immediately calls the SaveData routine:


Private Sub SaveData()
Dim fso
set fso = CreateObject("Scripting.FileSystemObject")
FolderName=fso.getParentFolderName(wscript.scriptfullname)
Savename=FolderName & "\expense.out"
If fso.fileexists(Savename) then
	fso.deletefile Savename
End If
objrs.save Savename, adPersistxml
End Sub

The SaveData subroutine is fairly straightforward. If the file exists, it is deleted and the recordset residing in the objrs variable is saved in entirety. Note the constant adPersistxml is used to save the file in the XML format.

Displaying an Excel Report

The only other function that we have not yet performed is to print the Excel report. After the file has been saved, it returns again to the main menu. This time we choose the "R" option. This options calls the DisplayReport subroutine:


Private Sub DisplayReport()
Dim xlApp
Dim xlWorkbook
Dim xlWorksheet
Dim myrange
Dim i
Dim j
Dim SumStr
Dim SelectedColumn
	
	set xlApp = CreateObject("Excel.Application")
	xlApp.Visible = True
	set xlWorkbook = xlApp.WorkBooks.Add
	set xlWorksheet = xlWorkBook.Worksheets.Add
	'msgbox "Excel objects created"
	xlWorksheet.Cells(1,1)="Category"
	xlWorksheet.Cells(1,1).Font.Bold=True
	xlWorksheet.Cells(1,2)="Description"
	xlWorksheet.Cells(1,2).Font.Bold=True
	xlWorksheet.Cells(1,3)="Publisher"
	xlWorksheet.Cells(1,3).Font.Bold=True
	xlWorksheet.Cells(1,4)="Place"
	xlWorksheet.Cells(1,4).Font.Bold=True
	xlWorksheet.Cells(1,5)="Cost"
	xlWorksheet.Cells(1,5).Font.Bold=True
	i=2
	objrs.Sort = "ExpenseCategory ASC"
	If not objrs.bof then objrs.movefirst
	while objrs.eof <> True
        	xlWorksheet.Cells(i,1)=objrs("ExpenseCategory")
        	xlWorksheet.Cells(i,2)=objrs("ExpenseDescription")
        	xlWorksheet.Cells(i,3)=objrs("PublisherorManufacturer")
            xlworksheet.Cells(i, 4)=objrs("Vendor")
            xlworksheet.Cells(i, 5)=objrs("ExpenseAmount")
         	i=i+1
        	objrs.movenext
	Wend
	set myrange=xlWorksheet.UsedRange
	myrange.Columns("A:E").Autofit
	xlWorksheet.Cells(i,1)="Totals "
	xlWorksheet.Cells(i,1).Font.Bold=True
	j=i-1 	
SumStr="=Sum(E2:E" & j & ")"
	xlWorksheet.Cells(i,5).Formula=SumStr
	xlWorksheet.Cells(i,5).Font.Bold=True
      xlWorksheet.Columns("E:E").NumberFormat = "0.00"
	
End Sub


Most of the above code was copied by doing macros in Excel and then pasting the code into my script once the Excel.Application, Workbook, and Worksheet objects had been created. For the first macro, I simply entered text into five adjacent columns and then highlighted and bolded them.

Figure 4

The display has been slightly altered to fit the publishing medium.

Note that setting the objrs.Sort equal to "EXPENSECATEGORY ASC" sets the sort order. The "ASC" is not strictly necessary, but to display the recordset in descending order "DESC" is required. Also note the recordset filter option could have been used to restrict the records selected. For example, to display only those that had the ExpenseCategory of "Books," the statement would look as follows:


  objrs.Filter = "ExpenseCategory='Book'"

Single quotes around Book and double quotes around the entire expression.

The program enters the main loop again while displaying the Excel spreadsheet. The reader is left to edit, print, and save the spreadsheet, if so desired. This completes the VBScript application. The entire source code may be downloaded below.

Download of VBScript 5.1

  1. Click on the following URL: http://www.msdn.microsoft.com/scripting/.
  2. Next, click on the hyperlink for version 2 of Windows Script Host under the heading of MicrosoftB. WindowsB. Script 5.1 Released.
  3. Next, click the hyperlink Download Microsoft Windows Script Host.
  4. Finally click the hyperlink for program ste51en.exe located at the bottom of the page.
  5. Choose to save this program to disk.
  6. Create a directory you can locate, if needed.
  7. Finish the download.
  8. Finally, locate the downloaded file and double-click to install.
  9. The setup program should direct you through the installation.
  10. Depending on the changes, you may be required to reboot.

Download of ADO 2.1

  1. First click on the following URL: http://www.microsoft.com/data/.
  2. Next, choose the hyperlink at the top of the page under the heading: There are ten new 2.12.4202.3 languages available for download.
  3. Under 2.1 releases choose Download 2.12.4203.3(GA).
  4. Select the Install Option English, unless of course you need a different language.
  5. Click the "Go to the Download " button.
  6. Click the "Save Program to Disk" option .
  7. (It's always useful to have these programs around in case you need to reload so create a directory like MDAC 2.1.)
  8. After creating the directory and downloading,
  9. (This could take a while depending on connection speed; it's about 6 megabytes.)
  10. double-click the Executable.
  11. (Windows 95 Users will need to download some additional files to make dcomcfg work on 95. The site should direct you to the correct download)
  12. Depending on the changes, you may be required to reboot.

Script Debugger in VBScript 5.1

When I first encountered VBScript 5.1, I could not get the program to stop execution and display the debugger simply by adding "Stop" statements to portions of my VBScript. After searching a few message boards, I soon discovered the problem. In versions of VBScript below 5.1, the debugger launches immediately upon encountering the "Stop" statement. In the 5.1 version, in addition to adding "Stop" statements, you must explicitly launch the debugger with a command-line option. Simply double-clicking on the VBS program will not cause the debugger to launch.

To launch the debugger on my machine where the path for my executable is the "D:\_vbscript" directory, I enter the following syntax:

d:\_vbscript\expense.vbs //d

The double backslashes are intended.

Download

About the Author

Stephan Onisick is an application developer for Media Services, Inc., a Time Warner company in Birmingham, Alabama. Stephan also teaches Visual Basic courses for the University of Alabama Special Studies Division. He currently holds the Microsoft Solution Developer Certificate and the Microsoft Certified Trainer Certificate for Visual Basic 6 Desktop Applications. Stephan can be reached at onisick@aol.com or onisick@home.com



Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

Most Popular Programming Stories

More for Developers

RSS Feeds