Click here to Skip to main content
Click here to Skip to main content
Go to top

How to link to an Access Database in a C# Application

, 21 Sep 2004
Rate this:
Please Sign up or sign in to vote.
An article explaining how to link your home database in your C# application.

Introduction

The ADO.NET object model provides an API for accessing database systems programmatically. Namespace System.Data is the root namespace for the ADO.NET API, the primary namespace for ADO.NET. System.Data.OleDb and System.Data.SqlClient contain classes that enable programmers to connect with and modify the data sources.

Some keywords:

  • DataSet: it stores the data in disconnected cache.
  • DataAdapter: the DataAdapter class represents a set of database commands and a database connection that you use to fill the DataSet and update the data source. It serves as a bridge between a DataSet and the data source.
  • Connection: it serves as a bridge between a data source and the application.
  • DataSource: is the database that the application will connect to.

Background

I’ve searched well so many sites about a code that I can [with the help of it] use ADO.NET to connect the Access database with a C# application. After searching the C# books, I’ve found some nice code that helped me to create this simple application. Hope it can help as a basic architecture.

Using the code

At first, you should simply open VS.NET and then at the File menu, click on New, Project. From the New Project dialog box, choose the Windows Application template project and name it WindowsApplication1, like shown below:

IE Window Class Names

After creating a window, add to it, four buttons Add, Update, Delete, Find, and four text boxes ISBN, Title, Author, Edition Number, and a label that represents the query that executed. The application will look like:

IE Window Class Names

Now, add OleDbDataAdapter in your application to control the database. The Access database that the application connects with will look like:

IE Window Class Names

When you add the OleDbDataAdapter, a configuration wizard will appear like:

IE Window Class Names

IE Window Class Names

Now, you should create a connection that will connect to your database. To do this, click on “New Connection” button.

IE Window Class Names

Select “Microsoft Jet 4.0 OLE DB Provider”. This provider enables the connection to communicate with the Access database. After that, click on “Next” button.

IE Window Class Names

In this page, you should determine the path of the data source that your application will connect with. After you do this, you can test the connection to ensure that there is no problem in the connection.

IE Window Class Names

IE Window Class Names

Finally, write the query that the data adapter will load in the DataSet; you can write the query manually or by the query builder.

But before that you should generate the database , to do this go into its property sheet and on the bottom of the sheet, select "Generate dataset".

IE Window Class Names

After finishing these steps, your application is now ready to control the database.

Now, let's begin to write the code for the Add button.

private void Add_btn_Click(object sender, System.EventArgs e)
{
    try
    {
        this.oleDbDataAdapter1.InsertCommand.CommandText = 
            "INSERT INTO BookDb (author, EditionNumber, ISBN, Title)" +
            "VALUES     ('"+ this.author_textBox.Text        
            +"','"+this.ed_textBox.Text+
            "' , "+ this.isbn_textBox.Text +",'"+   
            this.title_textBox.Text+"')";

        //open the bridge between the application and the datasource
        this.oleDbConnection1.Open();
        this.oleDbDataAdapter1.InsertCommand.Connection =  oleDbConnection1;

        //execute the qurey 
        this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();

        //close the connection
        this.oleDbConnection1.Close();

        MessageBox.Show("Record insedted Successfully");  //inform the user

        //clear the form 
        this.title_textBox.Text = "";
        this.isbn_textBox.Text = "";
        this.author_textBox.Text = "";
        this.ed_textBox.Text = "";

        //show the qurey
        this.qurey_label.Text = "";
        this.qurey_label.Text =                                    
        oleDbDataAdapter1.InsertCommand.CommandText.ToString();

    }
    catch(System.Data.OleDb.OleDbException exp)
    {
        //close the connection
        this.oleDbConnection1.Close();

        MessageBox.Show(exp.ToString());
    }

If you notice, in the insert query, all parameters come between ‘ ’ (single quotation) except the ISBN. The reason for this is the ISBN field in the database is declared as number while the other fields are declared as string, so they comes in ‘’ (single quotation).

Now, go to the Update button click function and write the following code:

private void up_btn_Click(object sender, System.EventArgs e)
{
    try
    {
        this.oleDbDataAdapter1.UpdateCommand.CommandText = 
            "UPDATE    BookDb SET "+
            "author ='"+ this.author_textBox.Text + 
            "', EditionNumber ='"+this.ed_textBox.Text + 
            "', ISBN ="+this.isbn_textBox.Text+", Title ='"+ 
            this.title_textBox.Text+"'"  +
            " WHERE  ISBN ="+ this.isbn_textBox.Text;

        //open the bridge between the application and the datasource
        this.oleDbConnection1.Open();

        this.oleDbDataAdapter1.UpdateCommand.Connection = oleDbConnection1;

        //execute the qurey 
        this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();

        //close the connection
        this.oleDbConnection1.Close();

        MessageBox.Show("Record updated Successfully");  //inform the user

        //clear the form 
        this.title_textBox.Text = "";
        this.isbn_textBox.Text = "";
        this.author_textBox.Text = "";
        this.ed_textBox.Text = "";

        //show the qurey
        this.qurey_label.Text = "";
        this.qurey_label.Text = 
           oleDbDataAdapter1.UpdateCommand.CommandText.ToString();
    }
    catch(System.Data.OleDb.OleDbException exp)
    {
        //close the connection
        this.oleDbConnection1.Close();
        MessageBox.Show(exp.ToString());
    }
}

And then the code for the Delete button click function:

private void del_btn_Click(object sender, System.EventArgs e)
{            
    try
    {
        this.oleDbDataAdapter1.DeleteCommand.CommandText = 
            "DELETE FROM BookDb WHERE ISBN =" + this.isbn_textBox.Text;

        //open the bridge between the application and the datasource
        this.oleDbConnection1.Open();
            
        this.oleDbDataAdapter1.DeleteCommand.Connection = oleDbConnection1;

        //execute the qurey 
        this.oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();

        //close the connection
        this.oleDbConnection1.Close();

        MessageBox.Show("Record deleted Successfully");  //inform the user

        //clear the form 
        this.title_textBox.Text = "";
        this.isbn_textBox.Text = "";
        this.author_textBox.Text = "";
        this.ed_textBox.Text = "";

        //show the qurey
        this.qurey_label.Text = "";
        this.qurey_label.Text = 
          oleDbDataAdapter1.DeleteCommand.CommandText.ToString();
    }
    catch(System.Data.OleDb.OleDbException exp)
    {
        //close the connection
        this.oleDbConnection1.Close();

        MessageBox.Show(exp.ToString());
    }
}

Finally, the code for the find button click function:

private void find_btn_Click(object sender, System.EventArgs e)
{
    try
    {
        this.oleDbDataAdapter1.SelectCommand.CommandText = 
            "SELECT * FROM BookDb WHERE ISBN = "+ this.isbn_textBox.Text;

        // clear the DataSet from the last operation
        dataSet11.Clear();

        this.oleDbDataAdapter1.Fill(this.dataSet11.Tables["BookDb"]);

    }
    catch(System.Data.OleDb.OleDbException exp)
    {
        MessageBox.Show(exp.ToString());
    }

    //copy the dataset in datatable object 
    DataTable dataTable = dataSet11.Tables[ 0 ];

    //if the row count = 0 then the qurey return nothing
    if ( dataTable.Rows.Count == 0 )
        MessageBox.Show("the Record not founded");
}

In this function, we use an object of type DataTable. A DataTable represents one table of in-memory relational data; the data is local to the .NET-based application in which it resides, but can be populated from a data source such as Microsoft® SQL Server, using a DataAdapter.

Full code

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

namespace WindowsApplication1
{
    /// <summary>
    /// Summary description for Form1.
    /// </summary>
    public class Form1 : System.Windows.Forms.Form
    {
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.Label label4;
        private System.Windows.Forms.TextBox isbn_textBox;
        private System.Windows.Forms.TextBox title_textBox;
        private System.Windows.Forms.TextBox author_textBox;
        private System.Windows.Forms.TextBox ed_textBox;
        private System.Windows.Forms.Button Add_btn;
        private System.Windows.Forms.Button up_btn;
        private System.Windows.Forms.Button del_btn;
        private System.Windows.Forms.Button find_btn;
        private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
        private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
        private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
        private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
        private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
        private System.Data.OleDb.OleDbConnection oleDbConnection1;
        private WindowsApplication1.DataSet1 dataSet11;
        private System.Windows.Forms.Label qurey_label;
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.Container components = null;

        public Form1()
        {
            //
            // Required for Windows Form Designer support
            //
            InitializeComponent();

            //
            // TODO: Add any constructor code after InitializeComponent call
            //
        }

        /// <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.isbn_textBox = new System.Windows.Forms.TextBox();
            this.dataSet11 = new WindowsApplication1.DataSet1();
            this.title_textBox = new System.Windows.Forms.TextBox();
            this.author_textBox = new System.Windows.Forms.TextBox();
            this.ed_textBox = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this.label4 = new System.Windows.Forms.Label();
            this.Add_btn = new System.Windows.Forms.Button();
            this.up_btn = new System.Windows.Forms.Button();
            this.del_btn = new System.Windows.Forms.Button();
            this.find_btn = new System.Windows.Forms.Button();
            this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
            this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
            this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
            this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
            this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
            this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
            this.qurey_label = new System.Windows.Forms.Label();
            ((System.ComponentModel.ISupportInitialize)
                                  (this.dataSet11)).BeginInit();
            this.SuspendLayout();
            // 
            // isbn_textBox
            // 
            this.isbn_textBox.DataBindings.Add(new 
                    System.Windows.Forms.Binding("Text", 
                    this.dataSet11, "BookDb.ISBN"));
            this.isbn_textBox.Location = new System.Drawing.Point(104, 64);
            this.isbn_textBox.Name = "isbn_textBox";
            this.isbn_textBox.Size = new System.Drawing.Size(216, 20);
            this.isbn_textBox.TabIndex = 0;
            this.isbn_textBox.Text = "";
            // 
            // dataSet11
            // 
            this.dataSet11.DataSetName = "DataSet1";
            this.dataSet11.Locale = new 
                    System.Globalization.CultureInfo("en-US");
            this.dataSet11.Namespace = "http://www.tempuri.org/DataSet1.xsd";
            // 
            // title_textBox
            // 
            this.title_textBox.DataBindings.Add(new 
                    System.Windows.Forms.Binding("Text", this.dataSet11, 
                    "BookDb.Title"));
            this.title_textBox.Location = new System.Drawing.Point(104, 104);
            this.title_textBox.Name = "title_textBox";
            this.title_textBox.Size = new System.Drawing.Size(216, 20);
            this.title_textBox.TabIndex = 1;
            this.title_textBox.Text = "";
            // 
            // author_textBox
            // 
            this.author_textBox.DataBindings.Add(new 
                     System.Windows.Forms.Binding("Text", this.dataSet11, 
                     "BookDb.author"));
            this.author_textBox.Location = new System.Drawing.Point(104, 144);
            this.author_textBox.Name = "author_textBox";
            this.author_textBox.Size = new System.Drawing.Size(216, 20);
            this.author_textBox.TabIndex = 2;
            this.author_textBox.Text = "";
            // 
            // ed_textBox
            // 
            this.ed_textBox.DataBindings.Add(new 
                     System.Windows.Forms.Binding("Text", this.dataSet11, 
                     "BookDb.EditionNumber"));
            this.ed_textBox.Location = new System.Drawing.Point(104, 184);
            this.ed_textBox.Name = "ed_textBox";
            this.ed_textBox.Size = new System.Drawing.Size(216, 20);
            this.ed_textBox.TabIndex = 3;
            this.ed_textBox.Text = "";
            // 
            // label1
            // 
            this.label1.Location = new System.Drawing.Point(16, 64);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(72, 23);
            this.label1.TabIndex = 4;
            this.label1.Text = "ISBN";
            // 
            // label2
            // 
            this.label2.Location = new System.Drawing.Point(16, 104);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(72, 23);
            this.label2.TabIndex = 5;
            this.label2.Text = "Title";
            // 
            // label3
            // 
            this.label3.Location = new System.Drawing.Point(16, 144);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(72, 23);
            this.label3.TabIndex = 6;
            this.label3.Text = "Author";
            // 
            // label4
            // 
            this.label4.Location = new System.Drawing.Point(16, 184);
            this.label4.Name = "label4";
            this.label4.Size = new System.Drawing.Size(80, 23);
            this.label4.TabIndex = 7;
            this.label4.Text = "Edtion Number";
            // 
            // Add_btn
            // 
            this.Add_btn.Location = new System.Drawing.Point(40, 16);
            this.Add_btn.Name = "Add_btn";
            this.Add_btn.TabIndex = 8;
            this.Add_btn.Text = "Add";
            this.Add_btn.Click += new System.EventHandler(this.Add_btn_Click);
            // 
            // up_btn
            // 
            this.up_btn.Location = new System.Drawing.Point(136, 16);
            this.up_btn.Name = "up_btn";
            this.up_btn.TabIndex = 9;
            this.up_btn.Text = "Update";
            this.up_btn.Click += new System.EventHandler(this.up_btn_Click);
            // 
            // del_btn
            // 
            this.del_btn.Location = new System.Drawing.Point(240, 16);
            this.del_btn.Name = "del_btn";
            this.del_btn.TabIndex = 10;
            this.del_btn.Text = "Delete";
            this.del_btn.Click += new System.EventHandler(this.del_btn_Click);
            // 
            // find_btn
            // 
            this.find_btn.Location = new System.Drawing.Point(16, 312);
            this.find_btn.Name = "find_btn";
            this.find_btn.Size = new System.Drawing.Size(320, 24);
            this.find_btn.TabIndex = 11;
            this.find_btn.Text = "Find";
            this.find_btn.Click += new System.EventHandler(this.find_btn_Click);
            // 
            // oleDbDataAdapter1
            // 
            this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
            this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
            this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
            this.oleDbDataAdapter1.TableMappings.AddRange(new 
                 System.Data.Common.DataTableMapping[] {
                    new System.Data.Common.DataTableMapping("Table", "BookDb", 
                    new System.Data.Common.DataColumnMapping[] {
                        new System.Data.Common.DataColumnMapping("ISBN", "ISBN"),
                        new System.Data.Common.DataColumnMapping("Title", "Title"),
                        new System.Data.Common.DataColumnMapping("EditionNumber", 
                                                               "EditionNumber"),
                        new System.Data.Common.DataColumnMapping("author", "author")
                    })
                 });
            this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
            // 
            // oleDbDeleteCommand1
            // 
            this.oleDbDeleteCommand1.CommandText = 
                "DELETE FROM BookDb WHERE (ISBN = ?) AND " + 
                "(EditionNumber = ? OR ? IS NULL AND Editi" +
                "onNumber IS NULL) AND (Title = ? OR ? IS NULL " + 
                "AND Title IS NULL) AND (author = ?" + 
                " OR ? IS NULL AND author IS NULL)";
            this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_ISBN", 
                 System.Data.OleDb.OleDbType.Integer, 0, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(10)), ((System.Byte)(0)), "ISBN", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_EditionNumber", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_EditionNumber1", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_Title", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "Title", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_Title1", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "Title", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_author", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "author", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbDeleteCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_author1", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "author", 
                 System.Data.DataRowVersion.Original, null));
            // 
            // oleDbConnection1
            // 
            this.oleDbConnection1.ConnectionString = 
                   @"Provider=Microsoft.Jet.OLEDB.4.0;" + 
                   @"Password="""";User ID=Admin;Data Source=D:\MY Db\Ahmed" + 
                   @" Article\WindowsApplication1\bookDb.mdb;Mode=Share" + 
                   @" Deny None;Extended Properties="""";Jet OLEDB:" + 
                   @"System database="""";Jet OLEDB:Registry " + 
                   @"Path="""";Jet OLEDB:Database Password="""";Jet " + 
                   @"OLEDB:Engine Type=5;Jet OLEDB:Database " + 
                   @"Locking Mode=1;Jet OLEDB:Global Partial Bulk " + 
                   @"Ops=2;Jet OLEDB:Global Bulk Transactions=1;" + 
                   @"Jet OLEDB:New Database Password="""";Jet OLEDB:" + 
                   @"Create System Database=False;Jet OLEDB:Encrypt " + 
                   @"Database=False;Jet OLEDB:Don't Copy Locale " + 
                   @"on Compact=False;Jet OLEDB:Compact Without " + 
                   @"Replica Repair=False;Jet OLEDB:SFP=False";
            // 
            // oleDbInsertCommand1
            // 
            this.oleDbInsertCommand1.CommandText = 
                 "INSERT INTO BookDb(author, EditionNumber, " + 
                 "ISBN, Title) VALUES (?, ?, ?, ?)";
            this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
            this.oleDbInsertCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("author", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, "author"));
            this.oleDbInsertCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("EditionNumber", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, "EditionNumber"));
            this.oleDbInsertCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("ISBN", 
                 System.Data.OleDb.OleDbType.Integer, 0, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(10)), ((System.Byte)(0)), "ISBN", 
                 System.Data.DataRowVersion.Current, null));
            this.oleDbInsertCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Title", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, "Title"));
            // 
            // oleDbSelectCommand1
            // 
            this.oleDbSelectCommand1.CommandText = 
                 "SELECT author, EditionNumber, ISBN, Title FROM BookDb";
            this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
            // 
            // oleDbUpdateCommand1
            // 
            this.oleDbUpdateCommand1.CommandText = 
                "UPDATE BookDb SET author = ?, EditionNumber = ?, " + 
                "ISBN = ?, Title = ? WHERE (ISBN " + 
                "= ?) AND (EditionNumber = ? OR ? IS NULL AND " + 
                "EditionNumber IS NULL) AND (Title =" + 
                " ? OR ? IS NULL AND Title IS NULL) AND (author " + 
                "= ? OR ? IS NULL AND author IS NU" + "LL)";
            this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("author", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, "author"));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("EditionNumber", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, "EditionNumber"));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("ISBN", 
                 System.Data.OleDb.OleDbType.Integer, 0, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(10)), ((System.Byte)(0)), "ISBN", 
                 System.Data.DataRowVersion.Current, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Title", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, "Title"));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_ISBN", 
                 System.Data.OleDb.OleDbType.Integer, 0, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(10)), ((System.Byte)(0)), "ISBN", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_EditionNumber", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_EditionNumber1", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "EditionNumber", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_Title", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "Title", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_Title1", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "Title", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_author", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "author", 
                 System.Data.DataRowVersion.Original, null));
            this.oleDbUpdateCommand1.Parameters.Add(new 
                 System.Data.OleDb.OleDbParameter("Original_author1", 
                 System.Data.OleDb.OleDbType.VarWChar, 50, 
                 System.Data.ParameterDirection.Input, false, 
                 ((System.Byte)(0)), ((System.Byte)(0)), "author", 
                 System.Data.DataRowVersion.Original, null));
            // 
            // qurey_label
            // 
            this.qurey_label.Location = new System.Drawing.Point(40, 224);
            this.qurey_label.Name = "qurey_label";
            this.qurey_label.Size = new System.Drawing.Size(288, 72);
            this.qurey_label.TabIndex = 12;
            this.qurey_label.Text = "Qurey";
            // 
            // Form1
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(360, 366);
            this.Controls.AddRange(new System.Windows.Forms.Control[] {
                                                  this.qurey_label,
                                                  this.find_btn,
                                                  this.del_btn,
                                                  this.up_btn,
                                                  this.Add_btn,
                                                  this.label4,
                                                  this.label3,
                                                  this.label2,
                                                  this.label1,
                                                  this.ed_textBox,
                                                  this.author_textBox,
                                                  this.title_textBox,
                                                  this.isbn_textBox});
            this.MaximizeBox = false;
            this.MinimizeBox = false;
            this.Name = "Form1";
            this.StartPosition = 
                 System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Form1";
            ((System.ComponentModel.ISupportInitialize)
                           (this.dataSet11)).EndInit();
            this.ResumeLayout(false);

        }
        #endregion

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main() 
        {
            Application.Run(new Form1());
        }

        private void find_btn_Click(object sender, System.EventArgs e)
        {
            try
            {
                
                this.oleDbDataAdapter1.SelectCommand.CommandText = 
                    "SELECT * FROM BookDb WHERE ISBN = "+ 
                    this.isbn_textBox.Text;

                // clear the DataSet from the last operation
                dataSet11.Clear();

                this.oleDbDataAdapter1.Fill(this.dataSet11.Tables["BookDb"]);

            }
            catch(System.Data.OleDb.OleDbException exp)
            {
                MessageBox.Show(exp.ToString());
            }

            //copy the dataset in datatable object 
               DataTable dataTable = dataSet11.Tables[ 0 ];

            //if the row count = 0 then the qurey return nothing
              if ( dataTable.Rows.Count == 0 )
                  MessageBox.Show("the Record not founded");
        }

        private void Add_btn_Click(object sender, System.EventArgs e)
        {
            try
            {
                this.oleDbDataAdapter1.InsertCommand.CommandText = 
                    "INSERT INTO BookDb (author, EditionNumber, ISBN, Title)" +
                    "VALUES ('"+ this.author_textBox.Text + 
                    "','"+this.ed_textBox.Text+
                    "' , "+ this.isbn_textBox.Text +",'"+ 
                    this.title_textBox.Text+ "')";

                //open the bridge between the application and the datasource
                this.oleDbConnection1.Open();

                this.oleDbDataAdapter1.InsertCommand.Connection = oleDbConnection1;

                 //execute the qurey 
                this.oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();

                //close the connection
                this.oleDbConnection1.Close();

                //inform the user
                MessageBox.Show("Record insedted Successfully");

                //clear the form 
                this.title_textBox.Text = "";
                this.isbn_textBox.Text = "";
                this.author_textBox.Text = "";
                this.ed_textBox.Text = "";

                //show the qurey
                this.qurey_label.Text = "";
                this.qurey_label.Text = 
                    oleDbDataAdapter1.InsertCommand.CommandText.ToString();

            }
            catch(System.Data.OleDb.OleDbException exp)
            {
                //close the connection
                this.oleDbConnection1.Close();

                MessageBox.Show(exp.ToString());
            }


        }

        private void up_btn_Click(object sender, System.EventArgs e)
        {
            try
            {
                this.oleDbDataAdapter1.UpdateCommand.CommandText = 
                    "UPDATE    BookDb SET "+
                    "author ='"+ this.author_textBox.Text + 
                    "', EditionNumber ='"+this.ed_textBox.Text +
                    "', ISBN ="+this.isbn_textBox.Text+", Title ='"+ 
                    this.title_textBox.Text+"'"  + 
                    " WHERE  ISBN ="+ this.isbn_textBox.Text;

                //open the bridge between the application and the datasource
                this.oleDbConnection1.Open();
            
                this.oleDbDataAdapter1.UpdateCommand.Connection = oleDbConnection1;

                //execute the qurey 
                this.oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();

                //close the connection
                this.oleDbConnection1.Close();

                //inform the user
                MessageBox.Show("Record updated Successfully");

                //clear the form 
                this.title_textBox.Text = "";
                this.isbn_textBox.Text = "";
                this.author_textBox.Text = "";
                this.ed_textBox.Text = "";

                //show the qurey
                this.qurey_label.Text = "";
                this.qurey_label.Text = 
                  oleDbDataAdapter1.UpdateCommand.CommandText.ToString();
            }
            catch(System.Data.OleDb.OleDbException exp)
            {
                //close the connection
                this.oleDbConnection1.Close();
                MessageBox.Show(exp.ToString());
            }

        }

        private void del_btn_Click(object sender, System.EventArgs e)
        {
            try
            {
                this.oleDbDataAdapter1.DeleteCommand.CommandText = 
                    "DELETE FROM BookDb WHERE ISBN =" + this.isbn_textBox.Text;

                //open the bridge between the application and the datasource
                this.oleDbConnection1.Open();

                this.oleDbDataAdapter1.DeleteCommand.Connection = oleDbConnection1;

                //execute the qurey 
                this.oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();

                //close the connection
                this.oleDbConnection1.Close();

                //inform the user
                MessageBox.Show("Record deleted Successfully");

                //clear the form 
                this.title_textBox.Text = "";
                this.isbn_textBox.Text = "";
                this.author_textBox.Text = "";
                this.ed_textBox.Text = "";

                //show the qurey
                this.qurey_label.Text = "";
                this.qurey_label.Text = 
                  oleDbDataAdapter1.DeleteCommand.CommandText.ToString();
            }
            catch(System.Data.OleDb.OleDbException exp)
            {
                //close the connection
                this.oleDbConnection1.Close();

                MessageBox.Show(exp.ToString());
            }
        }
    }
}

Tip

This application will not work unless you modify the path for the data source. To do this, go to the OleDbConnection property: ConnectionString, and modify the path by the path of the Access file on your PC.

The demo file for this application expects to find the Access file in “C:\”.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ahmed jamil Kattan
Web Developer
United Kingdom United Kingdom
Ahmed J. Kattan Bachelor degree from Jordan University of Science and Technology computer science department, Master Degree from University of Essex and PhD student at University of Essex ”United Kingdom”, I have written several applications, designed multiple algorithms and several publications. My favorite languages are C++ and C#.
 

 
see www.ahmedkattan.com to view Ahmed Kattan's online CV.

Comments and Discussions

 
AnswerRe: Multiple tables accessing Pinmemberseishin66613-Dec-06 22:55 
GeneralRe: Multiple tables accessing Pinmembermarwaelsawah14-Dec-06 2:36 
AnswerRe: Multiple tables accessing PinmemberAhmed jamil Kattan16-Dec-06 16:49 
QuestionPlease help updateing access database PinmemberMasterPro5-Oct-06 19:18 
AnswerRe: Please help updateing access database PinmemberAhmed jamil Kattan6-Oct-06 5:54 
QuestionRe: Please help updateing access database [modified] PinmemberMasterPro8-Oct-06 7:12 
QuestionHow to set the adodb.connection.OpenSchema parameters in c#? Pinmemberliuzhuangli6-Jul-06 8:07 
GeneralOLE object display PinsussMatijko14-Sep-05 1:21 
GeneralRe: OLE object display PinmemberAhmed jamil Kattan15-Sep-05 0:22 
GeneralRe: OLE object display PinmemberMatijko15-Sep-05 9:45 
GeneralRe: OLE object display PinmemberAhmed jamil Kattan17-Sep-05 23:09 
GeneralPlz Look... PinmemberUpul J Bandara7-Jul-05 20:38 
GeneralRe: Plz Look... PinmemberAhmed jamil Kattan8-Jul-05 21:13 
GeneralRe: Plz Look... PinmemberUpul J Bandara9-Jun-09 0:23 
Generalples help me!! PinsussN-ALhmmady27-May-05 3:09 
GeneralRe: ples help me!! PinmemberAhmed jamil Kattan1-Jun-05 0:26 
Generaldatabindings PinsussAnonymous8-May-05 22:07 
GeneralRe: databindings PinmemberAhmed jamil Kattan1-Jun-05 0:38 
GeneralRe: Help Me Please!!! PinmemberChristian Graus3-Jan-05 15:30 
GeneralRe: Help Me Please!!! PinmemberAhmed jamil Kattan4-Jan-05 6:33 
GeneralHelp Me Please!!! PinmemberWayKlaro!3-Jan-05 15:00 
GeneralAccess database from C#.Net via ODBC PinmemberWill L Pittenger12-Oct-04 22:47 
GeneralRe: Access database from C#.Net via ODBC PinsussAnonymous17-Oct-04 9:54 
GeneralRe: Access database from C#.Net via ODBC PinsussAnonymous17-Oct-04 9:55 
GeneralRe: Access database from C#.Net via ODBC PinmemberWill L Pittenger17-Oct-04 10:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 21 Sep 2004
Article Copyright 2004 by Ahmed jamil Kattan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid