How to Connectivity with Ms-Access 2007 with C# .Net

Connectivity with Ms-Access 2007 with C# .Net

Note:  In this topic we will discuss how to apply insert delete update search command in C# .Net with Microsoft Access 2007.  

Step 1: First we will go All Program -> Microsoft Office-> Microsoft Office Access 2007  after this we follow this step which is given below down.....

Step 2: after this we create table which name Student that contains four field SrNo->Number, Name->Text, Dob->DateTime,Course->Text  this  arrow is indicating the datatype  of each Colum of table.....

Step-3: after this we take a windows Application and follow this step……..

Code for Form1.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

//using this  namespace....................

using System.Data.OleDb;

using System.Text.RegularExpressions;

namespace ConnectivitywithAccess

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

OleDbConnection con;

private void Form1_Load(object sender, EventArgs e)

{

//connection String................................

con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\MyCollege.accdb;Persist Security Info=True;Jet OLEDB:Database Password=kush;");

DataGridBind();

}

// code for how to save data in Microsoft Access 2007.............

private void bntsave_Click(object sender, EventArgs e)

{

con.Open();

OleDbCommand cmd = new OleDbCommand("insert into Student values(" + Convert.ToInt32(txtsrno.Text) + ",'" + txtname.Text + "','" + Convert.ToDateTime(txtdob.Text) + "','" + txtcourse.Text + "')", con);

cmd.ExecuteNonQuery();

con.Close();

DataGridBind();

MessageBox.Show("Data Save Successfully");

TextClear();

}

//code for how to search data with SrNo…..

private void btnsearch_Click(object sender, EventArgs e)

{

con.Open();

OleDbCommand cmd = new OleDbCommand("select * from Student where SrNo=" + Convert.ToInt32(txtsrno.Text) + "", con);

OleDbDataReader dr = cmd.ExecuteReader();

if (dr.HasRows)

{

dr.Read();

txtname.Text = dr["Name"].ToString();

txtdob.Text = dr.GetDateTime(2).ToShortDateString();

txtcourse.Text = dr[3].ToString();

}

else

{

MessageBox.Show("SrNo =" + txtsrno.Text + " is  not found");

}

con.Close();

}

//code for how to delete data with SrNo…..

private void btnDelete_Click(object sender, EventArgs e)

{

con.Open();

OleDbCommand cmd = new OleDbCommand("delete from Student where SrNo="+Convert.ToInt32(txtsrno.Text)+"", con);

cmd.ExecuteNonQuery();

con.Close();

MessageBox.Show("SrNo =" + txtsrno.Text + " is  deleted successfully");

}

//code for  how bind dataGrid  with Table ………….with Disconnected Mode

private void DataGridBind()

{

// con.Open();

OleDbCommand cmd = new OleDbCommand("select * from Student order by SrNo", con);

//OleDbDataReader dr = cmd.ExecuteReader(); //with Connected Mode

//dr.Read();

//DataTable dt = new DataTable();

//dt.Load(dr);//

//dataGridView1.DataSource = dt;

//con.Close();

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

dataGridView1.DataSource = ds.Tables[0];

}

//code  for  how  to clear all TextBox

private void TextClear()

{

foreach(Control c in this.Controls)

{

if(c is TextBox)

//if (c.GetType() == typeof(TextBox))

{

c.Text = "";

}

}

}

// code  for  only  insert numeric value in TextBox .......................

private void txtsrno_KeyPress(object sender, KeyPressEventArgs e)

{

if (char.IsNumber(e.KeyChar) || e.KeyChar == 48 || e.KeyChar == 57)

{

e.Handled = false;

}

else

{

e.Handled = true;

}

}

}

}

//Result....