In this article we will learn how to Create & Retrieve Records from Ms-Access 03 using Oledb in C#
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(); } }}
Programming Dictionary in C#