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).AutoIncrementSee d = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSte p = -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" <[stop_spam]@host.topdomain> wrote in message
news:uH**************@TK2MSFTNGP09.phx.gbl...

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

[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

"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@TK2MSFTNGP11.phx.gbl...

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).AutoIncrementSee d = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSte p = -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" <[stop_spam]@host.topdomain> wrote in message
news:uH**************@TK2MSFTNGP09.phx.gbl...

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#\)]

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)

"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...

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

"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@TK2MSFTNGP11.phx.gbl...

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).AutoIncrementSee d = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSte p = -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" <[stop_spam]@host.topdomain> wrote in message
news:uH**************@TK2MSFTNGP09.phx.gbl...

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

[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
"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@tk2msftngp13.phx.gbl...

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)

"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...

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

"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@TK2MSFTNGP11.phx.gbl...

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).AutoIncrementSee d = -1
Me.DataSet11.Tables(0).Columns(0).AutoIncrementSte p = -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" <[stop_spam]@host.topdomain> wrote in message
news:uH**************@TK2MSFTNGP09.phx.gbl...
> 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
>
>
>
>
>
>
>
>
>
>

[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" <no**********@planet.nl> skrev i meddelandet
news:uO*************@tk2msftngp13.phx.gbl...

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

[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


"Cor Ligthert" <no**********@planet.nl> skrev i meddelandet
news:uj**************@TK2MSFTNGP10.phx.gbl...

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

[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
"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:uE*************@TK2MSFTNGP09.phx.gbl...

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
"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@tk2msftngp13.phx.gbl...

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)

"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...

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

"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@TK2MSFTNGP11.phx.gbl...
> 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).AutoIncrementSee d = -1
> Me.DataSet11.Tables(0).Columns(0).AutoIncrementSte p = -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" <[stop_spam]@host.topdomain> wrote in message
> news:uH**************@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>

[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


"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> skrev i meddelandet
news:eK**************@TK2MSFTNGP09.phx.gbl...

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
"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:uE*************@TK2MSFTNGP09.phx.gbl...

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
"One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
news:%2****************@tk2msftngp13.phx.gbl...

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)

"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...
> 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
>
> "One Handed Man ( OHM#)" <news.microsoft.com> skrev i meddelandet
> news:%2****************@TK2MSFTNGP11.phx.gbl...
> > 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).AutoIncrementSee d = -1
> > Me.DataSet11.Tables(0).Columns(0).AutoIncrementSte p = -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" <[stop_spam]@host.topdomain> wrote in message
> > news:uH**************@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>

[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

"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:OA*************@TK2MSFTNGP10.phx.gbl... 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]" <Ja************@msn.com> wrote in message
news:eO**************@tk2msftngp13.phx.gbl...

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

"Lars Netzel" <[stop_spam]@host.topdomain> wrote in message
news:OA*************@TK2MSFTNGP10.phx.gbl...

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>>

[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
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:ev**************@tk2msftngp13.phx.gbl...

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

--

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

<<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 . . .
"Jay B. Harlow [MVP - Outlook]" <Ja************@msn.com> wrote in message
news:Ob**************@TK2MSFTNGP12.phx.gbl...

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
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message news:ev**************@tk2msftngp13.phx.gbl...

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

eroded.

:-)

--

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

<<snip>>