inserting & retrieving records from M.S.Access-2007 using oledb in C#.net   Leave a comment


Objective:

To insert, search, edit and delete records from M.S.Access-2007 file using OleDb Connection.

Design:


Design the form as above with a DataGridView, 3 Labels, 3 TextBoxes, 10 buttons.

[ Note: In order to perform operations on M.S.Access-2007 records,
M.S.Office-2007 should be installed in your system.]

Introduction:

As we want to use OleDb Connection include the namespace:

‘using System.Data.OleDb’

For accesing records from M.S.Access-2003 file we use ‘Jet’ driver,

but for accesing records from M.S.Access-2003 file we use ‘Ace’ driver.

In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference:

Goto Project Menu

->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab.

Inorder to use this we have to include the namespace:

‘using Microsoft.VisualBasic’

->creating a primary key in the dataTable:

In this app. we use Find() method to search a record, which requires details of primarykey column for database tables this provided using statement: adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

But as we don’t have any primarykey column in M.S.Access table, we have to create a primary key column in datatable.

Eg:

ds.Tables[0].Constraints.Add(“pk_sno”, ds.Tables[0].Columns[0], true);

->pointing to current record in dataTable:

After searching a record, we have to get index of that record so that we can show next and previous records when we press ‘>>’(next) and ‘<<’(previous) buttons.

Eg:

rno= ds.Tables[0].Rows.IndexOf(drow);

—————————————————–

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 btnFirst_Click(object sender, EventArgs e)

{

if (ds.Tables[0].Rows.Count > 0)

{

rno = 0;

showdata();

}

else

MessageBox.Show(“no records”);

}

private void btnPrevious_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 btnNext_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 btnLast_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 btnInsert_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 btnSearch_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)

{

rno = ds.Tables[0].Rows.IndexOf(drow);

textBox1.Text = drow[0].ToString();

textBox2.Text = drow[1].ToString();

textBox3.Text = drow[2].ToString();

}

else

MessageBox.Show(“Record not found”);

}

private void btnUpdate_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 btnDelete_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 btnClear_Click(object sender, EventArgs e)

{

textBox1.Text = textBox2.Text = textBox3.Text = “”;

}

private void btnExit_Click(object sender, EventArgs e)

{

this.Close();

}

}

}

Advertisements

Posted October 4, 2010 by PrashanthChindam in c#

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: