[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5890
  • Last Modified:

How to save a DataTable using a DataTableAdapter

Any hints on why this doesn't save any records to the database table?

/gustav
VrsSourceTableAdapters.DataTableCustomerTableAdapter customerAdapterS =
                    new VrsSourceTableAdapters.DataTableCustomerTableAdapter();
                VrsSource.DataTableCustomerDataTable customersS;
 
                VrsTargetTableAdapters.DataTableCustomerTableAdapter customerAdapterT =
                    new VrsTargetTableAdapters.DataTableCustomerTableAdapter();
                VrsTarget.DataTableCustomerDataTable customersT;
 
                customersS = customerAdapterS.GetDataVrsCustomers();
                customersT = customerAdapterT.GetDataVrsCustomers();
 
 
                foreach (VrsSource.DataTableCustomerRow customerRowS in customersS)
                {
                    Console.WriteLine("Customer: " + customerRowS.name + "");
                    Console.WriteLine("  CustomerNo: " + customerRowS.customerNo + "");
                    customerRowS.SetModified();
                    customersT.ImportRow(customerRowS);
                }
 
                Console.WriteLine("s: " + customersS.Count.ToString());
                Console.WriteLine("t: " + customersT.Count.ToString());
 
                foreach (VrsTarget.DataTableCustomerRow customerRowT in customersT)
                {
                    Console.WriteLine("TCustomer: " + customerRowT.Name + "");
                    Console.WriteLine("  TCustomerNo: " + customerRowT.CustomerNo + "");
                    Console.WriteLine(" Error: " + customerRowT.HasErrors.ToString());
                }
    
                //customersT.AcceptChanges();
                //customerAdapterT.Update(customersT);
                customerAdapterT.FillVrsCustomers(customersT);

Open in new window

0
Gustav Brock
Asked:
Gustav Brock
  • 4
  • 2
2 Solutions
 
Gustav BrockCIOAuthor Commented:
I should add, that the purpose is to read records from one database/table to another database/table row by row as some conditions (not shown) are to be met for a row to be copied.

Target engine is SQL Server 2005.
IDE: VS2005 Standard.

/gustav
0
 
SolutionsCSCommented:
For my self i would use a dataset wich you would fill with your table adapter.
When finished copying from one table to the others from the dataset use accept change and update from from the dataset.  For me it's work perfectly.
0
 
Gustav BrockCIOAuthor Commented:
So you fill a DataSet and not a DataTable?
And then call AcceptChanges and then update of the DataTableAdapter?

/gustav
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
SolutionsCSCommented:
yes, on your dataset you will fill the date associate with it.
YourTableAdapter1.fill(Yourdataset.Yourtable1)
YourTableAdapter2.fill(Yourdataset.Yourtable2)

YourDataset.AcceptChanges
YourDataset.Update.

This will update entire dataset.
You can do it on specific table in it too.

0
 
Gustav BrockCIOAuthor Commented:
That would be nice but also represents the problem - I can't find an Update method for neither a DataSet nor a DataTable.

Only for the DataTableAdapter it seems to exist and that "doesn't work" (no errors, but no appending of records) for me with a DataTable.
Now I'll try to work with a DataSet ...

/gustav
0
 
Gustav BrockCIOAuthor Commented:
Oh well, make a note on this should you ever wish to copy data between DataSets or DataTables with method ImportRow.

The secret is the value of property RowState of the individual rows. Rows read from a table have a RowState of Unchanged.
If a row is copied to another DataSet/DataTable by method ImportRow, RowState remains Unchanged.

In my code sample, I marked rows as Modified with method SetModified.

The Update method of the DataTableAdapter, which is called to write the data rows to the underlying table, is quite clever as it doesn't touch rows with RowState Unchanged.
However, rows with RowState Modified will only be used to update existing records in the target table, thus it will not attempt to append or insert any such row.

For a row to be inserted by the later Update call, it has to have RowState set to Added.

So, for my code below, I have to decide if a row should update an existing record, or if it should be appended, or if it should be ignored - and then call the method SetModified or SetAdded or nothing:

                    // Mark row as modified.
                    customerRowS.SetModified();
or:
                    // Mark row as new.
                    customerRowS.SetAdded();

When I changed the code from SetModified to SetAdded and replaced the Fill method with:

      customerAdapterT.Update(customersT);

all records were written to the target table.

Of course, if you can write your code to add rows not using method ImportRow - with LoadDataRow or like:

                    customersT.Rows.Add(rowToAdd);

RowState of those rows, I guess, will be switched to Added automatically.

/gustav
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now