How to create, update, delete data in Access using C#?

Question

I am creating this project using Microsoft Visual C# 2008 Express Edition. I want insert data using radio buttons how to write the code for example, i want insert gender (male/female). Please help me to write the code

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        OleDbConnection con;
        DataSet ds1;
        OleDbDataAdapter da;

        int MaxRows = 0;
        int inc = 0;

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new.OleDbConnection();
            ds1 = new DataSet();

            con.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/MyWorkers1.mdb";

            string sql = "SELECT * from tblWorkers";
            da = new OleDbDataAdapter(sql, con);

            con.Open();
            da.Fill(ds1, "MyWorkers1");
            NavigateRecords();
            MaxRows = ds1.Tables["MyWorkers1"].Rows.Count;
            //MaxRows = ds1.Tables["MyWorkers1"].Rows[inc];
            //MessageBox.Show("Database open");

            con.Close();
            //MessageBox.Show("Database close");

            con.Dispose();
        }

        private void NavigateRecords()
        {
            DataRow drow = ds1.Tables["MyWorkers1"].Rows[inc];
            textBox1.Text = drow.ItemArray.GetValue(0).ToString();
            textBox2.Text = drow.ItemArray.GetValue(1).ToString();
            textBox3.Text = drow.ItemArray.GetValue(2).ToString();
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc++;
                NavigateRecords();
            }
            else
            {
                MessageBox.Show("No More Records");
            }
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (inc > 0)
            {
                inc--;
                NavigateRecords();
            }
            else
            {
                MessageBox.Show("First Record");
            }
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {

            if (inc != 0)
            {
                inc = 0;
                NavigateRecords();
            }
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc = MaxRows - 1;
                NavigateRecords();
            }
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            System.Data.OleDb.OleDbCommandBuilder cb;
            cb = new System.Data.OleDb.OleDbCommandBuilder(da);

            DataRow drow = ds1.Tables["MyWorkers1"].NewRow();

            drow[0] = textBox1.Text;
            drow[1] = textBox2.Text;
            drow[2] = textBox3.Text;

            ds1.Tables["MyWorkers1"].Rows.Add(drow);

            MaxRows = MaxRows + 1;
            inc = MaxRows - 1;

            da.Update(ds1, "MyWorkers1");

            MessageBox.Show("Record / Entry Added");
        }       

    }
}

When run this it shows error in da.Update(ds1, "MyWorkers1");` like

Invalid OperationException Unhandled connection property has not been intialized

Please help me.

Why do you put OleDbCommandBuilder in btnSave_click event?

Prokurors
– Prokurors

2013-09-24 14:17:03 +00:00
Commented Sep 24, 2013 at 14:17 — Prokurors
– Prokurors, Commented Sep 24, 2013 at 14:17
OK, I got it... this is so "intuitive" :(

Prokurors
– Prokurors

2013-09-24 14:31:11 +00:00
Commented Sep 24, 2013 at 14:31 — Prokurors
– Prokurors, Commented Sep 24, 2013 at 14:31

slugster · Accepted Answer · 2011-05-04 10:42:13Z

2

You need to open your connection again in the Save method like you do in the FormLoad method.

edited May 4, 2011 at 10:42

slugster

50k14 gold badges105 silver badges150 bronze badges

answered Apr 8, 2011 at 13:43

Ryan Bennett

3,42223 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Berry Ligtermoet · Accepted Answer · 2012-05-31 14:57:01Z

1

You forgot to open your connection before the update.

Best practice is to open and close a connection right before and after a transaction.

First off do not dispose the connection in your Form1_Load() by removing con.Dispose(); from the function.

Then add the following around your update call in your btnSave_Click eventhandler:

con.Open();
da.Update(ds1, "MyWorkers1");
con.Close();

That should do the trick.

edited May 31, 2012 at 14:57

answered Apr 8, 2011 at 13:44

Berry Ligtermoet

8812 gold badges7 silver badges24 bronze badges

2 Comments

Berry Ligtermoet Over a year ago

@premkumar: please post a new question as this is a different topic.

premkumar Over a year ago

@Berry Ligtermoet: i am create new project using window form C# and also i create ms-access student database in that tblStudent is the table name in it i am using three attributes namely, (studID, name, gender). I am already insert data, but i want display data using combobox by selecting studID (remaing data will be displayed when selecting studID).

Collectives™ on Stack Overflow

How to create, update, delete data in Access using C#?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related