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."

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

Answer 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()

Answer 3

You may need this to get the Update Command

ruleTableDA.UpdateCommand = commandBuilder.GetUpdateCommand();