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 take 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

Before performing 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:

 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DataNavigation1
{
    public partial class Form1 : Form
    {
        ADODB.Recordset rstPeople;
        ADODB.Connection conPeople;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            rstPeople = new ADODB.Recordset();
            conPeople = new ADODB.Connection();

            conPeople.Open("Provider='Microsoft.JET.OLEDB.4.0';" +
                           "Data Source='C:\\Programs\\People.mdb';",
                           "", "", 0);
            rstPeople.Open("Persons",
                           conPeople,
                           ADODB.CursorTypeEnum.adOpenDynamic,
                           ADODB.LockTypeEnum.adLockOptimistic,
                           0);
            btnFirst_Click(sender, e);
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            rstPeople.MoveFirst();

            txtPersonID.Text  = rstPeople.Fields["PersonID"].Value.ToString();
            txtFirstName.Text = rstPeople.Fields["FirstName"].Value.ToString();
            txtLastName.Text  = rstPeople.Fields["LastName"].Value.ToString();
            cboGenders.Text   = rstPeople.Fields["Gender"].Value.ToString();
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
            rstPeople.MovePrevious();

            if( !rstPeople.BOF )
            {
                txtPersonID.Text = rstPeople.Fields["PersonID"].Value.ToString();
                txtFirstName.Text = rstPeople.Fields["FirstName"].Value.ToString();
                txtLastName.Text = rstPeople.Fields["LastName"].Value.ToString();
                cboGenders.Text = rstPeople.Fields["Gender"].Value.ToString();
            }
            else
                btnFirst_Click(sender, e);
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            rstPeople.MoveNext();

            if( !rstPeople.EOF )
            {
                txtPersonID.Text = rstPeople.Fields["PersonID"].Value.ToString();
                txtFirstName.Text = rstPeople.Fields["FirstName"].Value.ToString();
                txtLastName.Text = rstPeople.Fields["LastName"].Value.ToString();
                cboGenders.Text = rstPeople.Fields["Gender"].Value.ToString();
            }
            else
                btnLast_Click(sender, e);
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            rstPeople.MoveLast();

            txtPersonID.Text = rstPeople.Fields["PersonID"].Value.ToString();
            txtFirstName.Text = rstPeople.Fields["FirstName"].Value.ToString();
            txtLastName.Text = rstPeople.Fields["LastName"].Value.ToString();
            cboGenders.Text = rstPeople.Fields["Gender"].Value.ToString();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            rstPeople.Close();
            conPeople.Close();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}
Home Copyright © 2005-2016, FunctionX