WinForms DataGridView - update database
- by Geo Ego
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."
