|
Introduction
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 by clicking on the following link: <<<Program Link to be added>>>
Download of VBScript 5.1
- Click on the following URL: http://www.msdn.microsoft.com/scripting/.
- Next, click on the hyperlink for version 2 of Windows Script Host under the heading of Microsoft® Windows® Script 5.1 Released.
- Next, click the hyperlink Download Microsoft Windows Script Host.
- Finally click the hyperlink for program ste51en.exe located at the bottom of the page.
- Choose to save this program to disk.
- Create a directory you can locate, if needed.
- Finish the download.
- Finally, locate the downloaded file and double-click to install.
- The setup program should direct you through the installation.
- Depending on the changes, you may be required to reboot.
Download of ADO 2.1
- First click on the following URL: http://www.microsoft.com/data/.
- 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.
- Under 2.1 releases choose Download 2.12.4203.3(GA).
- Select the Install Option English, unless of course you need a different language.
- Click the "Go to the Download " button.
- Click the "Save Program to Disk" option .
- (It’s always useful to have these programs around in case you need to reload so create a directory like MDAC
2.1.)
- After creating the directory and downloading,
- (This could take a while depending on connection speed; it’s about 6 megabytes.)
- double-click the Executable.
- (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)
- 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
You can download the complete source for the sample contained in this article:
http://15seconds.com/files/000323.zip
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
|
