ADO Record Navigation
A recordset is a technique of getting all records from a table or by isolating a few records based on a rule called a criterion. After getting those records, one of the actions you can perform is to view them. Record navigation consists of visiting the records, one at a time, sometimes in one direction or back and forth.
Imagine you have created a database named People and you created a table named Persons with the following columns:
After creating the table, you can populate it with a few
The Field Class
The Field Class
To perform record navigation, you must communicate what Windows control of a form would display what field of a recordset. This action is referred to as data binding. To bind a column to a control, you must identify that column. To support columns, the ADO library identifies each column as a field. In the ADO namespace of the .NET Framework, a column is represented with the Field class.
Like a column of a table, a Field object is represented by a Name property. Another important characteristic of a column is the value under it. This is represented in the Field class by the Value property.
When creating a recordset, you have the option of using all columns of a table or selecting only one or a few of them. After creating the recordset, its columns are stored in a collection called Fields. To recognize the columns of a recordset as an entity, the Recordset interface is equipped with a property called Fields.
To identify a Field object of a Fields
collection, you use its index. To do this, you have two main alternatives.
You can identify a column using its position, also called its index, in
the collection. As an alternative, you can pass the name of the column as
After binding the columns to the Windows controls of a form, you can provide a few buttons to the form for record navigation. To move from one record to another, the Recordset class is equipped with appropriate methods. Their names are:
While navigating among records, you should make sure you don't move below the first record. To check the position of the cursor with regards to the first record, you can use the BOF Boolean property. When the cursor is at the beginning of the recordset, this property has a value of true.
When moving to next records, make sure you don't pass the last record. To check whether the cursor is at the last record, check the value of the EOF Boolean property of the recordset. If this property has a True value, then you are on the last record.
Here is code that illustrates record navigation:
Public Class Form1 Dim rstPeople as ADODB.Recordset Dim conPeople As ADODB.Connection Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load rstPeople = New ADODB.Recordset conPeople = New ADODB.Connection conPeople.Open("Provider='Microsoft.JET.OLEDB.4.0';" & _ "Data Source='C:\\Programs\\People.mdb';") rstPeople.Open("Persons", _ conPeople, _ ADODB.CursorTypeEnum.adOpenStatic, _ ADODB.LockTypeEnum.adLockOptimistic, 0) btnFirst_Click(sender, e) End Sub Private Sub RecordNavigator() txtPersonID.Text = rstPeople.Fields("PersonID").Value txtFirstName.Text = rstPeople.Fields("FirstName").Value txtLastName.Text = rstPeople.Fields("LastName").Value cboGenders.Text = rstPeople.Fields("Gender").Value End Sub Private Sub btnFirst_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnFirst.Click rstPeople.MoveFirst() RecordNavigator() End Sub Private Sub btnPrevious_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnPrevious.Click rstPeople.MovePrevious() If Not rstPeople.BOF Then RecordNavigator() Else btnFirst_Click(sender, e) End If End Sub Private Sub btnNext_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnNext.Click rstPeople.MoveNext() If Not rstPeople.EOF Then RecordNavigator() Else btnLast_Click(sender, e) End If End Sub Private Sub btnLast_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnLast.Click rstPeople.MoveLast() RecordNavigator() End Sub Private Sub Form1_FormClosing(ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.FormClosingEventArgs) _ Handles MyBase.FormClosing rstPeople.Close() conPeople.Close() End Sub Private Sub btnClose_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnClose.Click End End Sub End Class
|Home||Copyright © 2005-2015, FunctionX|