2008 Winter Scripting Games
Solution to Beginner VBScript Event 9: Pool Party
VBScript solution to Event 9 in the 2008 Winter Scripting Games.
Solutions are also available for Windows PowerShell and Perl.
Event 9 – Pool Party
To successfully complete this event you needed to be able to do a little bit of simple arithmetic, and more importantly you needed to learn how to read from a Microsoft Access database. But really, it wasn’t so much reading from an Access database as reading from an ADODB recordset. Take a look at the script and we’ll explain what we mean:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = pool.mdb"
objRecordSet.Open "SELECT *" & _
" FROM SwimmingPool", _
objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordset.EOF
length = objRecordset.Fields.Item("Length")
width = objRecordset.Fields.Item("Width")
If not(objRecordset.Fields.Item("Slope")) Then
depth = objRecordset.Fields.Item("Depth")
vol = length * width * depth
Else
shallow = objRecordset.Fields.Item("SStart")
deep = objRecordset.Fields.Item("SEnd")
vol = length * width * ((deep + shallow)/2)
End If
Wscript.Echo "Name: " & objRecordset.Fields.Item("Customer")
Wscript.Echo " Volume in Liters: " & vol * 1000
Wscript.Echo
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
The event instructions mention that you don’t need to have Access installed on your computer to complete this event. The reason for this is that it’s possible to read from (and write to) an Access database using a disconnected recordset – in other words by using ADODB – which just happens to be a part of the Windows operating system. And that’s how we’re going to solve this event.
We start by declaring a couple of constants:
Const adOpenStatic = 3 Const adLockOptimistic = 3
We’ll use these constants to open our recordset. But first we need to create two objects:
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
The first (objConnection) references a connection to ADO, while the second (objRecordSet) references the recordset. Now we need to open each of these, starting with the connection:
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = pool.mdb"
To open a connection we call the Open method, passing it a string that specifies the provider and the data source. In this case the provider is the provider for an Access database, and the data source is the name of that database.
Now that we have an open connection to our database (pool.mdb) we can open a recordset that we’ll use to read from that database:
objRecordSet.Open "SELECT *" & _
" FROM SwimmingPool", _
objConnection, adOpenStatic, adLockOptimistic
We open the recordset by calling the Open method, passing it the SQL Select statement that specifies which records we want to open. Let’s take a quick look at the Select statement:
SELECT * FROM SwimmingPool
Here’s we’re retrieving all the records (that’s what the * means – everything) from the SwimmingPool table in our database.
We also pass the Open method the connection we just opened, as well as the constants we defined earlier. (These constants have to do with restricting access to the database while we’re reading from it. Don’t worry too much about this, for the most part you can just plug in these constants whenever you want to connect to a database.)
Now it’s time to start reading the records we just retrieved. We begin by making sure we start with the first record in our recordset:
objRecordSet.MoveFirst
The MoveFirst method does just that. We’re at the first record, but we know we’re going to have to read through all the records, so we set up a Do Until loop to do that:
Do Until objRecordset.EOF
We’re going to continue looping until we’ve reached the end of the recordset, at which point the recordset’s EOF property will be True.
Inside the loop we read the Length and Width fields from the recordset entry and store them in the aptly-named length and width variables:
length = objRecordset.Fields.Item("Length")
width = objRecordset.Fields.Item("Width")
One of the things we needed to do is determine whether each swimming pool is uniform depth or whether there’s a slope to it, with a deep end and a shallow end. We do this by checking the Slope field:
If Not(objRecordset.Fields.Item("Slope")) Then
The Slope field is a Yes/No (True/False) field. First we check to see if there is not a slope (that’s what the Not in the If statement is for). If the Slope field is false, meaning there is no slope, we fall into the If statement and perform these lines of code:
depth = objRecordset.Fields.Item("Depth")
vol = length * width * depth
Because there’s no slope we can retrieve the depth of the pool from the Depth field and assign it to the variable depth. At this point we can calculate the volume of water necessary to fill this pool:
vol = length * width * depth
Remember the volume of water is calculated by multiplying the length x width x depth, which we’ve then assigned to the variable vol.
If the pool has a slope to it, our Slope field will be True so we’ll fall into the Else part of our If statement:
shallow = objRecordset.Fields.Item("SStart")
deep = objRecordset.Fields.Item("SEnd")
vol = length * width * ((deep + shallow)/2)
To calculate the volume of water in a pool with a slope to it we average the depth of the deep end with the depth of the shallow end ((deep end + shallow end)/2). To do that we need to read from the SStart and SEnd fields to retrieve those depths:
shallow = objRecordset.Fields.Item("SStart")
deep = objRecordset.Fields.Item("SEnd")
Now that we have the shallow end and deep end depths we can calculate the volume:
vol = length * width * ((deep + shallow)/2)
All that’s left at this point for the current record is the echo back our results:
Wscript.Echo "Name: " & objRecordset.Fields.Item("Customer")
Wscript.Echo " Volume in Liters: " & vol * 1000
Wscript.Echo
First we echo back the name of the customer (stored in the Customer field), the we echo the volume of water required to fill that customer’s pool. Notice we multiplied the volume by 1000 to get the volume in liters. We then echo a blank line. Before we loop around to read the next record, we have to call the MoveNext method on the recordset to manually move to that next record:
objRecordset.MoveNext
At this point we loop around and start over with the next customer. After we’ve read through the entire database we can close our recordset and our connection, and we’re done:
objRecordSet.Close objConnection.Close
And here are our results:
Name: Jones
Volume in Liters: 2500000
Name: Myer
Volume in Liters: 1500000
Name: Smith
Volume in Liters: 1500000
Name: Kumar
Volume in Liters: 1200000
Name: Chen
Volume in Liters: 1500000
Name: Garcia
Volume in Liters: 1058000
