Last week I posted an article that covered the basics of the main object in
ADO, the connection object. This week I'm going to continue by discussing how to
use the second most used object in ADO, the recordset.
The first thing we need to talk about is when do you use a recordset versus a
connection object? You'll use a recordset when you need to return records to the
client - any time you need the results of a select statement. You use a
connection object (or the command object as we'll cover next week) to execute
'action' queries. Where it may get a little confusing is that for a recordset to
work, it has to have a connection! There are two different ways to do this, the
first builds on what we did last week, instantiating a connection object first:
| dim cn as adodb.connection dim rs as adodb.recordset set cn = new adodb.connection cn.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp" set rs = new adodb.recordset rs.open "Select * from customers order by companyname", cn |
As you can see, our first step is to get the connection object. Next we
instantiate the recordset object, then 'open' it by passing a sql select
statement as the first parameter and an open connection object as the second
parameter. This is the most common way, since normally you will reuse a
connection object several times before closing it.
If you just need a recordset, you can use this abbreviated method:
| dim rs as adodb.recordset rs.open "Select * from customers order by companyname", "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=eg;Application Name=TestApp" |
Did I mention there are different types of recordsets? Each recordset has a
cursor type (static, dynamic, forward only, or keyset). Sounds like our friend
the T-SQL cursor! If you understand the good and bad of T-SQL cursors, you're
well on your way to deciding which cursor type to use in a recordset. We'll talk
about this some more in a minute. While you're deciding on cursor type, you also
have to think about the lock type - again, you get four options: optimistic,
batch optimistic, pessimistic, and read only. And finally, we have to backtrack
just a bit to consider one final, critical option - the cursor location. For
each connection or recordset object, you have the option of setting a 'server'
cursor or a 'client side' cursor. Here are some captures right from VB showing
the options and how they work in the development environment:
I know that's a lot to put in one paragraph. We're not going to cover all the
possible variations, I just want you to know they are there. I'm going to offer
two configurations to get you started, then you can experiment and do some
additional reading as your skill grows.
The first one is that you JUST need to read the data, not make any changes to
it. Maybe to add a list of users to a listbox, or display a list of order
details in a web page. You should use a cursor type of forward only, a lock type
of read only, and a location of server. This will give you the fastest results
with the least amount of locking. This is also known as the 'fire hose' cursor
since the server just streams the data to you (the recordset) as quickly as it
can, then it's done. This is such a common thing that it is actually the
default. If you create a recordset and omit the cursor type and cursor location
parameters, you get a forward only read only recordset.
| rs.CursorLocation = adUseServer rs.Open "Select * from customers order by companyname", cn, adOpenForwardOnly, adLockReadOnly |
In the second scenario, you need to update the data. For this, I recommend
you use a cursor type of static, a lock type of optimistic, and a cursor
location of client. Using these options, all the data matching your query will
be pulled over to the client. No locks will be held on the rows you selected and
there is no guarantee that someone else will not change them while you're
working. This gives you the freedom to browse the recordset, sort it on the
client, etc, and place NO load on the server.
| rs.CursorLocation = adUseClient rs.Open "Select * from customers order by companyname", cn, adOpenStatic, adLockOptimistic |
Now that you know enough to decide how to configure your recordset, let's
talk about how to use it. Here is a sample that shows iterating through a
recordset and adding all the items to a listbox:
| Do Until rs.EOF List1.AddItem rs.Fields("CompanyName") rs.MoveNext Loop |
Recordsets have two very important properties, BOF (beginning of file) and
EOF (end of file). When you first open a recordset that has one or more records,
the BOF property will be true, the EOF property will be false. Once you 'movenext'
after looking at the last record, the EOF property will be true. It is absolutely
critical that you always check for BOF and EOF. Failure to do so can
result in the following error which your users will NOT appreciate:
Here is another example, this time I'm concatenating two values and adding
both to the listbox:
| Do Until rs.EOF List1.AddItem rs.Fields("CompanyName") & "/" & rs.Fields("ContactName") rs.MoveNext Loop |
There is also an alternate syntax you can use for addressing field names
which uses the 'bang' operator:
| Do Until rs.EOF List1.AddItem rs!CompanyName rs.MoveNext Loop |
And finally, you can address field names by using the ordinal
position. This is probably the fastest way to address a field, but also the most
dangerous and least understandable. Any change in your select statement will
cause you a LOT of problems if you don't adjust your ordinals. I'm including
this because you may see it in code samples, but please do not use this method!
| Do Until rs.EOF List1.AddItem rs.Fields(0) rs.MoveNext Loop |
So far we've just been displaying values, now let's look at how to edit and
add records. Here is how we would add a record:
| Do Until rs.EOF rs.AddNew rs.Fields("CustomerID") = "SSC" rs.Fields("CompanyName") = "SQLServerCentral.Com" rs.update Loop |
Use the AddNew method sets up a blank record. You then assign values to as
many fields as you need. To save it, you can either do so explicitly by using
the .Update statement, or by executing any .Movexxx operation. If you just want
to add records, here is one trick you'll use a lot - in your select statement,
add this as a where clause "where 1=0". No records will match, so very
little traffic will be generated, but you still get the data structure that
allows you to add records.
Finally when it's time to edit records, it works almost the same way:
| Do Until rs.EOF rs.Fields("Country") = "USA" rs.MoveNext Loop |
Notice that there no .Edit statement. It's not required in ADO, a change from
how recordsets worked in DAO. Once you're done with your recordset, always do a
good clean up:
| rs.Close Set rs = Nothing |
Once you use this a couple times, it's comfortable and easy to use.
Recordsets have a ton of features to make your life easier, we'll talk about
some of those in the upcoming months. For now, give ADO a try! As always, I'd
appreciate any questions or comments you have on this article. Just click the
'Your Opinion' tab below and start typing!
