loading Dataset from one connection and insert those rows to a table from different connection

[Anton Sommer]

Hello folks,



I am loading the Dataset from one connection, do a few changes to a few rows
and want to insert all of those rows into another table in another
connection. the problem is that the primarily loaded rows have rowstate
unchanged, the ones that get changed have state changed but non of them can
be inserted into another table because this would require rowstate added.
What can I do?

The rowstate is read only. Am I right?
Can I make the DataAdapter insert rows with rowstate different from
inserted?
Or would I have to manually build a table and set all values in a loop?
Sounds stupid to me!?!

Any other ideas?


Thank you

Anton

 

[Cor]

Hi Anton,

I think that this is a nice page for you?

http://msdn.microsoft.com/library/d.../cpguide/html/cpconmergingdatasetcontents.asp

Cor

 

[Marius Horak]

We have one project with Merge. It's so sloooow.
By the way I could not see any transaction in the MS example.
So if there is an error somewhere will Merge rollback all changes or none?

MH

 

[Anton Sommer]

Thank you Cor,

but on that page it says that merge will not change modified/unchanged to
added row state what I would need

Thank you anyway

Anton

 

[Cor]

Hi Anton

I knew that William had answered a kind of this question short ago.

http://tinyurl.com/2p6wx

Cor

 

[William Ryan eMVP]

Anton:

If I undestand your problem correctly, you want to query database a and fill
a datatable with it. Then, you want to change a few things and submit all
of the changes to Database b ? If that's the case and you want all of the
rows in Database a to be inserted into B, then set the
AcceptChangesDuringFill property to false on the first DataAdapter right
before fill. This will not set the rowstate to unchanged so the changes
will be reflected as Inserted. As long as your second DataAdapter has a
valid insert command, they will be inserted in the second table, along with
any mods you made before the update.

HTH,

Bill

 

[Anton Sommer]

Hello William thank you for responding,

If I undestand your problem correctly, you want to query database a and fill
a datatable with it. Then, you want to change a few things and submit all
of the changes to Database b ?

Well I want to query Database A and fill it into a datatable. I might do
changes to the datatable or not, and then I want to insert all rows of the
datatable into database B although the rowstates of the datatable are only
unchanged and modified, added does not exist. My problem is that you can not
insert tables that have a rowstate other then added.

I hope it is more clear now.

Thank you


Anton

 

[William Ryan eMVP]

Anton:

Before the fill, set AcceptChangesDuringFill to false, the default is true.
This will cause the Rowstate or everything you just filled to be Added, even
if you modify them, they'll be added and the changes will go back to the db.

This code loads a Dataset on Button_Load which in this case has 203 records.
The Destination Table (the query is exactly the same in my dataadapter (both
of them except the table name) has 0 records at this point. Then, I call
Update on the Second DataAdapter and the Destination table know has all of
the rows 203. If I modify anything , some or all or none, it'll have all
203 exaclty like I changed them b/c the rowstate is added so it doesn't do
any checking for concurrency:
The whole trick is setting AcceptChangesDuringFill to false before
DataAdapter1.fill

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnImport.Click
MessageBox.Show("DataSet currently has Changes: " &
ds.HasChanges.ToString) True
Dim i As Integer = daDestination.Update(ds, "TransferData")
Label2.Text = "Transferred Records: " & i.ToString'203

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
daSource.AcceptChangesDuringFill = False
End Sub

Private Sub btnLoadData_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoadData.Click
daSource.Fill(ds, "TransferData")
MessageBox.Show("Source Table has " & ds.Tables(0).Rows.Count.ToString
& " rows")
Label1.Text = "Source Table: " & ds.Tables(0).Rows.Count.ToString

End Sub

 

[William Ryan eMVP]

P.S> I'll have an article up doing everything you mention on KnowDotNet
around 8:00 pm tonight. This seems common enough of a problem that it's
worth writing about.

Bill

 

[Anton Sommer]

Thanks William,

you really helped me, thank you


Anton

 

[William Ryan eMVP]

Glad to be of help ;-)