.NET Winforms BindingNavigator Add and Delete buttons do not work

Question

On my VS2015 Winforms app, I created a DataGridView along with a BindingNavigator. The following code successfully displays the data in the DataGridView and I can navigate the data rows using the BindingNavigator. But when I try to add/delete a row using builtin Add/Delete buttons on the BindingNavigator the database does not reflect those changes.

The Code:

public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
        SqlDataAdapter dadapter;
        DataSet dset;
        BindingSource bs;
        string connstring = "database=emp;server=.;user=sa;password=wintellect";
        private void Form2_Load(object sender, EventArgs e)
        {
            dadapter = new SqlDataAdapter("select * from emp_detail", connstring);
            dset = new DataSet();
            dadapter.Fill(dset);
            bs = new BindingSource();
            bs.DataSource = dset.Tables[0].DefaultView;
            bindingNavigator1.BindingSource = bs;
            dataGridView1.DataSource = bs;
        }
    }

The database? All it does (and always was doing) is to delete the `DataRow` from the `DataTable`. Which is memory structure. — Ivan Stoev, Mar 17 '16 at 18:30
Changes in DataTable reflects to database via DataAdapter. DataAdapter must contains correct definitions for Insert, Update, Delete commands (in current case this commands can be autogenerated) and after user end edit you should call Update method: https://msdn.microsoft.com/en-us/library/xzb1zw3x.aspx — Andrey Polyakov, Mar 17 '16 at 18:35

Reza Aghaei · Accepted Answer · 2019-12-22T16:46:12.127

You forgot to save change to database. When you add, remove or edit items of your DataGridView, all changes are made in the underlying data source in memory and to persist changes, you should save those changes to database.

You can create valid InsertCommand, DeleteCommand and UpdateCommand for the SqlDataAdapter using a SqlCommandBuilder, then call Update method of the adapter to save changes of your DataTable to database:

SqlDataAdapter adapter;
DataSet table;
BindingSource bs;

private void Form1_Load(object sender, EventArgs e)
{
    var connection = "Your Connection String";
    var command = "SELECT * FROM SomeTable"
    adapter = new SqlDataAdapter(command, connstring);
    this.components.Add(adapter);
    table= new DataTable();

    //This line creates valid insert, update and delete commands for data adapter
    var commandBuilder = new SqlCommandBuilder(myTableAdapter);

    dadapter.Fill(table);
    bs = new BindingSource();
    bs.DataSource = table;
    bindingNavigator1.BindingSource = bs;
    dataGridView1.DataSource = bs;
}

private void SaveButton_Click(object sender, EventArgs e)
{
    this.Validate();
    this.dataGridView1.EndEdit();
    adapter.Update(table);
}

Note: The reason that I did this.components.Add(adapter); is because we should not forget to dispose the SqlDataAdapter. By adding it to components container of a form it will be disposed automatically.

If you have created the form without designer, you may don't have components container. In this case, dispose the adapter manually by overriding Dispose of the form or in Closing event.

.NET Winforms BindingNavigator Add and Delete buttons do not work

1 Answers1

Linked