Updating record in C#

Description
The program illustrate on how to connect C# to MsAcess database, retrieve records from the database and update records to database.
Steps in updating records to Ms Access database.

1. Add oledb reference. using System.Data.OleDb;

2. Create object variables. (note: Make sure your object variables for connection and command are declared globally. OleDbConnection cn; //variable use for connecting the database

OleDbCommand cm; //variable use for updating records

3. Create a connection string.

string connection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\StudentRecord.accdb";

4. Inside the Form constructor instantiate your connection.

cn = new OleDbConnection(connection);

cn.Open();

5. Inside the btnSearch_Click event create the statements for retrieving record from the database.

string input = Interaction.InputBox("Look up", "Enter Student No.", "", 0, 0);

string sql = @"SELECT * from tblStudent where studentno like '" + input + "'";

cm = new OleDbCommand(sql, cn);

dr = cm.ExecuteReader();

dr.Read();

if (dr.HasRows)

{

txtID.Text = dr.GetValue(0).ToString();

txtLname.Text = dr.GetString(1).ToString();

txtFname.Text = dr.GetString(2).ToString();

txtMI.Text = dr.GetString(3).ToString();

cboProgram.Text = dr.GetString(4).ToString();

}

else

{

MessageBox.Show("Student No. not exist!", "Searching Record", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

}

dr.Close();

6. Inside the btnUpdate_Click event create the statements for updating record to the database.

string sql = @"UPDATE tblStudent SET lastname='" + txtLname.Text + "',firstname='" + txtFname.Text + "',mi= '" + txtMI.Text + "',program='" + cboProgram.Text + "' where studentno = '" + txtID.Text + "'";

cm = new OleDbCommand(sql, cn);

cm.ExecuteNonQuery();

Screenshot

Programming Language: C#
Database: MS Access
Click Contact Us to request the source code

Share this

Labels