CODEWITHSUNDEEP
c#sql-serverdatasetinsert-updateupdatecommand
Andrew Titus
Andrew Titus

Reputation: 33

Added multiple records to DataSet but only one record is stored in database (C#)

OK this may be a bit of a long one but please bear with me. I searched high and low and I can't seem to find a clear answer about my particular issue.

As the subject above states, I am using DataSets to retrieve, edit, insert and update records in a database. However all the other functions work as expected except the insert. What happens is that when I insert new records into the data set all looks fine as using the watch feature I can see the new records in the dataset. But after I end the edit, accept the changes and do the update, only one record is sent to the database. See some code below. I have used some bindings as well.

There code is split into different functions as follows: SetAdapaterCommands, FillDataSets, SendToDatabase and BindToUI. Can anyone see anything wrong?

Or is there something about how the dataset insert and update commands work that I'm missing?

I should maybe also say that I am updating another parent table before this one. Not sure if that has anything to do with it.

Start set adapter commands

Select command

#region Select Task Command
queryString = "SELECT value1, value2, value3 FROM Table1;";

taskCommand = new SqlCommand(queryString, connection);
#endregion Select Task Command

Update command

#region Update Task Command
queryString = "UPDATE Table1 SET value1 = @value1, value2 = @value2, value3 = @value3" +
              "WHERE value1 = @value1;";

taskUpdateCommand = new SqlCommand(queryString, connection);

taskUpdateCommand.Parameters.Add("@value1", SqlDbType.Char, 10, "value1");
taskUpdateCommand.Parameters.Add("@value2", SqlDbType.Char, 10, "value2");
taskUpdateCommand.Parameters.Add("@value3", SqlDbType.VarChar, 50, "value3");

taskAdapter.UpdateCommand = taskUpdateCommand;

SqlParameter taskParameter = taskUpdateCommand.Parameters.Add("@oldValue1", SqlDbType.Char, 10, "value1");
taskParameter.SourceVersion = DataRowVersion.Original;
#endregion Update Task Command

Insert command

#region Insert Task Command
queryString = "INSERT INTO Table1 (value1, value2, value3) " +
              "VALUES (@value1, @value2, @value3);";

taskInsertCommand = new SqlCommand(queryString, connection);

taskInsertCommand.Parameters.Add("@value1", SqlDbType.Char, 10, "value1");
taskInsertCommand.Parameters.Add("@value2", SqlDbType.Char, 10, "value2");
taskInsertCommand.Parameters.Add("@value3", SqlDbType.VarChar, 50, "value3");            

taskAdapter.InsertCommand = taskInsertCommand;
#endregion Insert Task Command

End set adapter commands

Fill data set

private void loadFromDatabase()
{
    #region Load Data and From Database 
    SetAdapterCommands();

    #region Load Tasks
    try
    {
        connection.Open();
        taskAdapter.SelectCommand = taskCommand;
        taskAdapter.Fill(Table1DataSet);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    #endregion Load Tasks
}

Send changes to database

private void updateDatabase()
{
    try
    {
        Table1BindingSource.EndEdit();
        Table1DataSet.AcceptChanges();
        taskAdapter.Update(Table1DataSet);
    }
    catch(System.Exception ex)
    {
        MessageBox.Show("Update Failed");
    }
}

Bind to UI

textBoxValue1.DataBindings.Add("Text", Table1BindingSource, "value1");
textBoxValue2.DataBindings.Add("Text", Table1BindingSource, "value2");
textBoxValue3.DataBindings.Add("Text", Table1BindingSource, "value3");

Upvotes: 2

Views: 303

Answers (1)

Steve
Steve

Reputation: 216243

This is a common mistake. DataSet.AcceptChanges changes the RowState of every DataRow in every DataTable of your DataSet to the value DataRowState.Unchanged.

So if you call AcceptChanges the following call to Update doesn't find any row to update, delete or insert.

You should simply remove the call to AcceptChanges.

A bit of background. When you edit a row its RowState is changed to DataRowState.Modified (for inserts we have DataRowState.Added and for deleted rows we have DataRowState.Deleted)

The Update call of the DataAdapter searches the rows in these states to prepare and send the relative UPDATE/INSERT/DELETE to the datastore.

I think that AcceptChanges has a confusing name and many people thinks that this call is required before sending the Update but it is quite the contrary.

Upvotes: 4

Related Questions