WinForms DataGridView - update database

Question

I know this is a basic function of the DataGridView, but for some reason, I just can't get it to work. I just want the DataGridView on my Windows form to submit any changes made to it to the database when the user clicks the "Save" button.

I populate the DataGridView according to a function triggered by a user selection in a DropDownList as follows:

using (SqlConnection con = new SqlConnection(conString))
{
    con.Open();
    SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife], rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix] FROM   dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con);
    DataSet ruleTableDS = new DataSet();
    ruleTableDA.Fill(ruleTableDS);
    RuleTable.DataSource = ruleTableDS.Tables[0];
}

In my save function, I basically have the following (I've trimmed out some of the code around it to get to the point):

using (SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], 
       rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife],
       rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix]
       FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con))
    {
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(ruleTableDA);
        DataTable dt = new DataTable();
        dt = RuleTable.DataSource as DataTable;
        ruleTableDA.Fill(dt);
        ruleTableDA.Update(dt);
    }

Okay, so I edited the code to do the following: build the commands, create a DataTable based on the DataGridView (RuleTable), fill the DataAdapter with the DataTable, and update the database. Now ruleTableDA.Update(dt) is throwing the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

Solution 1

I believe there are a few problems here: The sequence to keep in mind is that when you load up your grid, it is pointed to a datatable/set already. When you type into the grid, the changes are temporarily persisted to the data table that is bound to the grid. Therefore, you do not want to be creating a data table every time you save, because the changes that were made to the existing data table are being ignored. Second issue is that you probably don't need to create a binding source everytime, because just like the first point, if the grid is showing data, there is already a binding source that it is bound to. Third problem is that the SQLCommandBuilder class has methods like GetInsertCommand, GetUpdateCommand, etc. that must be used to actually get the appropriate commands.

using (SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], 
       rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife],
       rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix]
       FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con))
    {
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(ruleTableDA);
        DataTable dt = new DataTable();
        dt = RuleTable.DataSource as DataTable;
        //ruleTableDA.Fill(dt);
        ruleTableDA.Update(dt);
    }

Solution 2

MSDN documentation states that the update/delete/insert commands are automatically set when you manually set the SelectCommand. It doesn't mention that it does the same when you construct one with a SqlDataAdapter. Try adding these lines after creating your SqlCommanduBuilder.

ruleTableDA.UpdateCommand = commandBuilder.GetUpdateCommand()
ruleTableDA.InsertCommand = commandBuilder.GetInsertCommand()
ruleTableDA.DeleteCommand = commandBuilder.GetDeleteCommand()

Comments

[Geo Ego]

I am populating the DataGridView to react to a user choice in a DropDownList. That selection triggers a function FillData(), which populate the DataGridView. I will update the question with that exact code.

[Amsakanna]

From his code it is evident that he is populating the grid manually.

[Geo Ego]

Thanks! This does create the commands. However, I'm still getting the concurrency violation exception.

[Jeremy]

You need to not fill your datatable again in the save button. Remember that we are trying to persist what exists in the current datatable to the database. You are overwriting the users changes before sending it to the database.

[Geo Ego]

You hit the nail on the head, Veer, which matches what Jeremy explains above: I'm filled the datatable and removing the user changes before updating. I took that out and it works like a charm. Thanks so much, folks.

[Geo Ego]

Thanks, Jeremy. That did it. I took out the Fill and it worked like a charm.

[TWood]

Another thing I sometimes see is after the update calling fill again to perform a manual refresh. This is sort of "proof" to the end user that their edits went out and stayed rather than being swallowed up by an exception that they did not see. It's unnecessary for most people but when you are starting out it does the job of double checking for you.