473,883 Members | 1,681 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A VERY strange problem when Updating a Dataset

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_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles adpProjParts.Ro wUpdated

If e.StatementType = StatementType.I nsert Then

Dim cmd As New OleDb.OleDbComm and("SELECT @@IDENTITY", e.Command.Conne ction)

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

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

CopyDataViews()

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

e.Row().AcceptC hanges()

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.d vProjCodes_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




Nov 20 '05 #1
14 2140
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.datatab le.

3.) Put the following code in the Load event of the form
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntSeed = -1
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntStep = -1
Me.OleDbDataAda pter1.Fill(Me.D ataSet11)

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******** ******@TK2MSFTN GP09.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_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles adpProjParts.Ro wUpdated
If e.StatementType = StatementType.I nsert Then

Dim cmd As New OleDb.OleDbComm and("SELECT @@IDENTITY", e.Command.Conne ction)
Dim OldId As Long = e.Row()("PRP_ID ") 'This is primary key

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

CopyDataViews()

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

e.Row().AcceptC hanges()

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.d vProjCodes_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



Nov 20 '05 #2
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******** ********@TK2MSF TNGP11.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.datatab le.

3.) Put the following code in the Load event of the form
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntSeed = -1
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntStep = -1
Me.OleDbDataAda pter1.Fill(Me.D ataSet11)

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******** ******@TK2MSFTN GP09.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_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles adpProjParts.Ro wUpdated

If e.StatementType = StatementType.I nsert Then

Dim cmd As New OleDb.OleDbComm and("SELECT @@IDENTITY",

e.Command.Conne ction)

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

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

CopyDataViews()

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

e.Row().AcceptC hanges()

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.d vProjCodes_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




Nov 20 '05 #3

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******** ******@TK2MSFTN GP12.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******** ********@TK2MSF TNGP11.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.datatab le.

3.) Put the following code in the Load event of the form
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntSeed = -1
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntStep = -1
Me.OleDbDataAda pter1.Fill(Me.D ataSet11)

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******** ******@TK2MSFTN GP09.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_Ro wUpdated(ByVal sender As Object, ByVal e As
System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles

adpProjParts.Ro wUpdated

If e.StatementType = StatementType.I nsert Then

Dim cmd As New OleDb.OleDbComm and("SELECT @@IDENTITY",

e.Command.Conne ction)

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

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

CopyDataViews()

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

e.Row().AcceptC hanges()

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.d vProjCodes_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





Nov 20 '05 #4
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******** ********@tk2msf tngp13.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******** ******@TK2MSFTN GP12.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******** ********@TK2MSF TNGP11.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.datatab le.

3.) Put the following code in the Load event of the form
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntSeed = -1
Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntStep = -1
Me.OleDbDataAda pter1.Fill(Me.D ataSet11)

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******** ******@TK2MSFTN GP09.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_Ro wUpdated(ByVal sender As Object, ByVal e As > System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles
adpProjParts.Ro wUpdated
>
> If e.StatementType = StatementType.I nsert Then
>
> Dim cmd As New OleDb.OleDbComm and("SELECT @@IDENTITY",
e.Command.Conne ction)
>
> Dim OldId As Long = e.Row()("PRP_ID ") 'This is primary key
>
> Dim NewID As Long = cmd.ExecuteScal ar 'This is the New PrimaryKey ID
>
> CopyDataViews()
>
> e.Row()("PRP_ID ") = NewID
>
> e.Row().AcceptC hanges()
>
> 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.d vProjCodes_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
>
>
>
>
>
>
>
>
>
>



Nov 20 '05 #5
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
Nov 20 '05 #6
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**********@p lanet.nl> skrev i meddelandet
news:uO******** *****@tk2msftng p13.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

Nov 20 '05 #7
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
Nov 20 '05 #8
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**********@p lanet.nl> skrev i meddelandet
news:uj******** ******@TK2MSFTN GP10.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

Nov 20 '05 #9
Lars,
When you define the ForeignKeyConst raint (part of the DataRelation) you need
to set the ForeignKeyConst raint.UpdateRul e 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.Ch ildKeyConstrain t to retrieve the ForeignKeyConst raint 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******** *****@TK2MSFTNG P09.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******** ********@tk2msf tngp13.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******** ******@TK2MSFTN GP12.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******** ********@TK2MSF TNGP11.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.datatab le.
>
> 3.) Put the following code in the Load event of the form
> Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntSeed = -1
> Me.DataSet11.Ta bles(0).Columns (0).AutoIncreme ntStep = -1
> Me.OleDbDataAda pter1.Fill(Me.D ataSet11)
>
> 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******** ******@TK2MSFTN GP09.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_Ro wUpdated(ByVal sender As Object, ByVal e As
> > System.Data.Ole Db.OleDbRowUpda tedEventArgs) Handles
> adpProjParts.Ro wUpdated
> >
> > If e.StatementType = StatementType.I nsert Then
> >
> > Dim cmd As New OleDb.OleDbComm and("SELECT @@IDENTITY",
> e.Command.Conne ction)
> >
> > Dim OldId As Long = e.Row()("PRP_ID ") 'This is primary key
> >
> > Dim NewID As Long = cmd.ExecuteScal ar 'This is the New PrimaryKey
ID > >
> > CopyDataViews()
> >
> > e.Row()("PRP_ID ") = NewID
> >
> > e.Row().AcceptC hanges()
> >
> > 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.d vProjCodes_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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>



Nov 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
4615
by: Bill C. | last post by:
Hi, I've got a simple console app that just reads an XML file into a DataSet then prints out a description of each table in the DataSet, including column names and row values for each column. I'm getting some strange results depending the input XML file I use. I was wondering if somebody could help me understand what is going on or point me to a good reference. The code for my program looks like this:
1
1340
by: Geraldine Hobley | last post by:
hello, I have put thisup b4, but I think that people have misunderstood the problem, so I will make it clearer. I have a problem updating a dataset that is bound to a datagrid. The code that I use to set up the grid dgsimple to the dataset Mydataset and the table "Orders" is as below (see bottom of page) My problem is that I can change data in the datagrid on the first row of the grid and the changes will be immediately written away,...
10
5699
by: jaYPee | last post by:
does anyone experienced slowness when updating a dataset using AcceptChanges? when calling this code it takes many seconds to update the database SqlDataAdapter1.Update(DsStudentCourse1) DsStudentCourse1.AcceptChanges() i'm also wondering because w/ out AcceptChanges the data is still save into the database and it is now faster.
2
1830
by: Niels Jensen | last post by:
I have a some code which imports information from a text file to a dataset. Depending op what is being extracted from the text file, the dataset has 5 tables which can be written to. During this accessing I have to delete some records depending on certain criteria. I initially had some errors because I wasn't accepting the changes made to the dataset. So using .acceptchanges fixed the issue. however later on in the program a user may...
4
2382
by: Geoff | last post by:
Hi I'm hoping somebody can help me with the following problem that has occurred to me. Suppose I have two tables in an SQL Server database. Let's call these tables A and B. Assume that A has two fields: a primary key and another holding a string. In table B there are three fields: a primary key, a foreign key (which links to the primary key in A) and other field holding a string.
2
13975
by: susan.f.barrett | last post by:
Hi, Despite me being able to type the following in to SQL Server and it updating 1 row: > updatestockcategory 1093, 839 In my code, it is not updating any rows. dataSet = new DataSet();
6
2339
by: mike11d11 | last post by:
I'm trying to create an application that will have multiple users working off a table on a SQL server. Since multi users will be updating different records at any given moment, how can i get those changes and merge them into my current Dataset. I've been playing around with the GetChanges method and Acceptchanges but they just dont seem to be pulling over the changes and updating my Dataset. Maybe if someone could give me a sample of...
0
1710
by: OldStd | last post by:
Updating data using 2 data sets I am having some problems in updating the database using two datasets as suggested by someone. 1. Data is displayed in a data grid from a dataset generated using the IDE that pulls data from two tables using an . The idea is to display the associated with the field. Because an is used, the IDE couldn’t auto-generate the Insert command, update command, or Delete command. 2. As a result, it was suggested...
1
2378
by: jonbartlam | last post by:
Hi There I'm not sure what exactly is going wrong here. I'm writing an application that retreives a table from a database (tbl_internalfaults) and updates it. (Actually, just the status column will need updating with 'CLOSED' where necessary.) I have been trying to use the OLEDB command builder to build the statement to update the database however it is returning an error whenever I try to do this. I already have a dataset full of data, 1...
0
9940
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7971
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7128
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5797
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5991
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3232
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.