In part 1 of this series, we had an introduction to ADO. We learned about the different methods of making a connection, and how to manage the connection object. Now, continuing along the path of enlightenment, we will learn how to extract data from a database using recordsets.
Written by Rich Smith Rating: / 45 August 27, 2003
What is a recordset? An ADO recordset object is used to hold a set of records that belong to a database table or tables. Like any relational database, the recordset object consists of records and columns. In the world of ADO, this object is one of the most important and is the most commonly used.
In essence, a recordset is a collection of records from your database. If you are looking to read data using ADO, a recordset object is what you use. After making the proper connection to the database, you create a recordset object and specify the data you would like the retrieve. You can opt to retrieve all the records in a table, in multiple tables, or even a small subset of records inside a table.
In addition to controlling the records that are to be retrieved, you can also specify precisely which columns to return as well. For example, if your table has 15 columns, but you only want to see the first two columns, it is much more efficient to only bring back the 2 columns, especially when working over a network.
Now that we know how to create and destroy a recordset object, lets learn how to fetch some data.
Lets start simple. For our example, let’s assume that we have a database on the machine, with a DSN called “MyDB”. In this database, we have a table named “Customers”. This table contains 5 columns, named “CustomerID”, “FirstName”, “LastName”, “PhoneNumber”, and “Status”.
When creating a recordset, one of the parameters in the Open method determines the data that you wish to retrieve. If you want to return all of the records in a table, then you specify only the table name.
The following example will create a recordset containing all of the records in the Customers table:
<% ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
Specifying a tablename in your open method is not the only way to specify data for your recordset. You can also use SQL to determine the precise data you wish to retrieve. Using SQL, you can specify the exact rows and columns to be returned.
Here is an example how to accomplish the same results with SQL, as we did in our last example by specifying a table name:
<% ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “select * from Customers”, conn
‘ Logic to manipulate data here
rs.close set rs = nothing
conn.close set conn = nothing %>
You will notice that not a lot changed between the two examples. But if we make a small change to the SQL statement, the data returned can vary greatly. For example:
rs
.open “select * from Customers where status = ‘Active’”, conn
You will notice in the above line I added a WHERE condition to the SQL statement. This specifies to ADO exactly which records should be placed into the recordset. This article is not going to go into the intricacies of SQL, and will assume that you are familiar with at least the very basics of SQL.
Well, now we have created a recordset object, and opened it. But how do we know if we actually found any data and if so, how much?
The recordset object has many properties. One of the most useful of these properties is the recordcount. By checking this property, you know if you actually found data with your search. For example:
<% ConnectionString = "DSN=MyDB”
set conn = server.createobject("adodb.connection") conn.open ConnectionString
set rs = Server.CreateObject("ADODB.recordset") rs.open “Customers”, conn
if rs.recordcount >= 0 then response.write “There are “ & rs.recordcount & “rows” response.write “that match your search” else response.write “Error with recordset” end if
rs.close set rs = nothing
conn.close set conn = nothing %>
As you can see in the above example, a return of –1 constitutes some sort of error. This can be triggered if you try to check a recordcount without first opening the recordset, for example.
We are getting closer to the end of the trail. We can now open recordsets and know if we have data in them. But how do we get at it?
The data in the fields is available by asking for it by name. For example, here is a piece of code to read the first and last names from the recordset:
set rs
= Server.CreateObject("ADODB.recordset") rs.open “select * from Customers”, conn response.write “The customer’s name is “ & rs(“FirstName”) & _ “ “ & rs(“LastName”) rs.close set rs = nothing
You can also access the data by utilizing the “fields” collection of the recordset object. This collection contains all of the columns that are in the recordset. Consider this example:
set rs
= Server.CreateObject("ADODB.recordset") rs.open “select * from Customers”, conn for each x in rs.Fields Response.Write(x.name) Response.Write(" = ") Response.Write(x.value & " ") next rs.close set rs = nothing
This example would go through all of the columns in the recordset and display the fieldname and the value.
Now it’s time for the fun part. We’ve got a recordset containing multiple rows. How do we show all of the data?
Well, we are able to move through the recordset using different methods. The most important methods for this are “MoveFirst”, “MoveLast”, “MoveNext”, and “MovePrevious”. The meanings of these methods are self describing.
But when moving through a recordset you must know when to stop. This is done for the most part by checking two properties. These are EOF (End of records) and BOF (Beginning of records).
Here is an example:
set rs
= Server.CreateObject("ADODB.recordset") rs.open “select * from Customers”, conn do until rs.EOF response.write “The customer’s name is “ & _ rs(“FirstName”) & “ “ & rs(“LastName”) rs.MoveNext loop rs.close set rs = nothing
There are many more properties and methods for the recordset object that allow you to many things. This article is aimed at only the basics. To learn to do things such as convert the recordset to an array, or to move the recordset pointer to any position within the recordset, I would suggest reading a tutorial more focused on the details of the subject.
We’ve now come to the point where we are able to create a database connection, and read data from that connection. In most ASP applications, this will account for more than 80% of the database interaction.
In the next article in this series, we will visit another very important operation when it comes to database interaction; the process of adding information into the database.
OVERVIEW of Creatable Recordsets
