ADO Record Navigation

Introduction 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: Column Name Data Type PersonID COUNTER FirstName Text(20) LastName Text(20) Gender Text(20) After creating the table, you can populate it with a few records 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. The Fields Collection 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 index.  Record Navigation 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: MoveFirst: Moves the cursor to the first record MovePrevious: Moves the cursor to the record previous of the current record MoveNext: Moves the cursor to the next record of the current record MoveLast: Moves the cursor to the last record Move: Moves the cursor to a position specified by passing a number as the desired position The Beginning and the Last Positions 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