creating & retrieving records from ms-access 07 using oledb   Leave a comment


                                                        

Note:

Goto 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_access07

{

    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.ace.Oledb.12.0; data source=E:\prash\stud.accdb;Persist Security Info=False”);//stud.accdb->access07 filename

            loaddata();                     

            showdata();

        }

        void loaddata()

        {

            adapter = new OleDbDataAdapter(“select * from student”, con);

            ds = new DataSet();//student-> table name in stud.accdb 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();

        }

    }

}

Advertisements

Posted September 3, 2010 by PrashanthChindam in c#

Tagged with , ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: