Gustav Brock
Gustav at cactus.dk
Thu Feb 7 05:35:32 CST 2008
Hi Charlotte and Stuart
That's a good idea - if the code works. I have not much hair left after having browsed thousands of sites (so it feels). I thought it would be a simple job to locate code somewhere around that I could modify, but no. Well, yes, but that is basic code snippets working with connection strings and SQL commands and so on for ASP kiddies. This must be very special, though I thought it to be some kind of standard routine. But no.
So again, should you ever have a task like the subject to do, make a note of this method.
Main issue is that the DataTableAdapter wizard creates a buggy SQL Update command which raised the error when you finally call the Update method of the DataTableAdapter:
"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."
The key to solve this I found here:
http://geekswithblogs.net/gaijin42/archive/2007/09/05/update_dataset_with_new_rows_acceptchanges_setmodified_orm_business_objects.aspx
During testing I received first the "Concurrency violation" message and only a Select statement was found for the Update SQL command.
Later - somehow - the wizard generated the full Update SQL command with a bunch of parameters - and that failed too.
Then I edited the Update SQL as described by Jason to include the parameter for the primary key only. That worked!
Finally, I found that the simplest method to copy a row of one type to a row of another type (remember, source and target are from different DataTableAdapters) is to copy the ItemArray.
So now I can update and append to the target rows from the source rows in one go with this code:
// DataTableAdapter to copy from.
VrsSourceTableAdapters.DataTableKundeTableAdapter kundeAdapterS =
new VrsSourceTableAdapters.DataTableKundeTableAdapter();
VrsSource.DataTableKundeDataTable kunderS;
// DataTableAdapter to copy to.
VrsTargetTableAdapters.DataTableKundeTableAdapter kundeAdapterT =
new VrsTargetTableAdapters.DataTableKundeTableAdapter();
VrsTarget.DataTableKundeDataTable kunderT;
// DataTable for source.
kunderS = kundeAdapterS.GetDataVrsKunder();
// DataTable for target.
kunderT = kundeAdapterT.GetDataVrsKunder();
// Read each row from the source rows.
// Update those found in the target table.
// Append those not found in the target table.
// Variable to hold primary key to locate.
Int32 key;
// Create row to hold a found row in the target.
VrsTarget.DataTableKundeRow foundRow = null;
foreach (VrsSource.DataTableKundeRow kundeRowS in kunderS)
{
// Value of primary key to locate.
key = kundeRowS.kundeNr;
// Find a matching row in the target.
foundRow = kunderT.FindByKundeNr(key);
if (foundRow == null)
{
// Row is not found.
// Set the RowState of the source row as Added.
kundeRowS.SetAdded();
// Append it to the target preserving the RowState.
kunderT.ImportRow(kundeRowS);
}
else
{
// Row is found.
// Copy full content from source row to target row.
// This sets RowState of the target row to Modified.
foundRow.ItemArray = kundeRowS.ItemArray;
// Do NOT call AcceptChanges here as this would clear property RowState.
}
}
Console.WriteLine("s: " + kunderS.Count.ToString());
Console.WriteLine("t: " + kunderT.Count.ToString());
// Write back to the database table the updated target DataTable.
kundeAdapterT.Update(kunderT);
// Call AcceptChanges if needed for further processing.
// kunderT.AcceptChanges();
Still, if anyone have a better method, I'm all ears. This is by no means an expert area for me.
/gustav
>>> cfoust at infostatsystems.com 07-02-2008 02:25 >>>
I've fallen over that one before too, and the best way to avoid that is
to put the code in a public helper class and then call it when you need
to do that task.
Charlotte Foust
-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Wednesday, February 06, 2008 3:14 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] Copy tables
Glad it's not just me trying to get my head around .Net and data at the
moment. :-) After 15 years of Access and classic VB development, there's
so much new stuff to learn here :-(
On 6 Feb 2008 at 14:30, Gustav Brock wrote:
> Hi Stuart et al
>
> Oh my, 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 below, 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.
> kundeRowS.SetModified();
> or:
> // Mark row as new.
> kundeRowS.SetAdded();
>
> When I changed the code from SetModified to SetAdded and replaced the Fill method with:
>
> kundeAdapterT.Update(kunderT);
>
> 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:
>
> kunderT.Rows.Add(rowToAdd);
>
> RowState of those rows, I guess, will be switched to Added automatically.
>
> /gustav
>
>
> >>> Gustav at cactus.dk 05-02-2008 18:02 >>>
> Hi Stuart et al
>
> No there is no GUI, just code.
> I have some code like this. It add rows but will not save them to the database table:
>
> VrsSourceTableAdapters.DataTableKundeTableAdapter kundeAdapterS =
> new VrsSourceTableAdapters.DataTableKundeTableAdapter();
> VrsSource.DataTableKundeDataTable kunderS;
>
> VrsTargetTableAdapters.DataTableKundeTableAdapter kundeAdapterT =
> new VrsTargetTableAdapters.DataTableKundeTableAdapter();
> VrsTarget.DataTableKundeDataTable kunderT;
>
> kunderS = kundeAdapterS.GetDataVrsKunder();
> kunderT = kundeAdapterT.GetDataVrsKunder();
>
> foreach (VrsSource.DataTableKundeRow kundeRowS in kunderS)
> {
> Console.WriteLine("Kunde: " + kundeRowS.navn + "");
> Console.WriteLine(" Kundenr: " + kundeRowS.kundeNr + "");
> // Mark row as modified.
> kundeRowS.SetModified();
> kunderT.ImportRow(kundeRowS);
> }
>
> Console.WriteLine("s: " + kunderS.Count.ToString());
> Console.WriteLine("t: " + kunderT.Count.ToString());
>
> kundeAdapterT.FillVrsKunder(kunderT);
>
> Target table is residing in MSDE.
> Count of s returns the count of records to add.
> Count of t returns existing records + count of records to add.
>
> What am I missing?
>
> /gustav
>
>
> >>> stuart at lexacorp.com.pg 05-02-2008 00:06:55 >>>
> Do you mean have the user physically browse through the rows looking for specific records?
>
> If that is the case, I would probably go with a DatagridView bound to the source data.
> When the user selects a row (double click, highlight and click a button or whatever), read the
> row data and feed it to a TableAdapter.Insert on the target table.
>
>
> On 4 Feb 2008 at 18:41, Gustav Brock wrote:
>
> > Hi all
> >
> > I have created two tableadapters, a source and a target, and need to
> > browse through the source looking for rows and, when a row meeting
> > some conditions is found, copy this to the target. What is the best
> > method?
> >
> > /gustav