Reputation: 1303

data is not getting deleted from database

In windows desktop application form I am using this code for deleting data from datagridview and database ,I have taken one checkbox column in dataridview ,If I click on checkbox row is getting deleted at that moment from datagridview ,but not from the database therefore when i reload form i can see that row again ,where I am going wrong?

public partial class EditEngClgList : Form
    {        
        private OleDbConnection acccon = null;
        private OleDbDataAdapter da = null;
        private DataTable dt = null;
        private BindingSource bs = null;
        private OleDbCommandBuilder cmdb = null;


        public EditEngClgList()
        {
            InitializeComponent();
            try
            {
                acccon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb");
                acccon.Open();
            }
            catch (Exception err)
            {
                MessageBox.Show("Error:" + err);
            }
            string sql = "Select * From EngColeges order by EngClgID";
            da = new OleDbDataAdapter(sql, acccon);
            cmdb = new OleDbCommandBuilder(da);

            dt = new DataTable();
            da.Fill(dt);
            bs = new BindingSource();
            bs.DataSource = dt;

            dataGridView1.DataSource = bs;

            dataGridView1.Columns[1].Visible = false;
            dataGridView1.Columns[2].HeaderText = "Engineering College Name";
            dataGridView1.Columns[3].HeaderText = "Adress";
            dataGridView1.Columns[4].HeaderText = "Entrance Type";
            dataGridView1.Columns[2].Width = 400;
        }

        private void button4_Click(object sender, EventArgs e)
        {
            List<int> checkedclg = new List<int>();
            DataRow dr;
            List<int> checkedclgid = new List<int>();
            for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
            {
                if (Convert.ToBoolean(dataGridView1.Rows[i].Cells["Delete"].Value) == true)
                {
                    checkedclg.Add(i);
                    checkedclgid.Add(Convert.ToInt16(dataGridView1.Rows[i].Cells["Delete"].Value)); 
                }
            }
            foreach (int k in checkedclg)
            {
                dr = dt.Rows[k];               
                dt.Rows[k].Delete();
                foreach (int j in checkedclgid)
                {
                    OleDbCommand oleDbCommand = new OleDbCommand("DELETE FROM EngColeges WHERE EngClgID = @clgID", acccon);
                    oleDbCommand.Parameters.Add("@clgID", OleDbType.Integer).Value = j;
                    oleDbCommand.Prepare();
                    oleDbCommand.ExecuteNonQuery();

                }
            }
        }

Upvotes: 2

Answers (6)

MRS1367

Reputation: 1053

You must change this line:

checkedclgid.Add(Convert.ToInt16(dataGridView1.Rows[i].Cells["Delete"].Value));

to the following:

checkedclgid.Add(Convert.ToInt32(dataGridView1.Rows[i].Cells["CellInTheInvisible‌Column"].Value));

Because you're converted a boolean type to Int16 and then you're used it in your query for checking with ID in your related table.

OleDbCommand oleDbCommand = new OleDbCommand("DELETE FROM EngColeges WHERE EngClgID = @clgID", acccon);

So, you must store ID of rows that you want to delete them.

Upvotes: 0

Durga

Reputation: 1303

Insted of storing value of Delete like this

checkedclgid.Add(Convert.ToInt16(dataGridView1.Rows[i].Cells["Delete"].Value));

storing The values of primary key column like this deletes data properly from database Also

checkedclgid.Add(Convert.ToInt32(dataGridView1.Rows[i].Cells["EngClgID"].Value));

Upvotes: 1

PythaLye

Reputation: 314

Just replace these two lines:

oleDbCommand.Prepare();
oleDbCommand.ExecuteNonQuery();

With:

da.DeleteCommand = oleDbCommand;

References: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.deletecommand.aspx

Upvotes: 0

Hack

Reputation: 1408

if (Convert.ToBoolean(dataGridView1.Rows[i].Cells["Delete"].Value) == true)
{
    checkedclg.Add(i);
    checkedclgid.Add(Convert.ToInt16(dataGridView1.Rows[i].Cells["Delete"].Value));
}

Looks like the wrong cell value is being passed to Convert.ToInt16? It's using the "Deleted" column instead of your ID column.

Also you can delete all the rows in one sql statement using the where in clause, for example: DELETE FROM table WHERE id IN (1, 2, 3, 4)

Upvotes: 1

Hanlet Escaño

Reputation: 17380

All you have right now is a command. You need your OleDbDataAdapter, and pass the command to it:

...
da = new OleDbDataAdapter(sql, acccon);
da.DeleteCommand = oleDbCommand;

More Info: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdataadapter.deletecommand.aspx

Upvotes: 0

shivasai

Reputation: 81

I think first of all you are deleting the row and then you are attempting to delete it from database. First of all you delete the row from database and then again call the select query for that table.

Upvotes: 0

data is not getting deleted from database

Answers (6)

Related Questions