TechnowledgeBase

Part Two: Running the Query.

So we’ve created our ADO connection and opened it successfully. Now what?

First, we’ll need a query to run. This isn’t a tutorial on SQL, so I’ll just give you one… assigning it to a string for easy use.

Dim strSQL
strSQL="select * from customers where CustomerNum=098381"

For those who don’t speak SQL, the simple query above will return a list of customers that have the customer number 098381. Logically, this is PROBABLY a list of just one customer, but don’t get the impression that this will always be the case. Your queries can be as complex as you want and can return as many records as you want them to. For simplicity, we’re just wanting to know the name of the customer with customer number 098381, and this will give it to us without too much fuss.

We’re going to run this query and it will (hopefully) return some value(s) to us. So we’ll need a place to store those values. In VBscript, that place is a RecordSet object. We’ll need to create one just like we did for the connection object in part 1. First, we declare its name, then we create an object to go with the name.

Dim objRS
set objRS=objCN.execute(strSQL)

WHOA (again)! It looks like something funky is going on in that second line! Well… yeah. We create RecordSet objects by running a method that accepts our SQL query as a parameter. Okay, I’ll say that last bit again in English: We already have a database connection named objCN. One of the neat things that a connection object can do is run queries for us. When it runs a query, it gives us a RecordSet object that contains the results. So in those two lines of code above, I:

1. declared a name for a recordset object(objRS) and
2. told the connection to run our query and place the results in an object with that name.

What if the query bombs? Well, there are TWO ways that it can bomb. The first is if your query isn’t valid… you’re accessing a table that doesn’t exist or some similar mistake. In that case, you won’t get a RecordSet object, and later attempts to access that nonexistent object may cause the world to explode. Or maybe just your program to crash. Or friend the err.number is a good place check. If everything went well, then err.number will be equal to zero. If something bad happened, it will be equal to something other than zero, so we use an if statement just like we did in Part 1:

if err.number<>0 then
’something bad happened
else
‘the query ran perfectly
end if


The OTHER way your query could bomb isn’t really bombing at all. You ran a valid query, but it didn’t find any results. Maybe there are no customers with a customer number of 098381. In this case, you DO have a valid RecordSet object… it just happens to be empty. How can we test for that? Recordset objects have a property called “eof”. It’s a Boolean that is TRUE when the Recordset is pointing at the the end of the file. When Recordsets are born, they start off pointing at the beginning of the file… so if this one has just been created and its already pointing to the end… then it must be empty, right? Right. So:

if objRS.eof then
'no records returned
else
'we got some records!
end if

So now we’ve got a recordset object with some data in it. How do you get that data OUT of that object and into some other variables, where we can actually use it. You can access the data by name with the following command:

strCust=objRS("customer")

Here, I am retrieving the “customer” variable and storing it in a string named strCust. Where does that word “customer” come from? Go back and look at the SQL query. There it is… it’s the name of a field in the table… a field that we just happened to return with our query. So, if our SQL query had fetched five or six fields, we could get the corresponding data by putting the names of those fields in lines of code like the one above. So what if your query returned multiple records? The Recordset starts off pointing at the first record, and any data you retrieve will be from that first record. To get to the other records, you have to use functions like:

objRS.MoveNext

This moves to the next record. Put it in a loop and it will go through each record one at a time. Be sure to test for EOF, though, or you might try to read a record AFTER the end of the file, and that will cause the end of the world.

Note: There are a LOT of other functions for moving through recordsets, and there are even other TYPES of RecordSets that allow you to do things like go backwards, sort, or update a set of records. But those aren’t for novices.

Now to finish up.

Any time you Open a connection to something, be it a database, a file, or the hot water in the bathtub, it’s a good idea to close it when you’re done. Our connection object from part one needs to be closed. Also, whenever you create an object, it’s good practice to destroy it when you’re done. Here, we created two objects (a recordset, and a connection), and we opened one connection. Here’s how we clean up after ourselves:

objCN.Close
objRS=nothing
objCN=nothing

Please CLOSE the connection BEFORE you set it equal to nothing…. don’t do it the other way around. Trust me.

So that’s it. We’ve made our connection to a database, retrieved some data, and we were even nice enough to clean up after ourselves. That wasn’t so hard, was it? Please note that just because I didn’t spend a LOT of time on error checking doesn’t mean that it isn’t important. I gave you enough error checking to get started (without blowing up the world), but in reality your checks should be a bit more robust. Having two lines of error checking for one line of actual WORK code is typical.

That’s it for this one. Your homework is to study up on different things you can do with recordsets and brush up on your error checking. Code On!

Popularity: 37% [?]

This entry was posted on Wednesday, June 13th, 2007 at 7:00 am and is filed under VBScript. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.