ado.net weird update problem... Insert works / update doesn't

Question

Hi,

I've just started out programming with ado.net and I've got this weird update problem.

 The code is a bit messy, but it shows all of the tests I've been trying to do to get an idea of what is going wrong...

So I've started out with an example dataset which was created through the the designer

Comments have been added in the code to indicate the results. 

Can anyone give me some pointers in which direction i need to look / troubleshoot?

Thanks

J.

        DataSet1TableAdapters.CustomerTableAdapter cda = new DataSet1TableAdapters.CustomerTableAdapter();
            DataSet1.CustomerDataTable test = new DataSet1.CustomerDataTable();
            cda.Fill(test);
          
            // works normally - records are printed correctly
            foreach (DataSet1.CustomerRow cr in test)
            { 
                Console.WriteLine(cr.Company.ToString());
            }
         
            DataSet1.CustomerRow custr = test.NewCustomerRow();
            custr["Company"] = "testbbb";
            custr["Address"] = "test";
            custr["PostalCode"] = "1000";
            custr["City"] = "Blahbla";
            custr["Country"] = "Blahbla";
            custr["Telephone"] = "none";
            custr["Fax"] = "none";
            custr["E-Mail"] = "none";
            custr["Website"] = "test";
            custr["VAT"] = "test";
            custr["PaymentTermsId"] = 1;
            test.AddCustomerRow(custr);
            test.AcceptChanges();
            // this work normal - the newly added rows are shown correctly.
            foreach (DataSet1.CustomerRow cr in test)
            {
                Console.WriteLine(cr.Company.ToString());
            }
            try
            {
                int nr = cda.Update(test);
                MessageBox.Show("Update successful");
               // this is always zero  
                Console.WriteLine(nr);
            }
            catch (System.Exception ex)
            {
                 // the update never throws an error... :-(
                 MessageBox.Show("Update failed");
            }
           
            // this always works....  
            int number = cda.Insert("testccc", "test", "1000", "gent", "belgium", "none", "none", "none", "none", "none", 1);
             
            Console.WriteLine(number);
            cda.Update(test);
            // all these commands look normal   
            Console.WriteLine(cda.Adapter.UpdateCommand.CommandText);
            Console.WriteLine(cda.Adapter.InsertCommand.CommandText);

Answer 1

The problem is the .AcceptChanges() that you've done after adding all those rows. AcceptChanges() marks all rows as Unchanged, but the Adapter.Update() expects there to be rows marked as Modified or Inserted. Get rid of that AcceptChanges() and you'll see what I mean ...

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 2

Thanks for the help, very clearly explained and obvious when you hear it ;-)