The ADO Recordset Object is a crucial component of ActiveX Data Objects (ADO), used for working with data in ASP (Active Server Pages). This object provides a way to manage data retrieved from a database, allowing developers to navigate, manipulate, and manage data in an efficient manner. In this article, we will explore the properties, methods, and events of the Recordset object, along with practical examples to help you understand its usage better.
Recordset Properties
The ADO Recordset Object has several properties that allow developers to interact with and manage the data. Below are the key properties:
Property | Description |
---|---|
ActiveConnection | Represents the connection to the database associated with the Recordset. |
EOF | Indicates whether the current position is after the last record. |
BOF | Indicates whether the current position is before the first record. |
RecordCount | Returns the number of records in the Recordset. |
State | Indicates the state of the Recordset (open, closed, etc.). |
Fields | Provides access to the fields in the current record of the Recordset. |
CursorType | Defines the type of cursor used by the Recordset (e.g., static, dynamic). |
LockType | Specifies the locking mechanism for the data (e.g., optimistic, pessimistic). |
ActiveConnection
The ActiveConnection property refers to the connection object that the Recordset is currently using. To create a Recordset, you need to establish a connection to the database first.
Dim conn, rs
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUsername;Password=YourPassword;"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = conn
EOF and BOF
The EOF (End of File) and BOF (Beginning of File) properties are used to determine the position within the Recordset. If EOF is True, the cursor is past the last record; if BOF is True, the cursor is before the first record.
If rs.EOF Then
Response.Write "End of Records"
End If
If rs.BOF Then
Response.Write "Start of Records"
End If
RecordCount
The RecordCount property returns the total number of records in the Recordset. Note that this property can be -1 if cursor type does not support record counting.
Response.Write "Number of Records: " & rs.RecordCount
State
The State property tells whether the Recordset is open or closed. A state of 0 indicates that it is closed, while 1 means it is open.
If rs.State = 1 Then
Response.Write "Recordset is open."
Else
Response.Write "Recordset is closed."
End If
Fields
The Fields property allows access to the data of the current record in the Recordset. You can reference fields by name or index.
Response.Write "First Name: " & rs.Fields("FirstName").Value
Response.Write "Last Name: " & rs.Fields(1).Value ' Using index
CursorType
CursorType defines the type of cursor you can use to navigate records. Here are common types:
CursorType | Description |
---|---|
0 – adOpenUnknown | Cursor type is not specified. |
1 – adOpenForwardOnly | Only allows movement forward. |
2 – adOpenKeyset | Allows navigating to specific records and reflects any changes made by others. |
3 – adOpenStatic | Static set of records; does not reflect changes made by others. |
4 – adOpenDynamic | Cursor that reflects changes made by others. |
LockType
The LockType property determines how the Recordset is locked when being edited, which can be important for data integrity. Common lock types include:
LockType | Description |
---|---|
0 – adLockUnknown | Lock type is not specified. |
1 – adLockReadOnly | Data is read-only. |
2 – adLockPessimistic | Locks records when editing, preventing others from accessing them. |
3 – adLockOptimistic | Records are only locked when updates are committed. |
Recordset Methods
Methods in the Recordset object are used to perform various actions on the records.
Method | Description |
---|---|
AddNew | Adds a new record to the Recordset. |
CancelUpdate | Undoes changes made to the current record. |
Close | Closes the Recordset. |
Delete | Deletes the current record from the Recordset. |
Find | Locates a specific record in the Recordset. |
Move | Moves the cursor to a specific record. |
MoveFirst | Moves the cursor to the first record. |
MoveLast | Moves the cursor to the last record. |
MoveNext | Moves the cursor to the next record. |
MovePrevious | Moves the cursor to the previous record. |
Open | Opens the Recordset with a specified SQL query. |
Requery | Refreshes the Recordset data from the database. |
Update | Commits changes made to the current record. |
AddNew
The AddNew method enables you to create a new record in the Recordset. Here’s how to do it:
rs.AddNew
rs.Fields("FirstName").Value = "John"
rs.Fields("LastName").Value = "Doe"
rs.Update
CancelUpdate
To undo changes made since the last call to Update, you can use the CancelUpdate method.
rs.Fields("FirstName").Value = "Jane"
rs.CancelUpdate
Close
Closing the Recordset is essential to free up resources:
rs.Close
Set rs = Nothing
Delete
The Delete method removes the current record from the Recordset:
rs.Delete
rs.Update
Find
You can locate a specific record using the Find method:
rs.Find "FirstName = 'John'"
If Not rs.EOF Then
Response.Write "Found: " & rs.Fields("LastName").Value
End If
Move Methods
These methods allow navigation through the records:
rs.MoveFirst ' Move to the first record
rs.MoveLast ' Move to the last record
rs.MoveNext ' Move to the next record
rs.MovePrevious ' Move to the previous record
rs.Move 5 ' Move forward five records
Open
The Open method is used to execute a SQL query and retrieve records:
rs.Open "SELECT * FROM Users", conn
Requery
To refresh the Recordset data, use the Requery method:
rs.Requery
Update
Finally, to save changes made to a record, you use the Update method:
rs.Fields("LastName").Value = "Smith"
rs.Update
Recordset Events
Events in the Recordset object allow developers to respond to various actions.
Event | Description |
---|---|
OnClose | Triggered when the Recordset is closed. |
OnDelete | Triggered when a record is deleted. |
OnMove | Triggered when the cursor is moved to a different record. |
OnUpdate | Triggered when a record is updated. |
Example of Using the Recordset Object
Here’s a complete example that demonstrates how to use the ADO Recordset object in an ASP application:
<%
Dim conn, rs, sql
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDatabase;User ID=YourUsername;Password=YourPassword;"
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Users"
rs.Open sql, conn
Response.Write "First Name Last Name "
Do While Not rs.EOF
Response.Write ""
Response.Write "" & rs.Fields("FirstName").Value & " "
Response.Write "" & rs.Fields("LastName").Value & " "
Response.Write " "
rs.MoveNext
Loop
Response.Write "
"
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
%>
Conclusion
The ADO Recordset Object is a powerful tool for handling data in ASP applications. Understanding its properties, methods, and events is essential for any beginner looking to work with databases in a web context. By practicing with the provided examples, you can gain confidence in managing data through the Recordset Object.
FAQ
- What is ADO? ADO stands for ActiveX Data Objects, which is a Microsoft technology for accessing data sources.
- What is a Recordset? A Recordset is an object that holds a set of records retrieved from a data source.
- How do I close a Recordset? You can use the Close method to close a Recordset.
- Can I navigate records in a Recordset? Yes, you can navigate records using methods like MoveNext, MovePrevious, etc.
- What is the difference between EOF and BOF? EOF indicates the cursor is after the last record while BOF indicates the cursor is before the first record.
Leave a comment