How to set up BindingNavigator? - BindingNavigator is not working

Question

I have a data app that uses a BindingNavigator. I have hooked up the navigator to a binding source, however it is not working. It will not add or delete rows. The binding source is: accountBindingSource. I don't understand what is wrong. I have the following code:

public partial class AccountDataGridView : Form
{
    public AccountDataGridView()
    {
        InitializeComponent();
        Setup();
    }

    private void AccountDataGridView_Load(object sender, EventArgs e)
    {
        // Change the back color of the first column. This can also be changed in the designer
        accountGridView.Columns[0].DefaultCellStyle.BackColor = Color.FromArgb(192, 192, 255);
    }

    private void Setup()
    {
        // Define a global variable for the data table
        Account = new DataTable(Text);
        query = string.Format("SELECT * FROM {0}", Text);
        // Establish a connection between the Database and the form
        conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Tutoring Database.accdb;Persist Security Info=False");
        conn.Open();
        // Setup data table
        OleDbDataAdapter accountAdapter = new OleDbDataAdapter(query, conn);
        if (accountAdapter != null)
        {
            accountAdapter.Fill(Account);
        }
        accountGridView.DataSource = Account;

        conn.Close();
    }

    private void DataErrorRaised(object sender, DataGridViewDataErrorEventArgs e)
    {
        // Data table error handling. This is triggered when the user attempts to input invalid data into the CurrentBalance column
        MessageBox.Show("You have entered an invalid data type for the currency column. Please enter text formatted like so: '$0.00'",
            "Account Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        // Update Access Database
        try
        {
            adapter.SelectCommand = new OleDbCommand(query, conn);
            adapter.InsertCommand = new OleDbCommand(query, conn);
            adapter.DeleteCommand = new OleDbCommand(query, conn);
            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

            adapter.Update(Account);
            Console.WriteLine("Saved");
        }

        catch
        {

        }
    }

    private DataTable Account;
    private string query;
    private OleDbConnection conn;
    private OleDbDataAdapter adapter = new OleDbDataAdapter();

    private void accountGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {
        btnSave_Click(null, null);
    }
}

Answer 1

In your question you assigned data to accountGridView.DataSource. So you can not expect the binding navigator work. The BindingSource is not connected to data and the BindingNavigator and DataGridView are not connected to the BindingSource.

You should perform these settings using designer or code:

• Load data and assign data to DataSource property of BindingSource. (Using code)
• Assign BindingSource to DataSource property of your DataGridView
• Assign BindingSource to BindingSource property of the BindingNavigator.

Note

Changing query has nothing to do with binding source. The BindingSource and BindingNavigator work regardless of the adapter or anything which provides/saves data. Neither BindingSource nor BindingNavigator are not aware about where the data comes from actually and how the data will be saved.

Answer 2

I just solved my problem by creating a new BindingSource and setting its properties through code. To do this, simply drag a BindingSource onto the form. Name it whatever you would like. In the code part of your solution type something similar to this (in the constructor).

BindingSource.DataSource = Account;

Make sure to set your BindingNavigator's BindingSource to the BindingSource you just created. Thank you for the help, everyone!

EDIT: I don't know if this had anything to do with my solution but I also edited my queries a little bit. This code is mandatory for the project to work.

try
{
    adapter.SelectCommand = new OleDbCommand(query, conn);
    adapter.InsertCommand = new OleDbCommand("INSERT INTO Account (AccountNumber, LastName, FirstName, CurrentBalance) " +
    "VALUES (?, ?, ?, ?)", conn);
    adapter.DeleteCommand = new OleDbCommand(query, conn);
    OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);

    adapter.Update(Account);
    Console.WriteLine("Saved");
}
catch
{
}

Answer 3

For posterity:

BindingNavigator's buttons are lightweight controls, unlike a real Button control.

When you click any button in the BindingNavigator, the Focus is not stolen from the current Control, and thus the sequence of events that lead to the data being pushed to the underlying database is never called.

I know I had an example of a few lines of code you had to put in each of those lightweight button's Click handler, but can't find it for now.

This has always been a PITA!