
or
Join Now!
|
|
Home/Homework & Reference/Codes & Ciphers
|
| Forum |
Ask A Question |
Question Board |
FAQs |
Search |
Return to Question Board
| Question Details |
Asked By |
Asked On |
| How to triversal through records MS Access database using C#. |
MAHASHA |
06/13/04 |
Hi, Here is my code please try to fix them if possible:
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) { //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"); } } }*/ }
} }
|
|
Your Options |
Additional Options are only visible when you login! !
|
|
|
|