Objective: To develop a windows application for performing insert, search, update, delete operations & navigation of M.S.Access 2007 records using Odbc connection.
Introduction:
Create a table in M.S.Access 2007 file and populate it.
In our application we use ‘stud.accdb’ (M.S.Access 2007) file, which consists ‘student’ table.
(Note: ‘stud.accdb’ is placed in ‘prash_access07.zip’ along with source code)
Creating and Configuring ODBC Data Source (dsn):
Go to Start Menu -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
Click on ‘Add’ button
-> Select ‘Microsoft Access Driver (*.mdb, *.accdb)’ ->click on ‘Finish’ button.
Give a name to your Data Source
Click on ‘Select’ button and select your M.S.Access 2007 file (*.accdb) -> OK -> OK
Your Data Source Name will be specified in ‘ODBC Data Source Administrator’ window
->Click on ‘OK’ button.
Thus, your Data Source (dsn) is configured.
Design:
Design the form as above with a DataGridView, 3 Labels, 3 TextBoxes, 10 buttons.
Binding an event procedure with mutiple controls in c#.net
Objective: Demo on binding an event procedure with mutiple controls in c#.net using calculator program
Design:
Design the form as above with 1 TextBox and 19 buttons.
Explaination:
By binding an event procedure with mutiple controls having similar functionality we can reduce the no. Of lines of code in a program, as well as save our time.
In our application, when a we click a numeric button the respective number(text) should appear on the textbox.
Example for click event of btn0:
->for click event of btn0
textBox1.Text = btn0.Text;
->for click event of btn1
textBox1.Text = btn1.Text;
The code for click events of above two buttons is almost same, only the button names(btn0,btn1) are different.
*Binding an Event Procedure with multiple controls:
Instead of writing the similar code for all numeric buttons, we can write the code for the click event of a single button and bind it to all other buttons with similar functionality
i.e; write code for click event of btn0 and bind it with click events of btn1,btn2,...,btn9.
->Binding Event Procedure of ‘btn0’with other numeric buttons:
Select ‘btn0’, right click-> select ‘Properties’
->click on events icon
->select ‘Click’ event and double click,
which generates click event for btn0, now write the code for btn0_click
Note: we can also generate click event of a button by double clicking on it.
->Now select the remaining numeric buttons on the form ,
Go to Properties window -> select ‘events’ tab -> select ‘Click’ event
->select ‘btn0_Click’
But if we do that, only the text of btn0 will appear on textbox when we click any of the numeric buttons.
Therefore, we have to recognize the button that had generated the event, read its text and display it on the textbox.
*When an event procedure is bound with mutiple controls, to recognize the control that has raised the event, we can make use of the parameter ‘sender’.
As ‘sender’ is of the type ‘object’, it will capture the object of the control that has raised the event.
We can refer to the members of that control by converting the ‘sender’ to appropriate control type.
Ex:
private void btn0_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;//converting the object 'sender' to control 'Button'
textBox1.Text = btn.Text;
}
->Similarly bind the buttons btnAdd, btnSub, btnMult and btnDiv to a single event procedure, as these 4 buttons have similar code
private void btnAdd_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
calc();
oper = Convert.ToChar(btn.Text);
flag = true;
}
-------------
Code:
using System;
using System.Windows.Forms;
namespace calc
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
double res = 0; char oper; bool txt_changed=false, flag = false, clear_txtbox =false;
private void Form1_Load(object sender, EventArgs e)
{
textBox1.Text = res.ToString();
}
private void btn0_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;//converting the object 'sender' to control 'Button'
txt_changed = true;
if (clear_txtbox == true || textBox1.Text == "0")
{
textBox1.Text = btn.Text;
}
else
textBox1.Text = textBox1.Text + btn.Text;
clear_txtbox = false;
}
private void btnDot_Click(object sender, EventArgs e)
{
int index;
index = textBox1.Text.IndexOf('.');
if (index == -1 || clear_txtbox == true)
{
if (clear_txtbox == true || textBox1.Text == "0")
{
textBox1.Text = "0.";
}
else
textBox1.Text = textBox1.Text + ".";
clear_txtbox = false;
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
Button btn = (Button)sender;
calc();
oper = Convert.ToChar(btn.Text);
flag = true;
}
void calc()
{
if (textBox1.Text.Length > 0)
{
if (flag == true && txt_changed == true)
{
switch (oper)
{
case '+':
res = res + Convert.ToDouble(textBox1.Text);
break;
case '-':
res = res - Convert.ToDouble(textBox1.Text);
break;
case '*':
res = res * Convert.ToDouble(textBox1.Text);
break;
case '/':
res = res / Convert.ToDouble(textBox1.Text);
break;
case '%':
res = res % Convert.ToDouble(textBox1.Text);
break;
default:
break;
}
textBox1.Text = res.ToString();
}
res = Convert.ToDouble(textBox1.Text);
}
textBox1.Text = res.ToString();
txt_changed = false;
clear_txtbox = true;
}
private void btnEqualto_Click(object sender, EventArgs e)
{
calc();
}
private void btnBackspace_Click(object sender, EventArgs e)
{
if (txt_changed == true)
textBox1.Text = textBox1.Text.Substring(0, textBox1.Text.Length - 1);
if (textBox1.Text.Length == 0 && res == 0)
textBox1.Text = res.ToString();
}
private void btnClear_Click(object sender, EventArgs e)
{
res = 0;
flag = false; clear_txtbox = false; txt_changed = false;
textBox1.Text = res.ToString();
}
}
}
Introduction to Code:
As we want to use Odbc Connection include the namespace:
'using System.Data.Odbc'
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.
In order to use this we have to include the namespace:
'using Microsoft.VisualBasic'
Odbc connection string:
Syntax:
OdbcConnection con = new OdbcConnection("dsn=<Data Source Name>");
Ex:
OdbcConnection con = new OdbcConnection(“dsn=myaccess07dsn ");
You just need to specify the Data Source Name(dsn) in the Connection String, no need to specify the driver details and path of the file, you dsn will take care of it.
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 is 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.Ex:
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 the index of that record so that we can show next and previous records when we press '>>'(next) and '<<'(previous) buttons.Ex:
rno= ds.Tables[0].Rows.IndexOf(drow);
-------------
Code:
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.Odbc;
using Microsoft.VisualBasic;
namespace prash_access07
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
OdbcConnection con;
OdbcCommand cmd;
OdbcDataAdapter adapter;
DataSet ds;
int rno;
private void Form1_Load(object sender, EventArgs e)
{
con = new OdbcConnection("dsn=myaccess07dsn");
//stud.accdb->access07 filename
loaddata();
showdata();
}
void loaddata()
{
adapter = new OdbcDataAdapter("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, EventArgse)
{
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 OdbcCommand("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 OdbcCommand("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 OdbcCommand("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();
}
}
}
