Home

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

Home Copyright © 2005-2015, FunctionX