how-to #10: open a recordset with Active Server Pages

Having opened a connection (see the previous page), if we are intending to read data from or store data in the database, we must first open a recordset. However, there are numerous ways of doing the same thing; the one you choose is often a matter of personal preference.

method 1: the conventional way

Remember that on the previous page we created an Access database called ‘club.mdb’ with one table called ‘members’, and have set up a DSN called ‘ClubMembers’. By one of the methods used in the previous page, we have a connection to it which is stored in an ADODB.Connection object which we have named ‘conConn’. Now assume we want to open the ‘members’ table:

Dim rsMembers
Set rsMembers = Server.CreateObject(“ADODB.Recordset”)
rsMembers.Open “members”, conConn

Note that the first parameter is the table name, but it could equally well be a SQL query or the name of a stored procedure. The second parameter is the connection object.

method 2: the long-winded way

Sometimes the code may be clearer using a less concise method of working. We could do this:

Dim rsMembers
Set rsMembers = Server.CreateObject(“ADODB.Recordset”)
rsMembers.Source = “members”
rsMembers.ActiveConnection = conConn

Alternatively, this would also work:

Dim rsMembers
Set rsMembers = Server.CreateObject(“ADODB.Recordset”)
Set rsMembers.ActiveConnection = conConn
rsMembers.Open “members”

As you see, there are different ways of doing the same thing is why reading other people’s ADO code can be difficult. I suggest you choose a method you feel most comfortable with and use that consistently.

method 3: using a Command object

This uses another feature of ADODB – the Command object. The syntax for opening the recordset would look like this:

Dim rsMembers, comCommand
Set rsMembers = Server.CreateObject(“ADODB.Recordset”)
Set comCommand = ServerCreateObject(ADODB.Command”)
Set comCommand.ActiveConnection = conConn
comCommand.CommandText = “members”
comCommand.CommandType = 2
rsMembers.Open comCommand

What is happening here is that we create a command object first, and assign the connection to it (the command object must have a connection in order to work). Then the CommandText property is set to the table name (or SQL string, etc.), the CommandType property is set to 2, which indicates that the CommandText is a table name, and finally the Open method of the recordset is called with the command object as a parameter. In this case, the recordset simply inherits the connection object from the command object.

Why would you want to do this? The command object is normally used when you don’t need a recordset but you want to manipulate the database in some way – perhaps to delete a table, or create a new one, for example. If you did find that occasionally you needed to open a recordset, you would otherwise have to create a new connection and use that, but the command object’s connection can be used instead.

method 4: using an implicit connection

So far we have explicitly created a connection object and assigned it somehow to the recordset. But you don’t actually need to do this. The following code shows an implicit connection – in this case, we don’t require the connection object at all. ADO assumes that since we want to open a recordset we must require a connection and does it anyway:

rsMembers.ActiveConnection = "DSN=ClubMembers"
rsMembers.Source = "members"

Or, of course:

rsMembers.ActiveConnection = "DSN=ClubMembers"
rsMembers.Open “members”

Here we assign the connection string (any format discussed in the previous page will do, it doesn’t have to be a DSN) and open the recordset. The connection is created automatically. Dreamweaver uses this method when you apply the open recordset behaviour. The disadvantage is that you lose your connection when the recordset closes, which may not be what you want if you need the connection for other things. The advantage is that it involves less code and you don’t have to remember to close the connection object as it is done for you.

Finally, remember to close the recordset and dereference it when you are finished with it:

Set rsMembers = Nothing

All you have to do now is get this to work on your actual server as well as on your development machine. This is shown on the following page.