Update Multiple table using Dataset

[mdhaman]

Hi,

I have an issue in updating the Primary Key in the Parent table. I have set
of three tables (Database SQL2000, VB.NET), where A is related to B and B is
related to C. I have written a stored procedure which returns all the
records in these tables and fill up a single DataSet. All these table have
primary key which is an Identity Column. The dataset then populates the
three grids in the client application. The users can add/update/delete the
values in all the three tables and there is single save button to do the
updates.

I am code I have created DataRelation between table A,B and C. Also when I
add a new add to the child table, I set the parent row as well. All these is
working fine. I am using the example as described in the CodeProject article
http://www.codeproject.com/cs/database/relationaladonet.asp?df=100&forumid=14883

But my problem here is when I add a row in the Parent table say A and then
add new rows in the child table B. The new generated ID in the Parent table
is not propagated to the rows in the child table.
I am using RowUpdated event to capture get the latest ID returned from the
stored procedure as output parameter. I use the latest ID to update the
datatable.

When I debug the application, I can retrieve the new ID in the RowUpdated
event and set it back to the datarow:
e.Row.Item("ColumnName") = newID.
e.Row.AcceptChanges()

But these changes are not seen in the dataset. It still has same old ID but
in the DEBUG mode the e.row has the new value.

Moreover, its an issue because the event arguments are passed as Byval and
not as ByRef. i.e. I am not getting the actual reference to the DataRow.

Has anybody faced this problems before? Please help...

 

[Sahil Malik [MVP]]

But my problem here is when I add a row in the Parent table say A and then

add new rows in the child table B. The new generated ID in the Parent
table
is not propagated to the rows in the child table.
I am using RowUpdated event to capture get the latest ID returned from the
stored procedure as output parameter. I use the latest ID to update the
datatable.

I've honestly tried to understand your problem and I cannot. There are a lot
of holes in your both your problem description and your understanding of how
stored procedures/datadapter.update work -

So where exactly and how exactly are you adding a row? - Client side
rows.add? or during update - if so, is there a fill right after update?
RowUpdated event - so I take it you're using MS Access? But why stored
procedure/output parameter? So is it not MS Access?
What .NET framework are we talking 1.1 or 2.0?
Why do you have to manually update the latest id to update the data table?
Why not just set sourcecolumn on the relevant sql parameter?
What is Table[0] out of A,B and C?
What are the data relations - who is the parent, who is the child?

The users can add/update/delete the
values in all the three tables and there is single save button to do the
updates.

And you expect DataAdapter to take care of that? What do your
insert/update/delete commands look like? How are you iterating through
various data relations?

When I debug the application, I can retrieve the new ID in the RowUpdated
event and set it back to the datarow:

And why do you have to do this manually .. for SQL Server/output parameter,
you shoudln't have to.

But these changes are not seen in the dataset. It still has same old ID
but
in the DEBUG mode the e.row has the new value.

So you set a Row's value to something new, then called AcceptChanges, and
then checked the value and it didn't change? Thats impossible.

Moreover, its an issue because the event arguments are passed as Byval and
not as ByRef. i.e. I am not getting the actual reference to the DataRow.

Okay great - DataRow is an object, ByVal / ByRef both change the actual
datarow.

.... and many other such questions before I can help you with a definitive
answer - which truthfully speaking needs me to look at your code which will
be quite expansive. And before you copy paste your code, I'd recommend
looking up a good book that explains updating hierarchical and single table
data. I'd recommend either my book or David Sceppa's book. What you are
trying to do - a 3 level hierarchy being saved into the database using one
single "save" button - is exactly the same example I choose to describe in
Chapter 10 of my book. It is in many pages and simply impossible for me to
type out in a short concise way here.

Sorry couldn't be of much help :-/, but I really think you are missing more
than one thing in your understanding of the dataadapter.update, and you
truly need a book to guide you through this mess instead of a one quick help
here on the newsgroups.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------