In Depth ASP.NET using ADO.NET

09 Feb 2004 | by John Godel | Filed in

Comments
PDF

Download.zip

Introduction
Developing a Templated DataBound Control
Table, TableRow, and TableCell Web Server Controls
Adding Rows and Cells to a Table Web Server Contro
Data Binding Button, TextBox and DropDownList cont
Data Binding Expressions
Exploring with Web Based Address Database
Adding CheckBox Control to DataGrid Control
Data Navigation Form with Unbound Controls

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.

Comments

20 Recipes for Programming MVC 3: Faster, Smarter Web Development

There's no need to reinvent the wheel every time you run into a problem with ASP.NET's Model-View-Controller (MVC) framework. This concise cookbook provides recipes to help you solve tasks many web developers encounter every day. Each recipe includes...

ADO.NET forum discussion

edmonton female escort services near me

by canadapleasure (0 replies)
USB Drive Activity Logger!

by coque0912 (7 replies)
Required Every quarter data from sql automatically in below sql format

by jain.piyush831 (0 replies)
Bagaimana memenangkan $ 1,54 miliar dalam Mega Jutaan

by gametogelan (0 replies)
input integer from text file and output text file

by shmilon (0 replies)

ADO.NET podcasts

.NET Rocks: Jeff Fritz Knows One ASP.NET

Published 7 years ago, running time 0h54m

Carl and Richard talk to Jeff Fritz about how ASP.NET has evolved over the years. Jeff talks about the continued dominance of web forms in relation to MVC and how the different libraries can be used together. In fact, there's ONE ASP.NET, and everything: web forms, MVC, Web API, SignalR, all work.

ADO.NET jobs

.NET Web Developer at FinTech Startup (with traction)

FundApps in London, United Kingdom
Negotiable
.NET Developer

sti-professionalservices in Detroit, United States
FOR ALL POSITIONS COMPENSATION IS NEGOTIABLE
Web Developer at Growing Startup

FundApps in London, United Kingdom
Senior Software Engineers

LexisNexis in Cary, United States

In Depth ASP.NET using ADO.NET

Data Navigation Form with Unbound Controls

Summary

You might also like...

Comments

About the author

John Godel

ADO.NET tutorials

ADO.NET books

20 Recipes for Programming MVC 3: Faster, Smarter Web Development

ADO.NET forum discussion

edmonton female escort services near me

by canadapleasure (0 replies)

USB Drive Activity Logger!

by coque0912 (7 replies)

Required Every quarter data from sql automatically in below sql format

by jain.piyush831 (0 replies)

Bagaimana memenangkan $ 1,54 miliar dalam Mega Jutaan

by gametogelan (0 replies)

input integer from text file and output text file

by shmilon (0 replies)

ADO.NET podcasts

.NET Rocks: Jeff Fritz Knows One ASP.NET

Published 7 years ago, running time 0h54m

ADO.NET jobs

.NET Web Developer at FinTech Startup (with traction)

FundApps in London, United Kingdom
Negotiable

.NET Developer

sti-professionalservices in Detroit, United States
FOR ALL POSITIONS COMPENSATION IS NEGOTIABLE

Web Developer at Growing Startup

FundApps in London, United Kingdom

Senior Software Engineers

LexisNexis in Cary, United States

Contribute

Web Development

Developer Jobs

Our tools

In Depth ASP.NET using ADO.NET

Data Navigation Form with Unbound Controls

Summary

You might also like...

Comments

About the author

by canadapleasure (0 replies)

by coque0912 (7 replies)

by jain.piyush831 (0 replies)

by gametogelan (0 replies)

by shmilon (0 replies)

.NET Rocks: Jeff Fritz Knows One ASP.NET

Published 7 years ago, running time 0h54m

FundApps in London, United Kingdom Negotiable

sti-professionalservices in Detroit, United States FOR ALL POSITIONS COMPENSATION IS NEGOTIABLE

FundApps in London, United Kingdom

LexisNexis in Cary, United States

Contribute

Web Development

Developer Jobs

Our tools

FundApps in London, United Kingdom
Negotiable

sti-professionalservices in Detroit, United States
FOR ALL POSITIONS COMPENSATION IS NEGOTIABLE