How to update SQL Server database using Datagridview binding source C#

Question

I'm writing a Winforms app in C# that enables the user to edit and update database using the datagridview.

The problem is, I can't make it to work. The only thing that I managed to achieve is updating what the datagridview is showing, but when I go into the database table, there is no change in the data. I have search a lot of sites discussing about that problem but nothing yet worked for me.

Things I have tried so far:

• binding the database to the datagridview
• changing the Copy to Output Directory property to Copy if newer
• using the dataAdapter.Update((DataTable)bindingSource1.DataSource) with or without any update query execute.
• execute update query without dataAdapter.update(...).

Here is my code:

public Form1()
{
    InitializeComponent();
    GetData("SELECT * FROM Table1");
}

void GetData(string selectCommand)
{
    SqlConnection conn = new SqlConnection(Properties.Settings.Default.NewDBTESTConnectionString);

    dataAdapter = new SqlDataAdapter(selectCommand, conn);
    commandBuilder = new SqlCommandBuilder(dataAdapter);

    table = new DataTable();
    dataAdapter.Fill(table);

    bindingSource1.DataSource = table;
    dataGridView1.DataSource = bindingSource1;
}

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
        dataAdapter.Update((DataTable)bindingSource1.DataSource);    
}

Solution

Without calling bindingSource1.EndEdit your underlying DataTable doesn't see any change and thus the Update command has nothing to update.

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
    bindingSource1.EndEdit();
    DataTable dt = (DataTable)bindingSource1.DataSource;

    // Just for test.... Try this with or without the EndEdit....
    DataTable changedTable = dt.GetChanges();
    Console.WriteLine(changedTable.Rows.Count);

    int rowsUpdated = da.Update(dt);    
    Console.WriteLine(rowsUpdated);
}

Comments

[someGuy]

Thank you for your response. I just tried your suggestion and it did saved the changes, but when i look at the database it doesn't show the update. only when i run the program I see my changes in the datagridview, not the databse.

[Steve]

What database are you looking at? If you use the Server Explorer window check if you have the same connection string. Sometimes the ServerExplorer is connected to the MDF file in the project folder while the program works on the one in the BIN\DEBUG folder.

[someGuy]

Yes, you were right! I'm using the Server Explorer window. now that I have changed it to the connection string it gives in its properties, instead of what i did (Properties.Settings.Default.NewDBTESTConnectionString),i saw the update on the database as well! thank you so much!

[user890332]

Just note that you will need to create a new sqlconnection, sqlcommand and SQLdataadapter here. The SQLdataadapter from the original bind has been closed.