In Depth ASP.NET using ADO.NET

Data Navigation Form with Unbound Controls

The form enables us to navigate through the records one at a time. We have used a combo box, two text boxes, and various command buttons in this application. The following is the run time view of the form shown initially. The data adapter is filled with data from the database and the combo box is populated with account numbers in ascending order when the form is loaded. The command buttons at the bottom of the form allows us to navigate through all the records one at a time. By using the first and last button respectively, we can instantly go to the first record and the last record. The current record number with respect to all the records is also displayed at the bottom of the page. These buttons are not the only means for navigating trough the records. By using the combo box we can also navigate through the records. The combo box enables us to select an account number and once its selected index is changed it will display all the details of that account number, basically the customer's name, and his balance.
The system provides not only navigating through the records it also enables us to;

  • Add a new record to the database.
  • Edit an existing record in the database.
  • Delete an existing record in the database.

When a new record is added or when an existing record is edited, the UPDATE button is used to update the actual database. The CANCEL button can be used in order to discard the changes we were about to make. All other buttons but the “UPDATE” and the “CANCEL” button are disabled when a user clicks the ADD button to add a new record. This is because the other buttons have no significance with the system is in ADD mode. The customer is supposed to click the “UPDATE” button to update the actual database or the “CANCEL” button to discard the current changes when he is done filling the details of the new record.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace NavigatingRecordsUnbound
{
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    ///
   
    public class Form1 : System.Windows.Forms.Form
    {
   
        private System.ComponentModel.Container components = null;
        private System.Data.OleDb.OleDbConnection oleDbConnection1;
        private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
        private System.Data.OleDb.OleDbCommandBuilder oleDbCommandBuilder1;
        private DataSet sampleDset;
        private string sqlStr;
        private int currentRow;
        private string opMode;
        private string AccountNumber;

        private System.Windows.Forms.ComboBox cbxAccountNumber;
        private System.Windows.Forms.TextBox tbxName;
        private System.Windows.Forms.TextBox tbxBalance;
        private System.Windows.Forms.Label lblAccountNumber;
        private System.Windows.Forms.Label lblName;
        private System.Windows.Forms.Label lblBalance;
        private System.Windows.Forms.Button btnFirst;
        private System.Windows.Forms.Button btnPrevious;
        private System.Windows.Forms.Button btnNext;
        private System.Windows.Forms.Button btnLast;
        private System.Windows.Forms.Button btnNew;
        private System.Windows.Forms.Button btnEdit;
        private System.Windows.Forms.Button btnDelete;
        private System.Windows.Forms.Button btnUpdate;
        private System.Windows.Forms.Label lblDisplay;
        private System.Windows.Forms.Button btnCancel;
        public Form1()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();
            // Attach event handlers to various buttons.
            btnFirst.Click += new EventHandler(btnFirst_Click);
            btnPrevious.Click += new EventHandler(btnPrevious_Click);
            btnNext.Click += new EventHandler(btnNext_Click);
            btnLast.Click += new EventHandler(btnLast_Click);
            btnNew.Click += new EventHandler(btnNew_Click);
            btnEdit.Click += new EventHandler(btnEdit_Click);
            btnDelete.Click += new EventHandler(btnDelete_Click);
            btnUpdate.Click += new EventHandler(btnUpdate_Click);
            btnCancel.Click += new EventHandler(btnCancel_Click);
            // Attach event handler to the Account Number combo box.
            cbxAccountNumber.SelectedIndexChanged += new EventHandler(cbxAccountNumber_Changed);
           
            loadData();
   
        }
private void loadData()
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Book Source Codes\\Navi\\bank_database.mdb";
    oleDbConnection1 = new OleDbConnection(connStr);
    oleDbConnection1.Open();
    sampleDset = new DataSet();
    sqlStr = "SELECT AccountNumber,CustomerName,AccountBalance "
                + "FROM bank ORDER BY AccountNumber";
oleDbDataAdapter1 = new OleDbDataAdapter(sqlStr, oleDbConnection1);
    oleDbDataAdapter1.SelectCommand.CommandText = sqlStr;
    oleDbCommandBuilder1 = new OleDbCommandBuilder(oleDbDataAdapter1);
    oleDbDataAdapter1.Fill(sampleDset,"dtblBankAccounts");
            currentRow = 0;
    }
        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        protected override void Dispose( bool disposing )
        {
            if( disposing )
            {
                if (components != null)
                {
                    components.Dispose();
                }
            }
            base.Dispose( disposing );
        }
        #region Windows Form Designer generated code
        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.btnCancel = new System.Windows.Forms.Button();
            this.lblBalance = new System.Windows.Forms.Label();
            this.btnFirst = new System.Windows.Forms.Button();
            this.cbxAccountNumber = new
                    System.Windows.Forms.ComboBox();
            this.btnUpdate = new System.Windows.Forms.Button();
            this.btnDelete = new System.Windows.Forms.Button();
            this.btnNew = new System.Windows.Forms.Button();
            this.btnPrevious = new System.Windows.Forms.Button();
            this.lblName = new System.Windows.Forms.Label();
            this.lblAccountNumber = new
                    System.Windows.Forms.Label();
            this.btnNext = new System.Windows.Forms.Button();
            this.tbxName = new System.Windows.Forms.TextBox();
            this.tbxBalance = new System.Windows.Forms.TextBox();
            this.btnLast = new System.Windows.Forms.Button();
            this.btnEdit = new System.Windows.Forms.Button();
            this.lblDisplay = new System.Windows.Forms.Label();
            this.SuspendLayout();
            //
            // btnCancel
            //
        this.btnCancel.Location = new System.Drawing.Point(384, 16);
            this.btnCancel.Name = "btnCancel";
            this.btnCancel.TabIndex = 14;
            this.btnCancel.Text = "Cancel";
            //
            // lblBalance
            //
        this.lblBalance.Location = new System.Drawing.Point(184, 112);
            this.lblBalance.Name = "lblBalance";
            this.lblBalance.Size = new System.Drawing.Size(80, 23);
            this.lblBalance.TabIndex = 5;
            this.lblBalance.Text = "Balance  $";
            this.lblBalance.TextAlign =
                  System.Drawing.ContentAlignment.MiddleRight;
            //
            // btnFirst
            //
        this.btnFirst.Location = new System.Drawing.Point(48, 192);
            this.btnFirst.Name = "btnFirst";
            this.btnFirst.TabIndex = 6;
            this.btnFirst.Text = "|< First";
            //
            // cbxAccountNumber
            //
            this.cbxAccountNumber.DropDownWidth = 96;
            this.cbxAccountNumber.Location = new
                    System.Drawing.Point(88, 72);
            this.cbxAccountNumber.Name = "cbxAccountNumber";
        this.cbxAccountNumber.Size = new System.Drawing.Size(96, 21);
            this.cbxAccountNumber.TabIndex = 0;
            //
            // btnUpdate
            //
        this.btnUpdate.Location = new System.Drawing.Point(296, 16);
            this.btnUpdate.Name = "btnUpdate";
            this.btnUpdate.TabIndex = 13;
            this.btnUpdate.Text = "Update";
            //
            // btnDelete
            //
        this.btnDelete.Location = new System.Drawing.Point(208, 16);
            this.btnDelete.Name = "btnDelete";
            this.btnDelete.TabIndex = 12;
            this.btnDelete.Text = "Delete";
            //
            // btnNew
            //
            this.btnNew.Location = new System.Drawing.Point(32, 16);
            this.btnNew.Name = "btnNew";
            this.btnNew.TabIndex = 10;
            this.btnNew.Text = "New";
            //
            // btnPrevious
            //
    this.btnPrevious.Location = new System.Drawing.Point(160, 192);
            this.btnPrevious.Name = "btnPrevious";
            this.btnPrevious.TabIndex = 7;
            this.btnPrevious.Text = "< Previous";
            //
            // lblName
            //
        this.lblName.Location = new System.Drawing.Point(200, 72);
            this.lblName.Name = "lblName";
            this.lblName.Size = new System.Drawing.Size(64, 23);
            this.lblName.TabIndex = 4;
            this.lblName.Text = "Name";
            this.lblName.TextAlign =
                    System.Drawing.ContentAlignment.MiddleRight;
            //
            // lblAccountNumber
            //
    this.lblAccountNumber.Location = new System.Drawing.Point(0, 72);
            this.lblAccountNumber.Name = "lblAccountNumber";
        this.lblAccountNumber.Size = new System.Drawing.Size(80, 23);
            this.lblAccountNumber.TabIndex = 3;
            this.lblAccountNumber.Text = "Acc Number";
            this.lblAccountNumber.TextAlign =
                    System.Drawing.ContentAlignment.MiddleRight;
            //
            // btnNext
            //
        this.btnNext.Location = new System.Drawing.Point(264, 192);
            this.btnNext.Name = "btnNext";
            this.btnNext.TabIndex = 8;
            this.btnNext.Text = "Next >";
            //
            // tbxName
            //
        this.tbxName.Location = new System.Drawing.Point(272, 72);
            this.tbxName.Name = "tbxName";
            this.tbxName.Size = new System.Drawing.Size(200, 20);
            this.tbxName.TabIndex = 1;
            this.tbxName.Text = "";
            //
            // tbxBalance
            //
        this.tbxBalance.Location = new System.Drawing.Point(272, 112);
            this.tbxBalance.Name = "tbxBalance";
            this.tbxBalance.TabIndex = 2;
            this.tbxBalance.Text = "";
            //
            // btnLast
            //
        this.btnLast.Location = new System.Drawing.Point(368, 192);
            this.btnLast.Name = "btnLast";
            this.btnLast.TabIndex = 9;
            this.btnLast.Text = "Last >|";
            //
            // btnEdit
            //
        this.btnEdit.Location = new System.Drawing.Point(120, 16);
            this.btnEdit.Name = "btnEdit";
            this.btnEdit.TabIndex = 11;
            this.btnEdit.Text = "Edit";
            //
            // lblDisplay
            //
        this.lblDisplay.Location = new System.Drawing.Point(136, 248);
            this.lblDisplay.Name = "lblDisplay";
            this.lblDisplay.Size = new System.Drawing.Size(232, 23);
            this.lblDisplay.TabIndex = 15;
            this.lblDisplay.TextAlign =
                    System.Drawing.ContentAlignment.MiddleCenter;
            //
            // Form1
            //
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.BackColor = System.Drawing.Color.Gainsboro;
            this.ClientSize = new System.Drawing.Size(488, 309);
            this.Controls.AddRange(new
    System.Windows.Forms.Control[] {
                                                                          this.lblDisplay,
                                                                          this.btnCancel,
                                                                          this.btnUpdate,
                                                                          this.btnDelete,
                                                                          this.btnEdit,
                                                                          this.btnNew,
                                                                          this.btnLast,
                                                                          this.btnNext,
                                                                          this.btnPrevious,
                                                                          this.btnFirst,
                                                                          this.lblBalance,
                                                                          this.lblName,
                                                                          this.lblAccountNumber,
                                                                          this.tbxBalance,
                                                                          this.tbxName,
                                                                          this.cbxAccountNumber});
            this.Name = "Form1";
            this.Text = "Navigating One Bank Record At A Time";
            this.Load += new System.EventHandler(this.Form1_Load);
            this.ResumeLayout(false);
        }
        #endregion
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.Run(new Form1());
        }
        private void Form1_Load(object sender, System.EventArgs e)
        {
            opMode = "View";
            lockControls();
            populateAccountNumbers();
            showData();
        }
        private void lockControls()
        {
            opMode = "View";
            tbxName.ReadOnly = true;
            tbxBalance.ReadOnly = true;
            btnUpdate.Enabled = false;
            btnCancel.Enabled = false;
        }
        private void unlockControls()
        {
            tbxName.ReadOnly = false;
            tbxBalance.ReadOnly = false;
            btnUpdate.Enabled = true;
            btnCancel.Enabled = true;
        }
private void populateAccountNumbers()
{
for ( int i=0; i < sampleDset.Tables["dtblBankAccounts"].Rows.Count; i++)
cbxAccountNumber.Items.Add
(sampleDset.Tables["dtblBankAccounts"].Rows[i]["AccountNumber"].ToString());
}
private void showData()
{
cbxAccountNumber.Text =
sampleDset.Tables["dtblBankAccounts"].Rows[currentRow]["AccountNumber"].ToString();
tbxName.Text =
sampleDset.Tables["dtblBankAccounts"].Rows[currentRow]["CustomerName"].ToString();
tbxBalance.Text =
sampleDset.Tables["dtblBankAccounts"].Rows[currentRow]["AccountBalance"].ToString();
lblDisplay.Text = "Record " + (currentRow + 1).ToString() + " of " + sampleDset.Tables["dtblBankAccounts"].Rows.Count.ToString();
}
private void btnFirst_Click(object source, System.EventArgs e)
{
    currentRow = 0;
    showData();
}
private void btnPrevious_Click(object source, System.EventArgs e)
{
    if ( currentRow > 0 )
currentRow --;
    showData();
}
private void btnNext_Click(object source, System.EventArgs e)
{
    if ( currentRow < sampleDset.Tables["dtblBankAccounts"].Rows.Count -1 )
currentRow ++;
    showData();
}
private void btnLast_Click(object source, System.EventArgs e)
{
    currentRow = sampleDset.Tables["dtblBankAccounts"].Rows.Count -1;
    showData();
}
private void btnUpdate_Click(object source, System.EventArgs e)
{
if (opMode == "New")
{   
string newAccountNumber;
newAccountNumber = cbxAccountNumber.Text;
if ( !checkIfUnique(newAccountNumber) )
{
    MessageBox.Show("Account Number already exists!");
    cbxAccountNumber.Focus();
    return;
}
DataRow newRow = sampleDset.Tables["dtblBankAccounts"].NewRow();
newRow["AccountNumber"] = cbxAccountNumber.Text;
newRow["CustomerName"] = tbxName.Text;
newRow["AccountBalance"] = double.Parse(tbxBalance.Text);
sampleDset.Tables["dtblBankAccounts"].Rows.Add(newRow);
cbxAccountNumber.Items.Add(cbxAccountNumber.Text);
oleDbDataAdapter1.Update(sampleDset,"dtblBankAccounts");
MessageBox.Show("Record successfully added!");
    }
    if (opMode == "Edit")
    {
string newAccountNumber;
newAccountNumber = cbxAccountNumber.Text;
if ( !checkIfUnique(newAccountNumber) )
{
    MessageBox.Show("Account Number already exists!");
    cbxAccountNumber.Focus();
    return;
}
int index = cbxAccountNumber.Items.IndexOf(AccountNumber);
cbxAccountNumber.Items.RemoveAt(index);
cbxAccountNumber.Items.Insert(index, cbxAccountNumber.Text);
sampleDset.Tables["dtblBankAccounts"].Rows[currentRow]["AccountNumber"] = cbxAccountNumber.Text;
sampleDset.Tables["dtblBankAccounts"].Rows[currentRow]["CustomerName"] = tbxName.Text;
sampleDset.Tables["dtblBankAccounts"].Rows[currentRow]["AccountBalance"] = double.Parse(tbxBalance.Text);
oleDbDataAdapter1.Update(sampleDset,"dtblBankAccounts");
MessageBox.Show("Record Updated");
    }
    currentRow = 0;
    lockControls();
    showData();
    btnNew.Enabled = true;
    btnEdit.Enabled = true;
    btnDelete.Enabled = true;
    btnFirst.Enabled = true;
    btnLast.Enabled = true;
    btnPrevious.Enabled = true;
    btnNext.Enabled = true;
    btnNext.Focus();
}
private void btnEdit_Click(object source, System.EventArgs e)
{
    currentRow = cbxAccountNumber.SelectedIndex;
    opMode = "Edit";
    unlockControls();
    btnCancel.Enabled = true;
    btnUpdate.Enabled = true;
    btnNew.Enabled = false;
    btnEdit.Enabled = false;
    btnDelete.Enabled = false;
    btnFirst.Enabled = false;
    btnLast.Enabled = false;
    btnPrevious.Enabled = false;
    btnNext.Enabled = false;
   
    AccountNumber = cbxAccountNumber.Text;
    cbxAccountNumber.Focus();
}
private void btnNew_Click(object source, System.EventArgs e)
{
    opMode = "New";
    unlockControls();
   
    cbxAccountNumber.Text = "";
    tbxName.Text = "";
    tbxBalance.Text = "";
    btnCancel.Enabled = true;
    btnUpdate.Enabled = true;
    btnNew.Enabled = false;
    btnEdit.Enabled = false;
    btnDelete.Enabled = false;
    btnFirst.Enabled = false;
    btnLast.Enabled = false;
    btnPrevious.Enabled = false;
    btnNext.Enabled = false;
    lblDisplay.Text = "";
    cbxAccountNumber.Focus();
}

private void btnCancel_Click(object source, System.EventArgs e)
{
    lockControls();
    showData();
    btnNew.Enabled = true;
    btnEdit.Enabled = true;
    btnDelete.Enabled = true;
    btnFirst.Enabled = true;
    btnLast.Enabled = true;
    btnPrevious.Enabled = true;
    btnNext.Enabled = true;
    btnNext.Focus();
}
private void btnDelete_Click(object source, System.EventArgs e)
{
    int index = cbxAccountNumber.SelectedIndex;
            sampleDset.Tables["dtblBankAccounts"].Rows[index].Delete();
    oleDbDataAdapter1.Update(sampleDset,"dtblBankAccounts");
    cbxAccountNumber.Items.RemoveAt(index);
    currentRow = 0;
    showData();
    MessageBox.Show("Record successfully deleted!");
}
private void cbxAccountNumber_Changed(object source, System.EventArgs e)
{
    if (opMode == "New" || opMode == "Edit")
    {
MessageBox.Show("You cannot select an Account Number in this mode!");
cbxAccountNumber.Focus();
    }
    else
    {
currentRow = cbxAccountNumber.SelectedIndex;
showData();
    }
}
private bool checkIfUnique(string newAccountNumber)
{
    string oldAccountNumber;
    foreach ( DataRow r in sampleDset.Tables["dtblBankAccounts"].Rows )
    {
oldAccountNumber = (string) r["AccountNumber"];
if ( newAccountNumber == oldAccountNumber )
    return false;
    }
    return true;
}
    }
}

Summary

In this article, we reviewed the DataGrid, data binding, and the most important server controls and techniques from ASP.NET and ADO.NET. We can use them most effectively when we take advantage of server controls. We discussed databinding expressions, server controls and then moved grid controls. Also we touched upon list and iterative controls. Data binding coverage does not end here, though, as it is too important a technology for building Web solutions with ADO.NET and ASP.NET.

You might also like...

Comments

About the author

John Godel United States

John H. GODEL has an experience more than 22 years in the area of software development. He is a software engineer and architect. His interests include object-oriented and distributed computin...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“It is practically impossible to teach good programming style to students that have had prior exposure to BASIC. As potential programmers, they are mentally mutilated beyond hope of regeneration.” - E. W. Dijkstra