One table not updating after a few insertions

[Guest]

Hello All,

I'm having this problem which deals with 2 tables in a database. Both tables
have a primary key set as an autonumber but also both tables are dependent on
each other by having their primary key as a value in their tables. The
problem is after a few insertions of records into the event table the
rowcount always stays the same. When I open the event table a new row is
present and the new autonumber is there. The new value of the 'headerid' from
the HeadOrder table overwrites the previous 'headerid' record in the Event
table. Could it be because of the autonumber?
The eventid autonumber starts at 39 while the headerid autonumber starts at
49 and increments by 1. When the new row in the Event table 'eventid' reaches
46, the headerid overwrites the previous record of 55 and becomes 56 but the
headerid at new row stays empty.

Example of the table Event

before new row is inserted
EventID HeaderID
29 39
30 40
.. .
45 55

after new row is inserted
EventID HeaderID
29 39
30 40
.. .
45 56 'The value of HeaderID is overwritten whereas it
should of
46 been inserted in the next record

here is the code where this happens:

'Fill required headerid to event table
dbInventaire.Open()
sqlstring = "select * from Event"
updater2 = New OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
CmdBuilder = New OleDb.OleDbCommandBuilder(updater2)
CmdBuilder.QuotePrefix = "["
CmdBuilder.QuoteSuffix = "]"
updater2.UpdateCommand = CmdBuilder.GetUpdateCommand
Try
updater2.Fill(ds2, "Event")
table2 = ds2.Tables.Item(0)
row2 = table2.Rows(table2.rows.count - 1) 'Problem is in this line
row2("HeaderID") = CInt(headeridx)
updater2.Update(ds2, "Event")
dbInventaire.Close()
Catch ex As Exception
MsgBox(ex.Message)
dbInventaire.Close()
EndTry



I'm using VB.NET 2005 with ADO.NET and MS Access 2003.

 

[Cowboy \(Gregory A. Beamer\)]

Where are you inserting the row? I do not see it in the code you have here.
Is this row fully committed to the database before you run this strange
code? Why did you not go ahead and set the HeaderID at the time you
originally inserted the row?

The idea of adding a row and then later updating leaves the database in an
inconsistent state, which is not wise. You are better to ensure the new row
is correct from the very beginning.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************