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);    
}

You need to add insert and update commands to your data adapter — rory.ap
– rory.ap, Commented Nov 15, 2014 at 13:39
Thank you for your quick response. How and where would you use those commands, because it's not working for me. — someGuy
– someGuy, Commented Nov 15, 2014 at 13:55

Steve · Accepted Answer · 2014-11-15 15:36:14Z

3

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);
}

edited Nov 15, 2014 at 15:36

answered Nov 15, 2014 at 13:56

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

someGuy Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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

Jia Zhuang · Accepted Answer · 2016-07-14 21:47:34Z

0

You can have a save or update button to execute the code like this:

bindingSource1.EndEdit();
DataTable dt = (DataTable)bindingSource1.DataSource;   
dataAdaper.Update(dt);

You will save all changes you made, but if you sort datagridview columns first then change data then run the save code above you will miss one record, the top first one before you sort. To fix it, you have to sort it back then save them. The best way to do it if you know which column to sort, you need to sort it when you load data to datagridview.

Jia Zhuang

answered Jul 14, 2016 at 21:47

Jia Zhuang

1

Comments

user890332 · Accepted Answer · 2022-06-30 18:16:07Z

0

You need to recreate the entire SqlDataAdapter in the CellEndEdit event.

    private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
    {
        SqlConnection conn = new SqlConnection(connectionString);
        var dataAdapter = new SqlDataAdapter("select * from [table]", conn);
        var commandBuilder = new SqlCommandBuilder(dataAdapter);
        dataAdapter.UpdateCommand = new SqlCommandBuilder(dataAdapter).GetUpdateCommand();            
        bindingSource1.EndEdit();
        dataAdapter.Update((DataTable)bindingSource1.DataSource);
    }

edited Jun 30, 2022 at 18:16

answered Jun 30, 2022 at 16:46

user890332

1,35916 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related