DataAdapter.Update returns 0 and doesn't update the table in SQL Server.

DataAdapter.Update returns 0 and doesn't update the table in SQL Server.

  • Comments 2

Short one today.

 

Sometimes there are reports that the DataAdapter.Update(datatable) method returns 0 and that the backing table in the database doesn’t get updated.

How come? On the client side we can clearly see that our added and modified rows have taken effect, ie. in the datatable all looks ok.

 

Well, the most common reason is that AcceptChanges() have been called on the DataTable prior to the Update. Why does this matter you may ask.

What happens when you call the Update method on the DataAdapter is that each row in the DataTable is checked for its RowState (Added, Modfied etc) and if there is a

change to it, ie. the RowState is not Unchanged, the appropriate action is taken. If a row has been added, an insert is triggered and if a modification has taken place, then an update is triggered.

 

Since I’m better with examples than words, let’s do it that way. First create a table with some rows in SQL Server:

 

create table TestTable (id int primary key, fname nvarchar(50), lname nvarchar(50))

 

insert into TestTable values (1, 'Mike', 'Spike')

insert into TestTable values (2, 'John', 'Johnson')

insert into TestTable values (3, 'Pete', 'Peterson')

 

--drop table TestTable

 

Then create a new C# Console application and run the following code:

 

        static void Main(string[] args)

        {

            string cs = @"Data Source=<your server>;Initial Catalog=<your database>;Integrated Security=SSPI";

            string sql = "SELECT id, fname, lname FROM TestTable";

 

            using (SqlConnection con = new SqlConnection(cs))

            {

                SqlDataAdapter da = new SqlDataAdapter(sql, con);

                DataTable dt = new DataTable("Persons");

                da.Fill(dt);

 

                Console.WriteLine("Rows are untouched...");

                ViewDataTableRowStatus(dt);

 

                // Modify a row.

                Console.WriteLine("Modifying the first row...");

                dt.Rows[0][1] = "Jake";

                ViewDataTableRowStatus(dt);

 

                // Add a row.

                Console.WriteLine("Adding a row");

                dt.Rows.Add(new object[]{100, "Paul", "Paulson..."});

                ViewDataTableRowStatus(dt);

 

                // Now, if calling AcceptChanges() all rows will be set to Unchanged.

                Console.WriteLine("Calling AcceptChanges()...");

                dt.AcceptChanges();

                ViewDataTableRowStatus(dt);

 

                // This means that when we call update on the DataAdapter, it will return 0

                // since no rows has actuall been sent to the database. (first we need to build the INSERT and UPDATE commands)

                SqlCommandBuilder builder = new SqlCommandBuilder(da);

                da.InsertCommand = builder.GetInsertCommand();

                da.UpdateCommand = builder.GetUpdateCommand();

                int rows = da.Update(dt);

                Console.WriteLine("Updated rows: {0}", rows);

            }

        }

 

        private static void ViewDataTableRowStatus(DataTable dt)

        {

            foreach (DataRow dr in dt.Rows)

            {

                Console.WriteLine("Id:{0}\tFirstName: {1}\tRowState: {2}", dr[0].ToString(), dr[1].ToString(), dr.RowState.ToString());

            }

            Console.WriteLine();

        }

 

and run it, the output should be:

 

Rows are untouched...

Id:1    FirstName: Mike RowState: Unchanged

Id:2    FirstName: John RowState: Unchanged

Id:3    FirstName: Pete RowState: Unchanged

 

Modifying the first row...

Id:1    FirstName: Jake RowState: Modified

Id:2    FirstName: John RowState: Unchanged

Id:3    FirstName: Pete RowState: Unchanged

 

Adding a row

Id:1    FirstName: Jake RowState: Modified

Id:2    FirstName: John RowState: Unchanged

Id:3    FirstName: Pete RowState: Unchanged

Id:100  FirstName: Paul RowState: Added

 

Calling AcceptChanges()...

Id:1    FirstName: Jake RowState: Unchanged

Id:2    FirstName: John RowState: Unchanged

Id:3    FirstName: Pete RowState: Unchanged

Id:100  FirstName: Paul RowState: Unchanged

 

Updated rows: 0

 

So this should illustrate that when the AccepChanges() method is called, then all the rows in the DataTable is set to Unchanged. And as such, they will not be updated to the database.

Try to comment out the AcceptChanges() call, and the added and modified row should be sent to the database.

 

Some more info here:

 

".NET Framework Class Library - DataAdapter.Update Method"

http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx

".NET Framework Class Library - DataTable.AcceptChanges Method"

http://msdn.microsoft.com/en-us/library/system.data.datatable.acceptchanges.aspx

Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post
Page 1 of 1 (2 items)