How to retrieve previous record or next record using MS Access and C#

Question

I am writing a program which binds records to text boxes. i would like to know how to do that. I have done several things but nothing worked. it has Butttons ADD, UPDATE, DELETE, SAVE, First, Last, Next, and Previous. I am having problem 1) How to see specfic records using index. 2) I have done following code using internet help and does not work. Can someone study my code and tell me how to fix them. Here is my code: using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data.OleDb; using System.Data; using Microsoft.VisualBasic; namespace final { ///

/// Summary description for frmRental. ///

public class frmBook : System.Windows.Forms.Form { internal System.Windows.Forms.Button btnLast; internal System.Windows.Forms.Button btnFirst; internal System.Windows.Forms.Button btnEdit; internal System.Windows.Forms.Button btnDelete; internal System.Windows.Forms.Button btnSave; internal System.Windows.Forms.TextBox txtISBN; internal System.Windows.Forms.TextBox txtTitle; internal System.Windows.Forms.TextBox txtAuthor; internal System.Windows.Forms.Button btnAdd; internal System.Windows.Forms.Label lblRecordNumber; internal System.Windows.Forms.Button btnPrevious; internal System.Windows.Forms.Button btnNext; internal System.Windows.Forms.Label Title; internal System.Windows.Forms.Label ISBN; internal System.Windows.Forms.Label label1; private System.Windows.Forms.StatusBar statusBar1; private System.Windows.Forms.StatusBarPanel sbpOne; private System.Windows.Forms.StatusBarPanel sbpTwo; private System.Windows.Forms.StatusBarPanel sbpThree; private System.Windows.Forms.MainMenu mmu; private System.Windows.Forms.ToolBar toolBar1; private System.Windows.Forms.MenuItem menuItem1; private System.Windows.Forms.MenuItem menuItem2; private System.Data.DataSet myDataSet; //private ListManager myListManager; private bool isBound=false; ///

/// Required designer variable. ///

private System.ComponentModel.Container components; public frmBook() { // // Required for Windows Form Designer support // InitializeComponent(); //Establish the connection between controls and database EstablishConnection(); // // TODO: Add any constructor code after InitializeComponent call // } ///

/// Clean up any resources being used. ///

protected override void Dispose( bool disposing ) { if( disposing ) { if(components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

private void InitializeComponent() { this.btnLast = new System.Windows.Forms.Button(); this.btnFirst = new System.Windows.Forms.Button(); this.btnEdit = new System.Windows.Forms.Button(); this.btnDelete = new System.Windows.Forms.Button(); this.btnSave = new System.Windows.Forms.Button(); this.txtISBN = new System.Windows.Forms.TextBox(); this.txtTitle = new System.Windows.Forms.TextBox(); this.txtAuthor = new System.Windows.Forms.TextBox(); this.btnAdd = new System.Windows.Forms.Button(); this.lblRecordNumber = new System.Windows.Forms.Label(); this.btnPrevious = new System.Windows.Forms.Button(); this.btnNext = new System.Windows.Forms.Button(); this.Title = new System.Windows.Forms.Label(); this.ISBN = new System.Windows.Forms.Label(); this.label1 = new System.Windows.Forms.Label(); this.statusBar1 = new System.Windows.Forms.StatusBar(); this.sbpOne = new System.Windows.Forms.StatusBarPanel(); this.sbpTwo = new System.Windows.Forms.StatusBarPanel(); this.sbpThree = new System.Windows.Forms.StatusBarPanel(); this.mmu = new System.Windows.Forms.MainMenu(); this.menuItem1 = new System.Windows.Forms.MenuItem(); this.menuItem2 = new System.Windows.Forms.MenuItem(); this.toolBar1 = new System.Windows.Forms.ToolBar(); ((System.ComponentModel.ISupportInitialize)(this.sbpOne)).BeginInit(); ((System.ComponentModel.ISupportInitialize)(this.sbpTwo)).BeginInit(); ((System.ComponentModel.ISupportInitialize)(this.sbpThree)).BeginInit(); this.SuspendLayout(); // // btnLast // this.btnLast.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnLast.Location = new System.Drawing.Point(419, 198); this.btnLast.Name = "btnLast"; this.btnLast.Size = new System.Drawing.Size(80, 24); this.btnLast.TabIndex = 29; this.btnLast.Text = "&Last"; this.btnLast.Click += new System.EventHandler(this.btnLast_Click); // // btnFirst // this.btnFirst.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnFirst.Location = new System.Drawing.Point(163, 198); this.btnFirst.Name = "btnFirst"; this.btnFirst.Size = new System.Drawing.Size(72, 24); this.btnFirst.TabIndex = 28; this.btnFirst.Text = "Firs&t"; this.btnFirst.Click += new System.EventHandler(this.btnFirst_Click); // // btnEdit // this.btnEdit.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnEdit.Location = new System.Drawing.Point(475, 102); this.btnEdit.Name = "btnEdit"; this.btnEdit.TabIndex = 22; this.btnEdit.Text = "&Update"; this.btnEdit.Click += new System.EventHandler(this.btnEdit_Click); // // btnDelete // this.btnDelete.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnDelete.Location = new System.Drawing.Point(475, 134); this.btnDelete.Name = "btnDelete"; this.btnDelete.TabIndex = 23; this.btnDelete.Text = "&Delete"; this.btnDelete.Click += new System.EventHandler(this.btnDelete_Click); // // btnSave // this.btnSave.Enabled = false; this.btnSave.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnSave.Location = new System.Drawing.Point(475, 166); this.btnSave.Name = "btnSave"; this.btnSave.TabIndex = 24; this.btnSave.Text = "&Save"; this.btnSave.Click += new System.EventHandler(this.btnSave_Click); // // txtISBN // this.txtISBN.ForeColor = System.Drawing.SystemColors.ControlText; this.txtISBN.Location = new System.Drawing.Point(227, 110); this.txtISBN.Name = "txtISBN"; this.txtISBN.Size = new System.Drawing.Size(216, 20); this.txtISBN.TabIndex = 18; this.txtISBN.Text = ""; // // txtTitle // this.txtTitle.ForeColor = System.Drawing.SystemColors.ControlText; this.txtTitle.Location = new System.Drawing.Point(227, 150); this.txtTitle.Name = "txtTitle"; this.txtTitle.Size = new System.Drawing.Size(216, 20); this.txtTitle.TabIndex = 20; this.txtTitle.Text = ""; // // txtAuthor // this.txtAuthor.ForeColor = System.Drawing.SystemColors.ControlText; this.txtAuthor.Location = new System.Drawing.Point(227, 70); this.txtAuthor.Name = "txtAuthor"; this.txtAuthor.Size = new System.Drawing.Size(216, 20); this.txtAuthor.TabIndex = 16; this.txtAuthor.Text = ""; // // btnAdd // this.btnAdd.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnAdd.Location = new System.Drawing.Point(475, 70); this.btnAdd.Name = "btnAdd"; this.btnAdd.TabIndex = 21; this.btnAdd.Text = "&Add"; this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click); // // lblRecordNumber // this.lblRecordNumber.ForeColor = System.Drawing.SystemColors.HighlightText; this.lblRecordNumber.Location = new System.Drawing.Point(307, 238); this.lblRecordNumber.Name = "lblRecordNumber"; this.lblRecordNumber.Size = new System.Drawing.Size(240, 24); this.lblRecordNumber.TabIndex = 27; this.lblRecordNumber.TextAlign = System.Drawing.ContentAlignment.TopRight; // // btnPrevious // this.btnPrevious.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnPrevious.Location = new System.Drawing.Point(243, 198); this.btnPrevious.Name = "btnPrevious"; this.btnPrevious.Size = new System.Drawing.Size(80, 23); this.btnPrevious.TabIndex = 26; this.btnPrevious.Text = "&Previous"; this.btnPrevious.Click += new System.EventHandler(this.btnPrevious_Click); // // btnNext // this.btnNext.ForeColor = System.Drawing.SystemColors.HighlightText; this.btnNext.Location = new System.Drawing.Point(331, 198); this.btnNext.Name = "btnNext"; this.btnNext.TabIndex = 25; this.btnNext.Text = "&Next"; this.btnNext.Click += new System.EventHandler(this.btnNext_Click); // // Title // this.Title.ForeColor = System.Drawing.SystemColors.HighlightText; this.Title.Location = new System.Drawing.Point(83, 150); this.Title.Name = "Title"; this.Title.TabIndex = 19; this.Title.Text = "Title"; // // ISBN // this.ISBN.ForeColor = System.Drawing.SystemColors.HighlightText; this.ISBN.Location = new System.Drawing.Point(83, 110); this.ISBN.Name = "ISBN"; this.ISBN.TabIndex = 17; this.ISBN.Text = "ISBN"; // // label1 // this.label1.ForeColor = System.Drawing.SystemColors.HighlightText; this.label1.Location = new System.Drawing.Point(83, 70); this.label1.Name = "label1"; this.label1.TabIndex = 15; this.label1.Text = "Author"; // // statusBar1 // this.statusBar1.Location = new System.Drawing.Point(0, 311); this.statusBar1.Name = "statusBar1"; this.statusBar1.Panels.AddRange(new System.Windows.Forms.StatusBarPanel[] { this.sbpOne, this.sbpTwo, this.sbpThree}); this.statusBar1.ShowPanels = true; this.statusBar1.Size = new System.Drawing.Size(632, 22); this.statusBar1.TabIndex = 30; // // sbpOne // this.sbpOne.Text = "Anil Singhal"; this.sbpOne.Width = 214; // // sbpTwo // this.sbpTwo.Width = 213; // // sbpThree // this.sbpThree.Width = 213; // // mmu // this.mmu.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] { this.menuItem1}); // // menuItem1 // this.menuItem1.Index = 0; this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] { this.menuItem2}); this.menuItem1.Text = "File"; // // menuItem2 // this.menuItem2.Index = 0; this.menuItem2.Text = "Exit"; // // toolBar1 // this.toolBar1.DropDownArrows = true; this.toolBar1.Location = new System.Drawing.Point(0, 0); this.toolBar1.Name = "toolBar1"; this.toolBar1.ShowToolTips = true; this.toolBar1.Size = new System.Drawing.Size(632, 42); this.toolBar1.TabIndex = 31; // // frmBook // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.BackColor = System.Drawing.SystemColors.Desktop; this.ClientSize = new System.Drawing.Size(632, 333); this.Controls.Add(this.toolBar1); this.Controls.Add(this.statusBar1); this.Controls.Add(this.btnLast); this.Controls.Add(this.btnFirst); this.Controls.Add(this.btnEdit); this.Controls.Add(this.btnDelete); this.Controls.Add(this.btnSave); this.Controls.Add(this.txtISBN); this.Controls.Add(this.txtTitle); this.Controls.Add(this.txtAuthor); this.Controls.Add(this.btnAdd); this.Controls.Add(this.lblRecordNumber); this.Controls.Add(this.btnPrevious); this.Controls.Add(this.btnNext); this.Controls.Add(this.Title); this.Controls.Add(this.ISBN); this.Controls.Add(this.label1); this.ForeColor = System.Drawing.SystemColors.ControlText; this.Menu = this.mmu; this.Name = "frmBook"; this.Text = "Book Inventory"; this.Closing += new System.ComponentModel.CancelEventHandler(this.frmBook_Closing); this.Load += new System.EventHandler(this.frmBook_Load); ((System.ComponentModel.ISupportInitialize)(this.sbpOne)).EndInit(); ((System.ComponentModel.ISupportInitialize)(this.sbpTwo)).EndInit(); ((System.ComponentModel.ISupportInitialize)(this.sbpThree)).EndInit(); this.ResumeLayout(false); } #endregion private void EstablishConnection() { try { sbpOne.Text = "Connecting to Database..."; string connString = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb"; OleDbConnection conn = new OleDbConnection(connString); string commString = "SELECT * FROM Books"; myDataSet = new DataSet(); conn.Open(); OleDbCommand myCommand = new OleDbCommand(commString); myCommand.FillDataSet(myDataSet,"Books"); conn.Close(); if(!isBound) { txtTitle.Bindings.Add("Text", myDataSet.Tables["Books"], "Title"); txtISBN.Bindings.Add("Text", myDataSet.Tables["Books"], "ISBN"); txtAuthor.Bindings.Add("Text", myDataSet.Tables["Books"], "Author"); GetListManager(); isBound=true; } sbpOne.Text ="Connected to Database"; } catch(Exception e) { MessageBox.Show("Error in connecting! "+e.ToString(), "Error", MessageBox.IconExclamation); } } private void GetListManager() { myListManager = this.BindingManager[myDataSet.Tables["Books"]]; } private void btnAdd_Click(object sender, System.EventArgs e) { /*string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Books.mdb"; conn = new OleDbConnection( connectionString ); conn.Open(); string query = "insert into Books (ISBN, Title, Author) VALUES ('"+txtISBN.Text +"', '"+txtTitle.Text+"', '"+txtAuthor.Text+"')"; oleDbInsertCommand1 = new OleDbCommand(); oleDbInsertCommand1.CommandText = query; oleDbInsertCommand1.Connection = conn; oleDbInsertCommand1.ExecuteNonQuery(); conn.Close(); if (btnAdd.Text == "&Cancel") //Cancel an Add or Edit { LockTextBoxes(); EnableNavigation(); btnSave.Enabled = false; btnAdd.Text = "&Add"; RejectChanges(); mblnAdding = false; } else //Begin an Add operation { UnlockTextBoxes(); ClearText(); txtAuthor.Focus(); DisableNavigation(); btnSave.Enabled = true; btnAdd.Text = "&Cancel"; lblRecordNumber.Text = ""; mblnAdding = true; }*/ } private void btnEdit_Click(object sender, System.EventArgs e) { try { string connStr = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb"; OleDbConnection conn = new OleDbConnection(connStr); conn.Open(); string strDel = "SELECT * FROM Books WHERE ISBN = '"+txtISBN.Text+"'"; OleDbCommand myCommand = new OleDbCommand(strDel,conn); myDataSet.Tables["Books"].Rows[myListManager.Position].Delete(); myCommand.Update(myDataSet, "Books"); sbpOne.Text="Record Deleted"; conn.Close(); } catch(Exception ee) { MessageBox.Show("Error in Deleting! "+ee.ToString(), "Error", MessageBox.IconExclamation); } /*UnlockTextBoxes(); DisableNavigation(); btnSave.Enabled = true; btnAdd.Text = "&Cancel";*/ } private void btnDelete_Click(object sender, System.EventArgs e) { try { if(txtISBN.Text!=""&&txtTitle.Text!=""&&txtAuthor.Text!="") { string strConn="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb" ; //if you have a ODBC System Dsn use the below code //string strConn="Data Source=YourDsn" ; OleDbConnection conn = new OleDbConnection(strConn) ; conn.Open(); //the string to get values from the textboxes and form an "INSERT INTO" // statement. string strInsert = "INSERT INTO Books (ISBN, Title, Author) VALUES ( " +txtISBN.Text+", '"+txtTitle.Text+"' , '"+txtAuthor.Text+")"; OleDbCommand comm = new OleDbCommand(strInsert,conn) ; //Execute the statement comm.ExecuteNonQuery() ; sbpOne.Text="Data Added to Database" ; //reset all the textboxes int i=int.Parse(txtISBN.Text); i++; txtISBN.Text=i.ToString() ; txtTitle.Text="" ; txtAuthor.Text="" ; sbpOne.Text="Recorded can be added Now"; conn.Close() ; } else { MessageBox.Show("All fields must be completed.", "Error", MessageBox.IconExclamation); } } catch(Exception ed) { MessageBox.Show("Error in Saving "+ed.ToString(), "Error", MessageBox.IconExclamation); } /*DataSet ItemsDataSet = new DataSet(); sqlDataItems.Fill(ItemsDataSet,"Books"); DataRow[] item = ItemsDataSet.Tables["Books"].Select("ISBN = '" + txtISBN.Text + "'"); try { int intCurrentRecordNumber = this.BindingContext[DsBooks1, "Books"].Position; DsBooks1.Books.Rows[intCurrentRecordNumber].Delete(); mblnIsDirty = true; DisplayRecordPosition(); } catch { MessageBox.Show("Unable to delete the record", "Books"); }*/ } private void btnSave_Click(object sender, System.EventArgs e) { int i=myListManager.Position; try { string strCon="Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=book.mdb"; OleDbConnection myConn = new OleDbConnection(strCon); myConn.Open(); string query = "UPDATE Books SET Title='"+txtTitle.Text+"', ISBN='"+txtISBN.Text+"', Author='"+txtAuthor.Text+"' WHERE ISBN ="+txtISBN.Text; OleDbCommand myCommand = new OleDbCommand(query,myConn); myCommand.ExecuteNonQuery(); sbpOne.Text = "Record Updated"; myConn.Close(); myDataSet=null; myListManager=null; if(isBound) { txtISBN.Bindings.Remove(0); txtAuthor.Bindings.Remove(0); txtTitle.Bindings.Remove(0); isBound=false; } EstablishConnection(); } catch(Exception ee) { MessageBox.Show("Error in Updating! "+ee.ToString(), "Error", "MessageBox.IconExclamation"); } myListManager.Position=i; //Save the new record for an Add or Edit /*if (mblnAdding) //Add in progress { try { DataRow newRow = DsBooks1.Books.NewRow(); newRow["Author"] = txtAuthor.Text; newRow["ISBN"] = txtISBN.Text; newRow["Title"] = txtTitle.Text; DsBooks1.Books.Rows.Add(newRow); } catch( Exception exc) { MessageBox.Show("Unable to add the record.\n" + exc.Message, "Books"); } mblnAdding = false; lblRecordNumber.Text = "Record added at the end of the table"; } //Actions to take to complete an Add or an Edit LockTextBoxes(); EnableNavigation(); btnSave.Enabled = false; btnAdd.Text = "&Add"; mblnIsDirty = true;*/ } private void btnFirst_Click(object sender, System.EventArgs e) { First(); //Move to the first record /*try { this.BindingContext[DsBooks1, "Books"].Position = 0; DisplayRecordPosition(); } catch { //Ignore any errors during navigation }*/ } private void btnPrevious_Click(object sender, System.EventArgs e) { Previous(); //Display the previous record /* try { this.BindingContext[DsBooks1, "Books"].Position -= 1; DisplayRecordPosition(); } catch { //Ignore any errors during navigation }*/ } private void btnNext_Click(object sender, System.EventArgs e) { Next(); //Display the next record /*try { this.BindingContext[DsBooks1, "Books"].Position += 1; DisplayRecordPosition(); } catch { //Ignore any errors during navigation }*/ } private void btnLast_Click(object sender, System.EventArgs e) { Last(); //Move to the last record /*try { this.BindingContext[DsBooks1, "Books"].Position = this.BindingContext[DsBooks1, "Books"].Count - 1; DisplayRecordPosition(); } catch { //Ignore any errors during navigation }*/ } private void ClearText() { //Clear text fields /*txtAuthor.Clear(); txtISBN.Clear(); txtTitle.Clear();*/ } private void DisableNavigation() { //Disable navigation buttons /*btnNext.Enabled = false; btnPrevious.Enabled = false; btnFirst.Enabled = false; btnLast.Enabled = false;*/ } private void DisplayRecordPosition() { //Display the current record position and count /*int intRecordCount; int intRecordPosition; intRecordCount = DsBooks1.Tables["Books"].Rows.Count; intRecordPosition = this.BindingContext[DsBooks1, "Books"].Position + 1; if (intRecordCount == 0) { lblRecordNumber.Text = "(No records)"; } else { lblRecordNumber.Text = "Record " + intRecordPosition.ToString() + " of " + intRecordCount.ToString(); }*/ } private void EnableNavigation() { //Enable navigation buttons /*btnNext.Enabled = true; btnPrevious.Enabled = true; btnFirst.Enabled = true; btnLast.Enabled = true;*/ } private void LockTextBoxes() { //Change to ReadOnly /*txtAuthor.ReadOnly = true; txtISBN.ReadOnly = true; txtTitle.ReadOnly = true;*/ } private void RejectChanges() { //Replace original value into bound screen fields /*int intRecordPosition = this.BindingContext[DsBooks1, "Books"].Position; DataRow curRow = DsBooks1.Books.Rows[intRecordPosition]; try { txtAuthor.Text = curRow["Author", DataRowVersion.Original].ToString(); txtISBN.Text = curRow["ISBN", DataRowVersion.Original].ToString(); txtTitle.Text = curRow["Title", DataRowVersion.Original].ToString(); } catch { }*/ } private void UnlockTextBoxes() { //Change the ReadOnly property txtAuthor.ReadOnly = false; txtISBN.ReadOnly = false; txtTitle.ReadOnly = false; } private void frmBook_Load(object sender, System.EventArgs e) { string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=book.mdb"; OleDbConnection myConnection = new OleDbConnection( connectionString ); string query = "select * from Books"; OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection ); DataSet employeeData = new DataSet(); myAdapter.Fill( employeeData ); // Repeat for each table in the DataSet collection. foreach ( DataTable table in employeeData.Tables ) { // Repeat for each row in the table. foreach ( DataRow row in table.Rows ) { MessageBox.Show( "ISBN: " + row["ISBN"].ToString() ); MessageBox.Show( "Title : " + row["Title"].ToString() ); MessageBox.Show( "Author : " + row["Author"].ToString() ); } } //Fill the dataset /*string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Books.mdb"; conn = new OleDbConnection( connectionString ); conn.Open(); string query = "Select * From Books"; oleDbSelectCommand1 = new OleDbCommand(); oleDbSelectCommand1.CommandText = query; oleDbSelectCommand1.Connection = conn; oleDbSelectCommand1.ExecuteReader(); conn.Close(); conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\\RnrBooks.mdb"; dbRnR.Fill(DsBooks1); DisplayRecordPosition();*/ } private void Next() { if(myListManager.Position==myListManager.Count -1) myListManager.Position = 0; else myListManager.Position +=1; } private void Previous() { if(myListManager.Position ==0) myListManager.Position=myListManager.Count -1; else myListManager.Position -=1; } private void Last() { myListManager.Position = myListManager.Count -1; } protected void Last(object sender, System.EventArgs e) { Last(); } private void First() { myListManager.Position = 0; } protected void Next(object sender, System.EventArgs e) { Next(); } protected void previous(object sender, System.EventArgs e) { Previous(); } protected void First(object sender, System.EventArgs e) { First(); } private void frmBook_Closing(object sender, System.ComponentModel.CancelEventArgs e) { //Save the changes /*if (mblnIsDirty) { if (MessageBox.Show("Do you want to save the changes?", "Books", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { try { dbRnR.Update(DsBooks1, "Books"); } catch { MessageBox.Show("Error saving the file", "Books"); } } }*/ } } }