Creating & Retrieving Records from Ms-Access 03 using Oledb in C#

In this article we will learn how to Create & Retrieve Records from Ms-Access 03 using Oledb in C#


image001.jpg

Note:

Go to Project Menu

->Add Reference -> select 'Microsoft.VisualBasic' from .NET tab; include namespace 'using Microsoft.VisualBasic'(To get input box)
-------------
Code: 


using
System;

using System.Data;
using
System.Windows.Forms;
using
System.Data.OleDb;
using
Microsoft.VisualBasic;
 
namespace
prash_access03
 
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

 
        OleDbConnection con;
        OleDbCommand cmd;
        OleDbDataAdapter adapter;
        DataSet ds;
        int rno;

 
        private void Form1_Load(object sender, EventArgs e)
        {
            con = new OleDbConnection(@" provider=Microsoft.Jet.Oledb.4.0; data source=E:\prash\stud.mdb");// stud.mdb->access03 filename

            loaddata();
            showdata();
        }

 
        void loaddata()
        {
            adapter = new OleDbDataAdapter("select * from student", con);
            ds = new DataSet();//student-> table name in stud.mdb file
            adapter.Fill(ds,"student");
            ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);// creating primary key for Tables[0] in dataset
            dataGridView1.DataSource = ds.Tables[0];
        }
        void showdata()
        {
            textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();
            textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();
            textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();
        }

 
        private void clear_btn_Click(object sender, EventArgs e)
        {
            textBox1.Text = textBox2.Text = textBox3.Text = "";
        }

 
        private void first_btn_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                rno = 0;
                showdata();
            }
            else
                MessageBox.Show("no records");
        }

 
        private void prev_btn_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (rno > 0)
                {
                    rno--;
                    showdata();
                }
                else
                    MessageBox.Show("First Record");
            }
            else
                MessageBox.Show("no records");
        }

 
        private void next_btn_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (rno < ds.Tables[0].Rows.Count - 1)
                {
                    rno++;
                    showdata();
                }
                else
                MessageBox.Show("Last Record");

 
            }
            else
                MessageBox.Show("no records");
        }

 
        private void last_btn_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                rno = ds.Tables[0].Rows.Count - 1;
                showdata();
            }
            else
                MessageBox.Show("no records");
        }

        private void insert_btn_Click(object sender, EventArgs e)
        {
            cmd=new OleDbCommand("insert into student values("+textBox1.Text+",' "+textBox2.Text+" ',' "+textBox3.Text+" ')",con);
            con.Open();
            int n=cmd.ExecuteNonQuery();
            con.Close();
            if (n > 0)
            {
                MessageBox.Show("record inserted");
                 loaddata();
           }              
            else
                MessageBox.Show("insertion failed");
       }

         private void search_btn_Click(object sender, EventArgs e)
        {

int
n=Convert.ToInt32(Interaction.InputBox("Enter sno:",
"Search"
,"20",200,200));
            DataRow drow = ds.Tables[0].Rows.Find(n);
            if (drow != null)
            {
                textBox1.Text = drow[0].ToString();
                textBox2.Text = drow[1].ToString();
                textBox3.Text = drow[2].ToString();
            }
            else
                MessageBox.Show("Record not found");
        }

        private void update_btn_Click(object sender, EventArgs e)
        {
            cmd = new OleDbCommand("update student set sname='" + textBox2.Text + "',course='" + textBox3.Text + "' where sno=" + textBox1.Text, con);
            con.Open();
            int n=cmd.ExecuteNonQuery();
            con.Close();
            if (n > 0)
            { MessageBox.Show("Record Updated");
  loaddata();
            }
            else
               MessageBox.Show("Update failed");
        }

 
        private void delete_btn_Click(object sender, EventArgs e)
        {
      cmd = new OleDbCommand("delete from student where sno=

"
+ textBox1.Text, con);
            con.Open();
            int n = cmd.ExecuteNonQuery();
            con.Close();
            if (n > 0)
            { MessageBox.Show("Record Deleted");
  loaddata();
}
            else
                MessageBox.Show("Deletion failed");
        }

        private void exit_btn_Click(object sender, EventArgs e)
        {
            this.Close();
        }

 
    }
}