none
RemoveAt & Update? (Have read sticky!) RRS feed

  • Question

  • Hi,    I'm having trouble with the removal of rows from an access database. I've read the sticky at the top of the forum and I don't think either of the points there are the cause. Adding rows persists through separate runs, but removes don't, though they are removed in the data view initially.
    Help is much appreciated, thanks

     Code I'm using is shown below:
    Code Snippet
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;

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

            private void Form1_Load(object sender, EventArgs e)
            {
                this.table1TableAdapter.Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb;";
                this.table1TableAdapter.Fill(this.testDataSet.Table1);
            }

            private void Add_Click(object sender, EventArgs e)
            {
                testDataSet.Table1Row row;
                row = testDataSet.Table1.NewTable1Row();
                row.Test = "A";
                testDataSet.Table1.Rows.Add(row);
            }

            private void Remove_Click(object sender, EventArgs e)
            {
                 testDataSet.Table1.Rows.RemoveAt(0);
             }

            private void Exit_Click(object sender, EventArgs e)
            {
                
                table1TableAdapter.Update(testDataSet.Table1);
                testDataSet.Table1.AcceptChanges();
                Application.Exit();
            }
        }
    }
    Thursday, February 21, 2008 5:54 PM

Answers

  • Removing and deleting are not the same thing.

     

    Calling Remove on a DataRow removes it from the DataTable's Rows collection, and sets its RowState to DataRowState.Detached (which you'll only ever see if you save a reference to it for some reason).  If it's not in the Rows collection, the Update method in the TableAdapter won't see it, and it won't update the underlying table.

     

    You want to call the Delete method.  This leaves the row in the Rows collection, but sets its RowState to DataRowState.Deleted.  The Update method will then see the row, sees that it's marked for deletion, and delete it from the table.

    Thursday, February 21, 2008 8:41 PM

All replies

  • Removing and deleting are not the same thing.

     

    Calling Remove on a DataRow removes it from the DataTable's Rows collection, and sets its RowState to DataRowState.Detached (which you'll only ever see if you save a reference to it for some reason).  If it's not in the Rows collection, the Update method in the TableAdapter won't see it, and it won't update the underlying table.

     

    You want to call the Delete method.  This leaves the row in the Rows collection, but sets its RowState to DataRowState.Deleted.  The Update method will then see the row, sees that it's marked for deletion, and delete it from the table.

    Thursday, February 21, 2008 8:41 PM
  • Ah thanks, I don't know quite why you would want to detach a row , but I'm sure theres a valid reason ! I've just tested it and delete works great.

    One more thing though, with the code I pasted above pressing remove repeatedly continues to remove rows (I suppose when it detaches the row it alters the indexes?). Is there an easy way to use delete that does the same? Will updating and refilling the dataset help?

    Thursday, February 21, 2008 8:51 PM
  • You detach rows when you want to remove them from the DataTable but you don't want to delete them from the database. 

     

    For instance, you an application might need to pull down a large set of rows and filter it down to a smaller working set before letting the user modify them.  Or an application might pull down many rows and, as the user modifies them and commits them to the database, remove them from the DataTable, so that the DataTable's contents represent the remaining work that the user has to do.

    Friday, February 22, 2008 9:48 PM