13.1.2- Creating a Recordset

by NT Community Manager.
Last Updated by Jim Minatel.

Public Categorized as 13. Using Recordsets.

Not tagged.

13.1.3- Introducing the ADO Constants >>

Creating a Recordset

That's all very well; now how do we go about creating a recordset? Well, ADO is quite flexible and consequently there's more than one way. If you want to create an explicit Recordset object in your code then you'll need to define a variable and then set it equal to a new Recordset object, like this:

Dim objRS

Set objRS = Server.CreateObject("ADODB.Recordset")

If this looks familiar to you it's because it's very similar to the syntax we used for creating an explicit Connection object in our code, in the previous chapter :

Dim objConn

Set objConn = Server.CreateObject("ADODB.Connection")

Once again, the two shaded lines above are enough to create a Recordset object; but the object is not connected to a database and it doesn't yet contain any data! Let's see how to put some data into the Recordset; and then we'll exercise our knowledge in an example.

Recordsets and Connections

It's clear from the previous chapter that the connection is fundamental part of the practice of passing information between our ASP page and our data store. The connection is the route by which the information is transferred from one to the other. If there's no connection, then there's no way for the data to be transported.

So, when we're trying to get data from the data store into a Recordset object (or vice versa), we need to consider the connection too. However, the necessity for a connection doesn't imply that we need to create an explicit ADO Connection object in our code.

Using an Explicit Connection Object

The most obvious way to get some data from a data store is to explicitly create an ADO Connection object in our code, and then use the Connection object's properties when working with the Recordset:

Dim strConn, objConn, objRS

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Movie2000.mdb"

Set objConn = Server.CreateObject("ADODB.Connection") ' Connection object

objConn.Open strConn

Set objRS = Server.CreateObject("ADODB.Recordset") ' Recordset object

objRS.Open "Movies", objConn ' use objConn to supply connection info

This is the technique we used in the Connect.asp example in Chapter 12 . What's the advantage of doing it this way? Well, connecting to a data store is quite 'expensive' – it's a task that takes a relatively long time to complete. So if you need to use several recordsets in your code, or if you're going to use one recordset to make a number of queries, it is best to only connect to the data store once. In this case, create an explicit Connection object and use it whenever you need to use the data store connection.

Avoiding an Explicit Connection Object

So, several Recordset (or Command) objects can share an explicitly created Connection object. This is a good reason to do it this way. However, it's not always the most efficient or convenient way to work. Instead, we can make good use of one of the distinct advantages of ADO: namely, that the ADO objects enjoy a flat hierarchy, in which the interdependence of objects within our code is hidden in order to make our code simpler. In this example, we can open the Recordset object directly – we don't need to create an explicit Connection object:

Dim objRS, strConnect

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Movie2000.mdb;"

Set objRS = Server.CreateObject("ADODB.Recordset") ' Recordset object

objRS.Open "Movies", strConnect ' use strConnect to supply connection info

What's happening here? Well, we've explicitly created a Recordset object in our code, and we've used its Open method to forge a connection between our code and the data store. This action is enough to create a physical connection, and to use objRS in the same way – yet there is no Connection object in sight!

In fact, there is an ADO Connection object – but ADO creates it under the covers, and (at the moment) we can't see it in our code. That's great – ADO creates the extra objects we need, so we don't need to worry about any kind of 'object hierarchy'. It keeps the logic simple and our code tidy.

If we never need to use the underlying Connection object in our code, this is perfect – it's a very tidy way of letting ADO deal with it for us. And if we decide that we would like to use it, we can give it a name in our code by referencing it via the Recordset object's ActiveConnection property:

Dim objRS, strConnect

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Movie2000.mdb;"

Set objRS = Server.CreateObject("ADODB.Recordset") ' Recordset object

objRS.Open tablename, strConnect ' use strConnect to supply connection info

...

Dim objConn

objConn = objRS.ActiveConnection

Once we've done this, we can treat objConn just as if we had created it explicitly using the Set command.

This notion of a 'flat' hierarchy extends to the other ADO objects too. We can specify connection details directly to a Recordset object (cutting out the need for an explicit Connection object); equally, you'll find that it's possible to specify the details of a SQL command directly to a Connection or Recordset object (cutting out the need for an explicit Command object).

This just gives us greater flexibility in our code. The interdependence between objects (like the relationship shown here between the Connection and Recordset objects) means that they can be handled explicitly in the code, if that's appropriate, or left for ADO to handle under the covers.

Try It Out – Creating a Recordset

So, let's use this to create a Recordset object and populate it with data from our Movies database – which we'll then show on screen. We've done it before, but this time we'll do it without an explicit Connection object in our code.

1. We'll use the connection details that you used in Chapter 12 , so make sure you have the DataStore.asp SSI file, from Chapter 12, saved into your \Inetpub\wwwroot\BegASP directory. You'll also need the Movies.mdb or Movies.mdf data files set up, as described in Chapter 12.

2. Create a new file, called Recordset.asp, and put the following lines into it:

Option Explicit

Dim strConnect

<HTML>

<HEAD>

<TITLE>ADO Recordset Object</TITLE>

</HEAD>

<BODY>

Const adOpenForwardOnly = 0

Const adLockReadOnly = 1

Const adCmdTable = 2

Dim objRS ' recordset object

Set objRS = Server.CreateObject ("ADODB.Recordset") ' create recordset object

objRS.Open "Movies", strConnect, _

adOpenForwardOnly, adLockReadOnly, adCmdTable ' now open it

While Not objRS.EOF ' now loop through the records

Response.Write objRS.Fields("Title") & ", "

objRS.MoveNext

Wend

objRS.Close ' now close it

Set objRS = Nothing ' ...and clean up

</BODY>

</HTML>

3. Save the Recordset.asp file into your \inetpub\wwwroot\BegASPFiles folder.

4. Load the page into your browser.

Chapter13_image003

And voila—a list of all the titles of every name in the table. We did pretty much the same in the last chapter, with the help of the Connection object, but there are some important differences in the way it has been achieved here. Let's see how it works.

How It Works

The first few lines of code are familiar to us – we used them in most of the examples in Chapter 12 . The Option Explicit command demands that we define all our variable names, and we follow that by including the DataStore.asp SSI that contains our connection string definition:

Option Explicit

Dim strConnect

<HTML>

<HEAD>

<TITLE>ADO Recordset Object</TITLE>

</HEAD>

<BODY>

Next, we define three constants, which we'll use in a moment with our Recordset object:

Const adOpenForwardOnly = 0

Const adLockReadOnly = 1

Const adCmdTable = 2

As you'll see shortly, we'll pass these constant values to the Recordset object when we open it – they tell the Recordset object about the type of recordset we want ADO to create for us. Don't worry about the different types of recordset available for now – all will be explained later in the chapter.

The next thing to do is declare our object variable, objRS:

Dim objRS ' recordset object

Now we can create the Recordset object itself. We use the Server.CreateObject method for this task, and we specify the ADO Recordset object's ProgID, which is ADODB.Recordset:

Set objRS = Server.CreateObject ("ADODB.Recordset") ' create recordset object

Once the object is created, we can open it. For that, we use the Open method of objRS:

objRS.Open "Movies", strConnect, _

adOpenForwardOnly, adLockReadOnly, adCmdTable ' now open it

The Recordset object's Open method has five parameters, and as you can see, we have specified a value for each of them here:

The first parameter is the source of the data. In this case it's the Movies table from our database.
The second parameter is the connection information. We need to tell the recordset that we want a connection to the Movie2000.mdf or Movie2000.mdb database. But we don't need to pass a Connection object – instead, we can just pass the connection string and ADO will set up the Connection object under the covers. So here, we've passed the connection string, strConnect

The last three parameters use the constants that we defined earlier on in the page:

The third and fourth parameters tell ADO what type of recordset to use – they define two characteristics of the required recordset, called the cursor type and the lock type. We'll be looking at these recordset characteristics later in the chapter.
The fifth parameter states that Movies is a database table.
The recordset is now open, and requested data has been copied from the database into our recordset. Now, we're in a position to examine the contents of the recordset – and to do that, we'll simply step through each record and write the contents of its Title field to the browser.

In order to step through the recordset, we use the cursor. Happily, when we open the recordset, ADO points the cursor at the first record. So in order to examine each record, we just need to create a loop in which we:

Examine the current record (i.e. the record to which the cursor is currently pointing); then
Move the cursor to the next record in the recordset

Starting at the first record, we'll just do that repeatedly until we run out of records. How do we know when we've run out of records? The Recordset object has a property called EOF ('end-of-file'), which is a Boolean value. It is True when the cursor runs past the last record in the recordset, and it's False otherwise. So, while we're stepping through the recordset, the value of EOF is False; if we step past the last record then EOF will be set to True. Hence, we repeat the loop while EOF is Not True.