updating from one data source to another

A common scenario I run into is to grab data from one data source and place
it in a dataset, modify the data, and then insert the data into another
datasource. The problem is that when I use the SqlDataAdapter's update
command it doesn't work because the Row versions are all set to Unchanged or
Modified and not New so the insert commands never get run. The row version
property is read only so it can't be changed. Is there a good work around
for this?
Thanks!

Jason

Joe
Sat Jul 12 14:50:39 CDT 2003

I have the same problem.
It would be so much simpler if we could change the property from Modified to
New (or vice versa.)

I have hacked up a workaround which is inelegant.

Any other ideas?
--
Joe Fallon




"Jay Nesbitt" <jay@iconologic.RemoveThis.com> wrote in message
news:OdO9VBlRDHA.2676@TK2MSFTNGP10.phx.gbl...
> A common scenario I run into is to grab data from one data source and
place
> it in a dataset, modify the data, and then insert the data into another
> datasource. The problem is that when I use the SqlDataAdapter's update
> command it doesn't work because the Row versions are all set to Unchanged
or
> Modified and not New so the insert commands never get run. The row
version
> property is read only so it can't be changed. Is there a good work around
> for this?
> Thanks!
>
> Jason
>
>

Baskar
Sat Jul 12 21:20:45 CDT 2003

Use AcceptChangesDuringFill Property of the DataAdapter object...Hope this
helps..

This is the excerpt from the ADO.NET reference book...
When I worked as a support engineer helping developers who were having
problems using ADO, I was amazed at how many developers were trying to use
ADO as some sort of data synchronization tool. They would query one database
and then point their Recordset at a different database and call Update,
expecting that ADO would synchronize the tables in the two databases. ADO
could not do that. But ADO.NET can.sort of.

The DataAdapter object has an AcceptChangesDuringFill property that accepts
a Boolean value. This property, which is set to True by default, controls
the RowState of the rows retrieved by the DataAdapter. If the property is
set to True, the new DataRow objects will each have a RowState of Unchanged.
Setting AcceptChangesDuringFill to False causes the new DataRow objects to
have a RowState of New.

This means that if you set AcceptChangesDuringFill to False, you can query a
table in one database and then pass the DataSet to a DataAdapter that's set
to communicate with another database and insert all of the newly retrieved
rows into this other database.

"Jay Nesbitt" <jay@iconologic.RemoveThis.com> wrote in message
news:OdO9VBlRDHA.2676@TK2MSFTNGP10.phx.gbl...
> A common scenario I run into is to grab data from one data source and
place
> it in a dataset, modify the data, and then insert the data into another
> datasource. The problem is that when I use the SqlDataAdapter's update
> command it doesn't work because the Row versions are all set to Unchanged
or
> Modified and not New so the insert commands never get run. The row
version
> property is read only so it can't be changed. Is there a good work around
> for this?
> Thanks!
>
> Jason
>
>

davidsc
Mon Jul 14 13:40:12 CDT 2003

Joe,

You could set the DataAdapter's InsertCommand to execute a
query like the following:

IF NOT EXISTS(SELECT ID FROM MyTable WHERE ID = @ID)
BEGIN
-- Insert Scenario
INSERT INTO MyTable (...) VALUES (...)
END
ELSE
BEGIN
-- Update Scenario
UPDATE MyTable SET ... WHERE ...
END

Of course, placing this logic in a stored procedure and
calling the stored procedure from the InsertCommand would be an
even better idea.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

Joe
Mon Jul 14 20:45:18 CDT 2003

David,
Thanks for the hint.

Just finished your book on ADO.Net.
Very well written and very useful.
--
Joe Fallon



"David Sceppa" <davidsc@online.microsoft.com> wrote in message
news:nvNscdjSDHA.1636@cpmsftngxa06.phx.gbl...
> Joe,
>
> You could set the DataAdapter's InsertCommand to execute a
> query like the following:
>
> IF NOT EXISTS(SELECT ID FROM MyTable WHERE ID = @ID)
> BEGIN
> -- Insert Scenario
> INSERT INTO MyTable (...) VALUES (...)
> END
> ELSE
> BEGIN
> -- Update Scenario
> UPDATE MyTable SET ... WHERE ...
> END
>
> Of course, placing this logic in a stored procedure and
> calling the stored procedure from the InsertCommand would be an
> even better idea.
>
> I hope this information proves helpful.
>
> David Sceppa
> Microsoft
> This posting is provided "AS IS" with no warranties,
> and confers no rights. You assume all risk for your use.
> © 2003 Microsoft Corporation. All rights reserved.
>