Database update/save problem
I have a form that is connected to an sql database, and uses a bindingnavigator to scroll through records, add/delete/save etc. (shows data in text boxes on the form)
My problem is that when I attempt to change any of the data, I will get the following error when attempting to save: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
I've tried messing around with it a bit, like I tried just updating that specific table instead of updateall, and that looked like it worked but it never actually saved when i reopened the program. I don't really understand how to create a "valid updatecommand" so hopefully someone could explain this to me..
Thanks
Re: Database update/save problem
If you use strongly typed dataset so try this
Code:
private void saveAll()
{
// check changes on client side
if (dt.GetChanges() == null)//<-- dt is datatable
return;
// get command builder relative to dataadapter (da) settings:
SqlCommandBuilder cb = new SqlCommandBuilder(da);
// bypass of parallelism error
cb.ConflictOption = ConflictOption.OverwriteChanges;
// updating database
da.Update(dt);
}
private void saveToolStripButton_Click(object sender, EventArgs e)
{
if (dt.GetChanges() == null)//<-- dt is datatable
return;
if (!this.Validate())
{
MessageBox.Show("problem with validating input. Reject changes");
return;
}
else
{
bind.EndEdit();
saveAll();
}
}
Otherwise you might use a parametrizied query:
Code:
private void saveToolStripButton_Click(object sender, EventArgs e)
{
if (!this.Validate())
{
MessageBox.Show("problem with validating input. Reject changes");
return;
}
else
{
bind.EndEdit();
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = insertQuey;
//bind is BindingSource object, that is used as DataSource of BindingNavigator:
cmd.Parameters.AddWithValue("@FirstName", ((DataRowView)bind.Current).Row.ItemArray[1].ToString());
cmd.Parameters.AddWithValue("@LastName", ((DataRowView)bind.Current).Row.ItemArray[2].ToString());
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
Re: Database update/save problem
Quote:
Originally Posted by
Specter93
I have a form that is connected to an sql database, and uses a bindingnavigator to scroll through records, add/delete/save etc. (shows data in text boxes on the form)
My problem is that when I attempt to change any of the data, I will get the following error when attempting to save: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
I've tried messing around with it a bit, like I tried just updating that specific table instead of updateall, and that looked like it worked but it never actually saved when i reopened the program. I don't really understand how to create a "valid updatecommand" so hopefully someone could explain this to me..
Thanks
Google is your friend here. Most likely you have not generated an update statement hence the exception
Update requires a valid UpdateCommand
http://msdn.microsoft.com/en-us/libr...wy(VS.80).aspx
Go here MDSN Social Forums which is a link to a solution and while there look at related topics on the right side of the page.
Re: Database update/save problem
Quote:
Originally Posted by
kevininstructor
If I try to check the option to refresh the database like that thread recommends, when I click finish it never actually saves what I selected, and if I look at the advanced options again the refreshed option is still not checked..
Re: Database update/save problem
Almost certainly, it's an issue relating to a primary key. If you are using the Data Source wizard or a CommandBuilder, in order to have DELETE and UPDATE statements generated automatically, your query must include one table only, that table must have a primary key and your query must return that primary key. Does your query satisfy those conditions?