One table not updating after a few insertions

7 Jul 2006 5:19 PM

samyk

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.

8 Jul 2006 2:35 PM

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

Show quote

"samyk" <sa***@discussions.microsoft.com> wrote in message
news:E6C837E5-9218-475E-BEB9-D47B44E5EF2E@microsoft.com...
> 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.
>