A VERY strange problem when Updating a Dataset

[Lars Netzel]

A little background:

I use three Datagrids that are in a child parent relation.

I Use Negative Autoincrement on the the DataTables and that's workning nice.

My problem is when I Update these grid and write to the database and I set
the new Primary Keys and related Fields to the new asigned atuonumbers in
the Access.

The following Sub is the RowUpdated for the TopGrid's Adapter that will get
the new ID and then set that as a ParentID in Datatable for the the Next
Grid.

'---------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------------------------------------------------------

Private Sub adpProjParts_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles adpProjParts.RowUpdated

If e.StatementType = StatementType.Insert Then

Dim cmd As New OleDb.OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)

Dim OldId As Long = e.Row()("PRP_ID") 'This is primary key

Dim NewID As Long = cmd.ExecuteScalar 'This is the New PrimaryKey ID

CopyDataViews()

e.Row()("PRP_ID") = NewID

e.Row().AcceptChanges()

Me.dvProjCodes_COPY.RowFilter = Nothing

Me.dvProjCodes_COPY.RowFilter = "prco_prpid=" & OldId

Dim I As Long

Dim alRows As New ArrayList

Dim drvRowIter As DataRowView

For I = Me.dvProjCodes_COPY.Count - 1 To 0 Step -1

alRows.Add(Me.dvProjCodes_COPY.Item(I))

Next

Me.dvProjCodes_COPY.RowFilter = Nothing

m_bIsSaving = True

For Each drvRowIter In alRows

drvRowIter.Row("prco_prpid") = NewID

Next

m_bIsSaving = False

End If

End Sub

'---------------------------------------------------------------------------
--------------------------

If I debug this Sub, everythign is fine and I everything seems to get the
right IDs when stepping thru it. BUT when I later check the database, One or
more of the Items in the next grid (that I just changed the Parent ID on
with this Sub) has gotten the old Negative Autonumber back.. and Of course
the relation in Access is lost.

I have been trying to figure out what rows are "changed" back to the
Negative ID and the only thing I have seen that's even remotly close, is
that it's often is the Row in the datagrid that I have my cursor on when I
trigger the Save (Update on the dataset). But that' snot completely true, it
also seems like the rows I have HAD selected and then Left also the Get
Negative ID back..

BUT.. here's the weird part.. it shouldn't matter what Rows I have selected
in the grids.. since the RowUpdated happens AFTER all that.. so I'm
completely Confused right now.

To me it seems like even though the RowUpdated Sub is working alright when
stepping thru it, some of the Items in the DataTable that I set the New
ParentID on, changes back after that...

I know it's hard to explain this problem...

PLEASE HELP!!!

/Lars Netzel

 

[One Handed Man \( OHM#\)]

You dont need any of this code.

Lets say you add some records right ?,, they will have negative PK's on the
Grid, once you run an update to the datasource, these will be assigned new
positive ID's providing your Acccess Corresponding PK is Autoincremented in
the design.

You can prove this.

1.) Create a table with two fields in it. One being the ID ( Primary key )
and set this to Autoincrement in Access.

2.) Put a grid on a form and bind the grid to the dataset.datatable.

3.) Put the following code in the Load event of the form
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSeed = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementStep = -1
Me.OleDbDataAdapter1.Fill(Me.DataSet11)

4.) Create two buttons one for Fill and One for Update put the code in for
that.
5.) Create four entries, ( see the negative keys ).
6.) Click the Update and then Load.
7.) Bingo Your four new records all have numbers 1-4 for their PK.
8.) Updating the data in those non PK keys does not affect the PK, try it.


HTH - OHM ( Terry Burns )

 

[Lars Netzel]

The PK is not the problem... it's the ParentID (foreign key) column in the
Next table that's the problem.. which does not get the "new" ID from
access...

So that's why I use the code to Catch the New ID given by Access in the
RowUpdated Event.. and then use ID to set as the Foregin Key in the next
table.. BUT here's where the troubles comes..

Some of the Rows that I "manually" set the foregin key on are fine, but the
row that is selected in the grid... for some reason get'äs the Negative
Foreign key value back before it's saved..

/Lars

You dont need any of this code.

Lets say you add some records right ?,, they will have negative PK's on the
Grid, once you run an update to the datasource, these will be assigned new
positive ID's providing your Acccess Corresponding PK is Autoincremented in
the design.

You can prove this.

1.) Create a table with two fields in it. One being the ID ( Primary key )
and set this to Autoincrement in Access.

2.) Put a grid on a form and bind the grid to the dataset.datatable.

3.) Put the following code in the Load event of the form
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSeed = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementStep = -1
Me.OleDbDataAdapter1.Fill(Me.DataSet11)

4.) Create two buttons one for Fill and One for Update put the code in for
that.
5.) Create four entries, ( see the negative keys ).
6.) Click the Update and then Load.
7.) Bingo Your four new records all have numbers 1-4 for their PK.
8.) Updating the data in those non PK keys does not affect the PK, try it.


HTH - OHM ( Terry Burns )

A little background:

I use three Datagrids that are in a child parent relation.

I Use Negative Autoincrement on the the DataTables and that's workning nice.

My problem is when I Update these grid and write to the database and I set
the new Primary Keys and related Fields to the new asigned atuonumbers in
the Access.

The following Sub is the RowUpdated for the TopGrid's Adapter that will get
the new ID and then set that as a ParentID in Datatable for the the Next
Grid.

'---------------------------------------------------------------------------

--------------------------------------------------------------------------
--
------------------------------------------------------------------------

Private Sub adpProjParts_RowUpdated(ByVal sender As Object, ByVal e As
System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles adpProjParts.RowUpdated

If e.StatementType = StatementType.Insert Then

Dim cmd As New OleDb.OleDbCommand("SELECT @@IDENTITY", e.Command.Connection)

Dim OldId As Long = e.Row()("PRP_ID") 'This is primary key

Dim NewID As Long = cmd.ExecuteScalar 'This is the New PrimaryKey ID

CopyDataViews()

e.Row()("PRP_ID") = NewID

e.Row().AcceptChanges()

Me.dvProjCodes_COPY.RowFilter = Nothing

Me.dvProjCodes_COPY.RowFilter = "prco_prpid=" & OldId

Dim I As Long

Dim alRows As New ArrayList

Dim drvRowIter As DataRowView

For I = Me.dvProjCodes_COPY.Count - 1 To 0 Step -1

alRows.Add(Me.dvProjCodes_COPY.Item(I))

Next

Me.dvProjCodes_COPY.RowFilter = Nothing

m_bIsSaving = True

For Each drvRowIter In alRows

drvRowIter.Row("prco_prpid") = NewID

Next

m_bIsSaving = False

End If

End Sub

'--------------------------------------------------------------------------- One

or
more of the Items in the next grid (that I just changed the Parent ID on
with this Sub) has gotten the old Negative Autonumber back.. and Of course
the relation in Access is lost.

I have been trying to figure out what rows are "changed" back to the
Negative ID and the only thing I have seen that's even remotly close, is
that it's often is the Row in the datagrid that I have my cursor on when I
trigger the Save (Update on the dataset). But that' snot completely

true,

it
also seems like the rows I have HAD selected and then Left also the Get
Negative ID back..

BUT.. here's the weird part.. it shouldn't matter what Rows I have selected
in the grids.. since the RowUpdated happens AFTER all that.. so I'm
completely Confused right now.

To me it seems like even though the RowUpdated Sub is working alright when
stepping thru it, some of the Items in the DataTable that I set the New
ParentID on, changes back after that...

I know it's hard to explain this problem...

PLEASE HELP!!!

/Lars Netzel

 

[One Handed Man \( OHM#\)]

How have you got these relationships defined?

You can create a relation in the dataset and bind the Details grid to this,
that way it should work.

Regards OHM (Terry Burns)

The PK is not the problem... it's the ParentID (foreign key) column in the
Next table that's the problem.. which does not get the "new" ID from
access...

So that's why I use the code to Catch the New ID given by Access in the
RowUpdated Event.. and then use ID to set as the Foregin Key in the next
table.. BUT here's where the troubles comes..

Some of the Rows that I "manually" set the foregin key on are fine, but the
row that is selected in the grid... for some reason get'äs the Negative
Foreign key value back before it's saved..

/Lars

You dont need any of this code.

Lets say you add some records right ?,, they will have negative PK's on the
Grid, once you run an update to the datasource, these will be assigned new
positive ID's providing your Acccess Corresponding PK is Autoincremented in
the design.

You can prove this.

1.) Create a table with two fields in it. One being the ID ( Primary key )
and set this to Autoincrement in Access.

2.) Put a grid on a form and bind the grid to the dataset.datatable.

3.) Put the following code in the Load event of the form
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSeed = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementStep = -1
Me.OleDbDataAdapter1.Fill(Me.DataSet11)

4.) Create two buttons one for Fill and One for Update put the code in for
that.
5.) Create four entries, ( see the negative keys ).
6.) Click the Update and then Load.
7.) Bingo Your four new records all have numbers 1-4 for their PK.
8.) Updating the data in those non PK keys does not affect the PK, try it.


HTH - OHM ( Terry Burns )



will
get

'---------------------------------------------------------------------------'---------------------------------------------------------------------------

One

or
more of the Items in the next grid (that I just changed the Parent ID on
with this Sub) has gotten the old Negative Autonumber back.. and Of course
the relation in Access is lost.

I have been trying to figure out what rows are "changed" back to the
Negative ID and the only thing I have seen that's even remotly close, is
that it's often is the Row in the datagrid that I have my cursor on

when

I true,

 

[Lars Netzel]

Tried that, gave the same result so the relations actually worked but some
rows still got the Wrong Foregn Key when written to the databasen... which
means the Negativ Foregin key that the Dataset Generated for the Parent
Table.

So I'm thinking it must be cause of something else.. or?

What about that it happens to the row that is selected in the Grid... any
ideas why it happens to that one?

I have checked to see if there are any other events triggred (like Position
Changed) and it was but those are handled with a Global Variable and they
don't do anything.. so I'm sure that the Save I do and all the Updates are
not interfreered by any other code I have written somewhere else... I
hope...

IT's really really frustrating, don't know where to turn anymore really...
and I really really need it done by today..

I Appiciate any help I can get here!

/Lars

How have you got these relationships defined?

You can create a relation in the dataset and bind the Details grid to this,
that way it should work.

Regards OHM (Terry Burns)

The PK is not the problem... it's the ParentID (foreign key) column in the
Next table that's the problem.. which does not get the "new" ID from
access...

So that's why I use the code to Catch the New ID given by Access in the
RowUpdated Event.. and then use ID to set as the Foregin Key in the next
table.. BUT here's where the troubles comes..

Some of the Rows that I "manually" set the foregin key on are fine, but the
row that is selected in the grid... for some reason get'äs the Negative
Foreign key value back before it's saved..

/Lars

on
the Autoincremented
in I
set atuonumbers
in

'---------------------------------------------------------------------------'---------------------------------------------------------------------------

get
the

right IDs when stepping thru it. BUT when I later check the

database,
One

or
more of the Items in the next grid (that I just changed the Parent

ID

on

close,

is when alright
when

 

[Cor Ligthert]

Hi Lars,

Did you do that
ds.update....
ds.clear
ds.fill.........................
I know it looks not very advanced, however it is a good start to investigage
the problem in my opinion.

Cor

 

[Lars Netzel]

Yes.. I did

And the problems come already in the Update Methid!

Also I just saw that When I have saved one time and then it reloads.. I get
the right ID's set on the PK fields.. BUT since the Autoincrement is set
to -1 it starts on the last "real" Id and then count backwards.. and the
gives me a conflict off course..

/Lars

 

[Cor Ligthert]

Hi Lars,

I did not use that -1 method yet, however when you do that, did you set
after the fill as well the seed again on -1?

Cor

 

[Lars Netzel]

No, you have to se the Seed and Step Before you fill your dataset...
otherwise it's gonna use starting values depending on the PK exsisting in
table...

Then you'll get the problem of Counting backwards from a positive number..

/Lars

 

[Jay B. Harlow [MVP - Outlook]]

Lars,
When you define the ForeignKeyConstraint (part of the DataRelation) you need
to set the ForeignKeyConstraint.UpdateRule to Rule.Cascade.

This will cause the child rows FK field to be updated to the parent row PK
field when the DataSet.Update command returns the PK from the database.

As OHM has stated, the DataSet will largely take care of all the update on
both the Child & Parent for you. You need to be certain that you define the
DataSet correctly is the Rub!

If you are only defining the DataRelation, you can use
DataRelation.ChildKeyConstraint to retrieve the ForeignKeyConstraint object
that was created as part of the DataRelation.

If I didn't the other day, I would strongly recommend you review the
relevant chapters in Sceppa's book!

For a good tutorial on ADO.NET as well as a good desk reference once you
know ADO.NET see David Sceppa's book "Microsoft ADO.NET - Core Reference"
from MS press.

Hope this helps
Jay

Tried that, gave the same result so the relations actually worked but some
rows still got the Wrong Foregn Key when written to the databasen... which
means the Negativ Foregin key that the Dataset Generated for the Parent
Table.

So I'm thinking it must be cause of something else.. or?

What about that it happens to the row that is selected in the Grid... any
ideas why it happens to that one?

I have checked to see if there are any other events triggred (like Position
Changed) and it was but those are handled with a Global Variable and they
don't do anything.. so I'm sure that the Save I do and all the Updates are
not interfreered by any other code I have written somewhere else... I
hope...

IT's really really frustrating, don't know where to turn anymore really...
and I really really need it done by today..

I Appiciate any help I can get here!

/Lars

How have you got these relationships defined?

You can create a relation in the dataset and bind the Details grid to this,
that way it should work.

Regards OHM (Terry Burns)



but
the assigned
new in
for try
it.

and

I the
Next

'---------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------

Private Sub adpProjParts_RowUpdated(ByVal sender As Object, ByVal

e

As

'---------------------------------------------------------------------------

ID close, on
when the
Get the
New

 

[Lars Netzel]

Did'nt work... still hade the selected Row in the datagrid getting the
negativ value saved in the FK field.

now we bypassed by saving everything in the database with the wrok,
negative, FK values and then ww saved the real values in an Array and then
looped thru and updated the database manually.. sort of Timepressure here!

/lars

Lars,
When you define the ForeignKeyConstraint (part of the DataRelation) you need
to set the ForeignKeyConstraint.UpdateRule to Rule.Cascade.

This will cause the child rows FK field to be updated to the parent row PK
field when the DataSet.Update command returns the PK from the database.

As OHM has stated, the DataSet will largely take care of all the update on
both the Child & Parent for you. You need to be certain that you define the
DataSet correctly is the Rub!

If you are only defining the DataRelation, you can use
DataRelation.ChildKeyConstraint to retrieve the ForeignKeyConstraint object
that was created as part of the DataRelation.

If I didn't the other day, I would strongly recommend you review the
relevant chapters in Sceppa's book!

For a good tutorial on ADO.NET as well as a good desk reference once you
know ADO.NET see David Sceppa's book "Microsoft ADO.NET - Core Reference"
from MS press.

Hope this helps
Jay

Tried that, gave the same result so the relations actually worked but some
rows still got the Wrong Foregn Key when written to the databasen... which
means the Negativ Foregin key that the Dataset Generated for the Parent
Table.

So I'm thinking it must be cause of something else.. or?

What about that it happens to the row that is selected in the Grid... any
ideas why it happens to that one?

I have checked to see if there are any other events triggred (like Position
Changed) and it was but those are handled with a Global Variable and they
don't do anything.. so I'm sure that the Save I do and all the Updates are
not interfreered by any other code I have written somewhere else... I
hope...

IT's really really frustrating, don't know where to turn anymore really...
and I really really need it done by today..

I Appiciate any help I can get here!

/Lars


in
the PK's
on

code

in and

'---------------------------------------------------------------------------

--------------------------------------------------------------------------

ByVal

e

PrimaryKey

ID

'---------------------------------------------------------------------------

to
get

the
right IDs when stepping thru it. BUT when I later check the database,
One
or
more of the Items in the next grid (that I just changed the

Parent
ID

on
with this Sub) has gotten the old Negative Autonumber back.. and Of
course
the relation in Access is lost.

I have been trying to figure out what rows are "changed" back to the
Negative ID and the only thing I have seen that's even remotly close,
is
that it's often is the Row in the datagrid that I have my cursor on
when
I
trigger the Save (Update on the dataset). But that' snot completely
true,
it
also seems like the rows I have HAD selected and then Left also the
Get
Negative ID back..

BUT.. here's the weird part.. it shouldn't matter what Rows I have
selected
in the grids.. since the RowUpdated happens AFTER all that.. so I'm
completely Confused right now.

To me it seems like even though the RowUpdated Sub is working alright
when
stepping thru it, some of the Items in the DataTable that I set the
New
ParentID on, changes back after that...

I know it's hard to explain this problem...

PLEASE HELP!!!

/Lars Netzel

 

[Jay B. Harlow [MVP - Outlook]]

Lars,
This is NOT that difficult! It should "just work"! (It "just works" for me!
)

How are you creating the DataSet?
How are you creating the DataRelation?

Are you calling DataAdapter.Update on the Parent before you call
DataAdapter.Update on the children?

sort of Timepressure here!

Understand. Sceppa's book fully explains the how & why to get it to work.
(It might be quicker to sit down with the book & review the relevant
chapter).

BTW: Reviewing Sceppa's book, the Rule.Cascade on the ForeignKeyConstraint
is the default.

Is this VS.NET 2002, VS.NET 2003, or VS.NET 2005 (the Whidbey preview)?

Hope this helps
Jay

Did'nt work... still hade the selected Row in the datagrid getting the
negativ value saved in the FK field.

now we bypassed by saving everything in the database with the wrok,
negative, FK values and then ww saved the real values in an Array and then
looped thru and updated the database manually.. sort of Timepressure here!

/lars

<<snip>>

 

[One Handed Man \( OHM - Terry Burns \)]

This is the first time I have seen the shell of your cool composure eroded.



--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

 

[Jay B. Harlow [MVP - Outlook]]

OHM,
I didn't think I was showing erosion as much as attempting to "walking
softly with a big stick" ;-)

For some reason Lars is having trouble with this, and I don't see that this
is that difficult, especially if you read Sceppa's book, as Sceppa IMHO
provides a clear explanation of what is going on. Now if Lars was
implementing IBindingList itself, I could see Lars having problems as
IBindingList is a very interesting interface to FULLY implement...

I suspect there is something simply in Lars code that he is not telling us
as to why its not working for him...

Just a thought
Jay

This is the first time I have seen the shell of your cool composure eroded.

<<snip>>

 

[One Handed Man \( OHM - Terry Burns \)]

Yeah, I thinks its one of those things, if you were there at the pc you
would probably spot it right away.

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .