I’m trying to update one row of a SQL database from a DataTable.
In Variables.cs I have
public static DataSet PeopleDS = new DataSet();
public static DataTable PeopleTable = PeopleDS.Tables.Add("PeopleTable");
public static SqlDataAdapter PeopleDA;
In the main body I have:-
Variables.PeopleDA = new SqlDataAdapter();
Variables.PeopleDA.SelectCommand = new SqlCommand ("SELECT * FROM People" ,con0);
Variables.PeopleDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
Variables.PeopleDA.Fill(Variables.PeopleDS, "People");
Variables.PeopleTable = Variables.PeopleDS.Tables["People"];
After a selected person is found that row of the table is read into a DataRow variable:
SelectString = "PersonID = " + WBID.ToString();
foundrows = Variables.PeopleTable.Select(SelectString);
ThisRow = foundrows[0];
And values passed to a form:
textBox1.Text = ThisRow["FullName"].ToString();
textBox3.Text = ThisRow["PersonalAddress"].ToString();
textBox4.Text = ThisRow["EmailAddress"].ToString();
textBox5.Text = ThisRow["EmailAddress2"].ToString();
textBox6.Text = ThisRow["Details"].ToString();
textBox7.Text = ThisRow["EmployeeNo"].ToString();
etc etc.
This form can then be edited and any changed values are passed back to a DataRow:
ThisRow = Variables.PeopleTable.Select("PersonID=" + textBox17.Text).FirstOrDefault();
ThisRow["Title"] = comboBox2.SelectedItem;
ThisRow["CompanyID"] = Convert.ToInt16(comboBox3a.SelectedValue.ToString());
Etc etc
I’ve checked and so far so good, the DataTable has the changed values.
I can’t see how to run a SQL update for just this record – to find the correct row in the SQL table and simply copy the DataRow back. Having to write a SQL UPDATE statement listing every field would make DB changes require code changes.
What I have tried:
Started writing the complete UPDATE statement before realising its problems
Tried Variables.PeopleDA.Update(Variables.PeopleDS) -this gives System.InvalidOperationException