Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Introduction to ADO Part 2 - Recordsets

By Andy Warren,

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!

Total article views: 16200 | Views in the last 30 days: 5
 
Related Articles
FORUM

VB6.0 and SQL 2005 database ado/recordset not supported

error adding record using Addnew/Update via recordset

FORUM

Cursor

cursor

FORUM

replicating records w/o using a cursor

How do I replicate records w/o using a cursor based on a variable?

FORUM

Setting Cursor Type for Recordset

How do I set the Cursor Type, Cursor Location and other properties for a Recordset Object which is r...

Tags
ado    
performance tuning    
programming    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones