SQLServerCentral Article

Introduction to ADO Part 2 - Recordsets

,

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!

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating