How do you bind SQL Data to a .NET DataGridView?

Question

I am trying to bind a table in an SQL database to a DataGridView Control. I would like to make it so that when the user enters a new line of data in the DataGridView that a record is automatically added to the database. Here is my current attempt...

    BOMClassesDataContext DB = new BOMClassesDataContext();

    Form_Load()
    {
        var mfrs = from m in DB.Manufacturers
                   select m;

        BindingSource bs = new BindingSource();
        bs.DataSource = mfrs;

        dataGridView1.DataSource = bs;
    }

    dataGridView_CellValueChanged()
    {
        try
        {
            DB.SubmitChanges()
        }
        catch(Exception Ex)
        {
            MessageBox.Show(Ex.Message);
        }
    }

If I click the bottom empty row it automatically fills in the ID (identity) column of the table with a "0" instead of the next unused value. If I change that value manually to the next available then it adds the new record fine but if I leave it at 0 it does nothing. How can i fix this? In my LINQ to SQL classes the ID column of the table has the AutoGenerate property set to true.

Answer 1

You need to execute DB.SubmitChanges() when the user has finished.

With Linq To SQL, no changes are made to the underlying database until you do that.