Introduction to ActiveX Data Objects
If you haven't read my "ASP Chain" article on this web site, you really need to, before you read this one. It gives the background for ADO in the context of ASP web pages. If you have, perhaps you recall the illustration below:
We're going to be concentrating in this discussion on steps 4 and 5, which involve the use of COM objects, probably the single most powerful aspect of ASP that I can think of. That's because "ActiveX" and "COM" are synonymous terms. Why are there 2 terms for one technology? Ask Microsoft!
In any case ActiveX Data Objects (ADO) are COM (Component Object Model) objects, and this is what you employ to gain access to a database through an ASP page. Note: This article, regardless of its' length, is only intended to be an introduction to ADO, and is hardly comprehensive!
The Big Three
There are 3 top-level ADO objects that you need to be concerned with: The Command, Connection, and RecordSet objects.
Now, if you know nothing about Object Oriented Programming (OOP), you may get a little lost here, and I would suggest that you do some study on the subject, because, like it or not, ASP is a programming technology, and you're going to have to do some programming to deal with it! In many ways, OOP is easier to deal with than traditional Procedural Programming, and you don't have to know how it works down to the lowest level to use COM objects. In fact, if you understand what Objects, Properties, Events, and Methods are, you're probably in pretty good shape. Briefly, an Object is pretty much what it sounds like. Take a Window, for example. A Window is an object. It has properties, such as the property of being maximized, minimized, or sized. It has methods, such as the Open and Close method. And events can occur, which will affect the Window object, such as a mouse-click on the minus sign in the upper right-hand corner of the Window, which minimizes the Window. A procedure that determines what to do when an event occurs is called an Event Handler.
Okay, now read and study the paragraph above until you understand each concept fully. Congratulations. You're a programmer! ;-) Just kidding. Now, where was I? Oh yes. Command, Connection, and RecordSet.
One of the coolest things about ADO is that when you use one of these objects, the other 2 are created automatically! So, why have 3 to start with? Well, because each object has different properties, events, and methods, and from time to time you may want to do something with some of them. So, which object you invoke, how you use it, etc., are more or less dictated by what your needs are.
So, let's start with a brief description of each object:
Command Object
When you do something with a Database, you must send a Command to that database. The Command Object is a sort of "container" for a command. Since a command can take many forms, the Command Object is designed to accommodate those many forms, and enable you to work with the elements of the Command easily. In most cases, your command is going to be a SQL Statement, but it may contain parameters to be passed to a Stored Procedure, or other similar qualities. But most of the time, you won't need to specifically invoke the Command Object.
Connection Object
The Connection Object is the link between your ASP application and the database itself. The link must be opened initially, it must be closed when you're done, and it may have varying qualities, according to your needs. These qualities are the properties of the Connection Object. And there are a few methods as well. But there are also default properties for a Connection Object, and 99% of the time, these default properties will serve you best. So again, most of the time, you won't specifically need to invoke the Connection Object.
RecordSet Object
This is the object you're going to be doing almost all of your work with, and therefore, it is the object which I am going to concentrate on in this article. A RecordSet object is a container for what is called a Cursor. A cursor is a temporary table, which is constructed by performing a query on a table in a database. It doesn't exist in a file; it exists in memory. but other than that, it has all the characteristics of a database table. It has rows (records) and columns (fields), and the rows and columns have properties of their own. It has a Record Pointer (which is also, confusingly enough, referred to as a cursor!), which moves from one row or record to another. And it has a host of other properties as well, which we shall explain.
Illustration of a RecordSet Object
But first, I'd like to explain how you can use the invocation of a RecordSet to implicitly create your Command and Connection Objects. If you've seen my ASP/ADO tutorials, you're probably familiar with the following line of code:
rs.Open q, [Connection String]
"q" in this case refers to a SQL Query string, such as "SELECT * FROM mytable," which has been previously defined somewhere. [Connection String] is a Connection string. A Connection String is simply text that contains information about how to connect to a database. The Query string is the Query string (text) of the Command Object, and the Connection string is the Connection string of the Connection Object. Both objects are created implicitly when you use the Open method of the RecordSet, and by passing those arguments to the Open method, you set those properties of the 2 other objects. In most cases, this is all you need to to with these other 2 objects. Cool, huh? Since usually, all of your work is going to be with the records in the RecordSet, the RecordSet object is the only one you have to create.
But let's get a bit more intimate with the RecordSet Object, shall we? after all, we're going to be spending a lot of time together.
RecordSet Properties
The following is a list of the properties available for a RecordSet, with a brief description of each:
| AbsolutePage | Page of current position |
| AbsolutePosition | Current position |
| ActiveConnection | Active Connection Object |
| BOF | Beginning of File |
| Bookmark | Bookmark of current position |
| CacheSize | Number of records cached |
| CursorLocation | Server or Client |
| CursorType | Forward, static, dynamic, keyset |
| EOF | End of File |
| EditMode | Whether or not an edit is in progress |
| Filter | What kind of records to hide |
| LockType | Record lock type for edits or updates |
| MaxRecords | Maximum number of records to retrieve |
| PageCount | Total number of pages |
| PageSize | Number of records per page |
| RecordCount | Total number of records |
| Source | Source Command Object |
| Status | Status of last action |
You may never encounter some of these properties, as usually, the default settings for the Read/Write properties are fine, and in fact, there are some properties listed here which are not supported by various providers. For example, take the CursorType property. By default, this is "forward only." Unless you work with the same provider all the time, you'd probably be best off leaving it "forward only," as dynamic RecordSets are not supported by all providers. I learned this from painful experience!
The most common properties that I personally use are EOF (You can test for this to find out if any records have been returned - if you run a query and the pointer is at EOF, no records were returned) and MaxRecords. But MaxRecords isn't supported by all providers either! So watch it!
RecordSet Methods
The following is a list of the methods available for the RecordSet object, with a brief description of each:
| AddNew | Add a new record to the RecordSet |
| CancelBatch | Cancel pending batch updates |
| CancelUpdate | Cancel pending update |
| Clone | Copy the RecordSet |
| Close | Close the RecordSet |
| Delete | Delete current record |
| GetRows | Retrieve multiple records |
| Move | Move the record pointer to a specific record |
| MoveNext | Move the record pointer to the next record |
| MovePrevious | Move the record pointer to the previous record |
| MoveFirst | Move the record pointer to the first record |
| MoveLast | move the record pointer to the last record |
| NextRecordSet | Load the next RecordSet in a multi-set query |
| Open | Open the RecordSet (execute the query) |
| Requery | Re-execute the last query executed |
| Resync | Synchronize the data with the server |
| Supports | Determine if a feature is supported by provider |
| Update | Update the current record |
| UpdateBatch | Update pending batched record updates |
The methods I use most commonly are listed below, with a detailed description of each:
Open
<RecordSetName>.Open source, connection, cursor, lock, type
| source | Can be a Command Object or a string containing a query. This parameter is optional. If the RecordSet's "Source" property is set, it is unnecessary. |
| connection | Can be a Connection Object or a string containing Connection information (also known as a "Connection String" separated by semicolons. This parameter is optional. If the RecordSet's "ActiveConnection" property has been set, it is unnecessary. |
| cursor | A constant numeric value used to assign the RecordSet's CursorType property. This parameter is optional. By default, the CursorType is Forward Only, and if the RecordSet's CursorType has been explicitly set elsewhere, it is also unnecessary. |
| lock | A constant numeric value used to assign the LockType property. this parameter is optional. The default LockType property is Read Only, and if the RecordSet's LockType property has been explicitly set elsewhere, it is also unnecessary. |
| type | A constant numeric value used to assign the CommandType property of the Command object. This parameter is optional. The default value is Unknown (8), and if the Command Object's CommandType property has been set explicitly elsewhere, it is also unnecessary. This value can be helpful in optimizing your code, to give a hint as to what the CommandType will be. |
A note about using this method: I use this method even when executing an UPDATE, DELETE or INSERT SQL statement. Even though these statements don't return a RecordSet, because of the fact that the Open method executes the query you specify, it will execute these statements just as well. That's just part of my personal KISS philosophy!
MoveNext
<RecordSetName>.MoveNext
This method simply moves the record pointer (or cursor) to the next record in the RecordSet. It is often used in loops which display all the records returned in the current RecordSet. If you attempt to use this method after the record pointer has moved beyond the last record, an error will be generated. Therefore, it is always a good idea to test for EOF before using this method.
Close
<RecordSetName>.Close
This method takes no parameters. It is important to explicitly close the RecordSet, even though there are built-in timeouts which will close the RecordSet without your specific invocation of the method. Closing the RecordSet explicitly speeds up the process, and eases the burden on the Server. When the RecordSet is closed, the Connection Object is also closed, as well as the Command Object.
Supports
value = <RecordSetName>.Supports( option )
The value returned by this method will be either TRUE or FALSE. the method will test whether the current provider supports a variety of options.
This method can test for support of the following options (These options are expressed as integers):
| adAddNew (16778240) | Supports the AddNew method |
| adApproxPosition (16384) | Supports positioning based on ratios. Used to provide Absolute Positioning. AbsolutePage and AbsolutePosition properties depend upon it. |
| adBookMark (8192) | Supports use of bookmarks for navigation |
| adDelete (16779264) | Supports the Delete method |
| adHoldRecords (256) | Supports RecordSet holding multiple discrete blocks of records. Allows you to read more records without committing pending changes. |
| adMovePrevious (512) | Supports the MovePrevious method |
| adReSync (131072) | Supports the ReSync method |
| adUpdate (16809984) | Supports the Update method |
| adUpdateBatch (65536) | Supports the UpdateBatch method |
Requery
<RecordSetName>.Requery
This method can be used to re-execute the same query initially used to open the RecordSet, without having to close and reopen the RecordSet. It refreshes the RecordSet, which keeps the same properties and parameters used when it was initially opened.
Conclusion
As I mentioned before, this is hardly a complete description of ADO, or even of what you can do with a RecordSet object. But it should be a good introduction, and help you to know what the most important things to know are. If you can just grasp the basic principles outlined here, you will be able to do a lot with ADO. However, I do recommend getting a good reference on the subject, such as "Beginning ASP Databases," my personal recommendation for the best introductory ASP/ADO book available.
I wrote this article as a companion for the tutorials which I have posted on this web site. I have discovered that many people out there who are struggling with ASP/ADO are reading (and attempting to follow) my tutorials without really understanding the underlying principles, which makes it far more difficult. I hope that this will serve to provide some foundation for working with the tutorials.
Good Luck!