someGuy someGuy - 4 months ago 85
SQL Question

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

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

Answer

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