How to Delete and possibly add to a database using OLEDB

[ogsirus]

Hi Everyone,

Ive been messing around the OLEDB in C#, What im trying to do is create a program that would read from a Access Database, and then the user has the right to add,edit and delete a records. So far i can get the Database to display and are able to edit current records. Just wondering if someone can kindly shed some light on how to be able to delete and add records.

The code is as follows:

using System.Data;
using System;
using System.Drawing;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data.OleDb;

namespace BookDatabase
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;
        private DataRow selectedRow;
        private DataSet setdata = new DataSet();
        private BindingManagerBase pos;
        private OleDbConnection con;
        private OleDbDataAdapter apt;
        private bool isbound = false;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
            if (apt != null)
                apt.Dispose();
            if (con != null)
                con.Dispose();
        }
    
        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.listBox1 = new System.Windows.Forms.ListBox();
            this.button1 = new System.Windows.Forms.Button();
            this.button2 = new System.Windows.Forms.Button();
            this.button3 = new System.Windows.Forms.Button();
            this.button4 = new System.Windows.Forms.Button();
            this.button5 = new System.Windows.Forms.Button();
            this.textBox1 = new System.Windows.Forms.TextBox();
            this.button6 = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // listBox1
            // 
            this.listBox1.FormattingEnabled = true;
            this.listBox1.Location = new System.Drawing.Point(12, 26);
            this.listBox1.Name = "listBox1";
            this.listBox1.Size = new System.Drawing.Size(400, 199);
            this.listBox1.TabIndex = 0;
            this.listBox1.Click += new EventHandler(this.listData);
            // 
            // button1
            // 
            this.button1.Location = new System.Drawing.Point(438, 26);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(75, 31);
            this.button1.TabIndex = 1;
            this.button1.Text = "Start";
            this.button1.UseVisualStyleBackColor = true;
            this.button1.Click += new System.EventHandler(this.ButtonStart);
            // 
            // button2
            // 
            this.button2.Enabled = false;
            this.button2.Location = new System.Drawing.Point(438, 63);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(75, 31);
            this.button2.TabIndex = 2;
            this.button2.Text = "Select";
            this.button2.UseVisualStyleBackColor = true;
            this.button2.Click += new System.EventHandler(this.ButtonSelect);
            // 
            // button3
            // 
            this.button3.Location = new System.Drawing.Point(438, 100);
            this.button3.Name = "button3";
            this.button3.Size = new System.Drawing.Size(75, 32);
            this.button3.TabIndex = 3;
            this.button3.Text = "Insert";
            this.button3.UseVisualStyleBackColor = true;
            // 
            // button4
            // 
            this.button4.Enabled = false;
            this.button4.Location = new System.Drawing.Point(438, 138);
            this.button4.Name = "button4";
            this.button4.Size = new System.Drawing.Size(75, 31);
            this.button4.TabIndex = 4;
            this.button4.Text = "Update";
            this.button4.UseVisualStyleBackColor = true;
            this.button4.Click += new System.EventHandler(this.ButtonUpdate);

            // 
            // button5
            // 
            this.button5.Enabled = false;
            this.button5.Location = new System.Drawing.Point(438, 175);
            this.button5.Name = "button5";
            this.button5.Size = new System.Drawing.Size(75, 31);
            this.button5.TabIndex = 5;
            this.button5.Text = "Delete";
            this.button5.UseVisualStyleBackColor = true;
            this.button5.Click += new System.EventHandler(this.ButtonDelete);
            // 
            // textBox1
            // 
            this.textBox1.Location = new System.Drawing.Point(12, 249);
            this.textBox1.Name = "textBox1";
            this.textBox1.Size = new System.Drawing.Size(400, 20);
            this.textBox1.TabIndex = 6;
            this.textBox1.Click += new System.EventHandler(this.textUpdate);
            // 
            // button6
            // 
            this.button6.Location = new System.Drawing.Point(438, 238);
            this.button6.Name = "button6";
            this.button6.Size = new System.Drawing.Size(75, 31);
            this.button6.TabIndex = 7;
            this.button6.Text = "Exit";
            this.button6.UseVisualStyleBackColor = true;
            this.button6.Click += new System.EventHandler(this.ButtonExit);
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(554, 303);
            this.Controls.Add(this.button6);
            this.Controls.Add(this.textBox1);
            this.Controls.Add(this.button5);
            this.Controls.Add(this.button4);
            this.Controls.Add(this.button3);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.listBox1);
            this.Name = "Form1";
            this.Text = "Database";
            this.ResumeLayout(false);
            this.PerformLayout();


        }

        #endregion

        private System.Windows.Forms.ListBox listBox1;
        private System.Windows.Forms.Button button1;
        private System.Windows.Forms.Button button2;
        private System.Windows.Forms.Button button3;
        private System.Windows.Forms.Button button4;
        private System.Windows.Forms.Button button5;
        private System.Windows.Forms.TextBox textBox1;
        private System.Windows.Forms.Button button6;


        void ButtonSelect(object s, EventArgs e)
        {
            textBox1.Text = selectedRow["Title"].ToString();
            
        }
        void ButtonStart(object s, EventArgs e)
        {
            try
            {
                string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
                string dbcommand = "Select BookKey, Title, Pages from Books;";
                OleDbConnection con = new OleDbConnection(dbconnection);
                OleDbCommand com = new OleDbCommand(dbcommand, con);
                OleDbDataAdapter apt = new OleDbDataAdapter(com);

                con.Open();
                apt.Fill(setdata);
                con.Close();

                listBox1.Items.Clear();
                foreach (DataRow row in setdata.Tables[0].Rows)
                {
                    listBox1.Items.Add(row["BookKey"] + " | " + row["title"] + " | " + row["pages"]);
                }
                if (!isbound)
                {
                    pos = this.BindingContext[setdata, "Book"];
                    isbound = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("error");
            }
        }
        void ButtonUpdate(object s, EventArgs e)
        {
            string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            string dbcommand = "Select BookKey, Title, Pages from Books;";
            OleDbConnection con = new OleDbConnection(dbconnection);
            OleDbCommand com = new OleDbCommand(dbcommand, con);
            OleDbDataAdapter apt = new OleDbDataAdapter(com);     
            apt.UpdateCommand = new OleDbCommand("UPDATE Books SET Title =? WHERE BookKey = ?", con);
            apt.UpdateCommand.Parameters.Add("@Title", OleDbType.VarChar, 50, "Title");         
            OleDbParameter bookParameter = apt.UpdateCommand.Parameters.Add("@BookKey", OleDbType.Integer);
            bookParameter.SourceColumn = "BookKey";
    
            //update dataTable
            selectedRow["Title"] = textBox1.Text;
            setdata.Tables[0].Rows[listBox1.SelectedIndex].BeginEdit(); //lock the row
            DataRow rows = setdata.Tables[0].Rows[listBox1.SelectedIndex];
            rows["Title"] = selectedRow["Title"];
         
            //update D
            con.Open();
            DataSet dataSetChanges = setdata.GetChanges(DataRowState.Modified);
            apt.Update(dataSetChanges);
            con.Close();

            setdata.Tables[0].AcceptChanges();
            setdata.Tables[0].Rows[listBox1.SelectedIndex].EndEdit(); //unlock

            //update listBox1
            listBox1.Items.Clear();
            foreach (DataRow row in setdata.Tables[0].Rows)
            {
                listBox1.Items.Add(row["BookKey"] + " | " + row["title"] + " | " + row["pages"]);
            }
        }
        void ButtonDelete(object s, EventArgs e)
        {
            string dbconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            OleDbConnection con = new OleDbConnection(dbconnection);
            con.Open();
            string dbcommand = "delete from books where BookKey = ?";
            OleDbCommand com = new OleDbCommand(dbcommand, con);
            com.ExecuteNonQuery();
            setdata.Tables["Books"].Rows[pos.Position].Delete();
            setdata.Tables["Books"].AcceptChanges();
            con.Close();
        }
        void ButtonExit(object s, EventArgs e)
        {
            Application.Exit();
        }
        void listData(object s, EventArgs e)
        {
            selectedRow = setdata.Tables[0].Rows[listBox1.SelectedIndex];
            button2.Enabled = true;
            button5.Enabled = true;
        }
        void textUpdate(object s, EventArgs e)
        {
            button4.Enabled = true;
        }

        
    }
}

Thank you

[MattBerry]

Hi,

Have a look at DataAdapters to update your source and don't do acceptchanges as this clears the update flags.

Hope this helps

[ogsirus]

setdata.Tables[0].Rows[pos.Position].Delete();
//Update the database
apt.Update( setdata , "Books" );

I tried the above still didnt work. Not to sure if this is what you ment.

[MattBerry]

This should give you a general idea of what you need to do.

private void btn_Retrieve_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=MATT-PC\SQLEXPRESS;Initial Catalog=DataAdapterTest;Integrated Security=True");
            SqlCommand comm = new SqlCommand("Select id, value from MyTable", conn);
            da = new SqlDataAdapter(comm);

            SqlCommandBuilder cb = new SqlCommandBuilder(da);
            da.InsertCommand = cb.GetInsertCommand();
            da.UpdateCommand = cb.GetUpdateCommand();
            

            da.Fill(ds, "MyTable");

            if (ds.Tables["MyTable"].Rows.Count > 0)
            {
                int value = Convert.ToInt32(ds.Tables["MyTable"].Rows[0]["Value"]);
                textBox1.Text = value.ToString();
            }
        }

        private void btn_Save_Click(object sender, EventArgs e)
        {
            if (ds != null)
            {
                if (ds.Tables["MyTable"].Rows.Count > 0)
                    ds.Tables["MyTable"].Rows[0]["Value"] = Convert.ToInt32(textBox1.Text);
                else
                {
                    DataRow dr = ds.Tables["MyTable"].NewRow();
                    dr["Value"] = Convert.ToInt32(textBox1.Text);
                    ds.Tables["MyTable"].Rows.Add(dr);
                }
                da.Update(ds.Tables["MyTable"]);
            }
        }

[ogsirus]

Ok Ive got it sort of working now what ive used is this for the delete:

string pro = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            con = new OleDbConnection(pro);
            try
            {
                con.Open();
                string command = "DELETE * FROM BOOK";
                OleDbCommand com = new OleDbCommand(command, con);
                int c = (int)com.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                con.Close();
            }

Right now it is just deleting a column not the whole row. am assuming i do somthing similar for an insert except change the sql command