Problem in getting autonumber value

S

samyk

New member
Joined
Jul 6, 2006
Messages
2
Programming Experience
3-5
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 when I add new rows to get the autonumber in order to put it to the other table the autonumber is a null but when I check the database the new row is there with the new autonumber. Here is the code that I have been working on. Can someone spot a problem that I am not aware of, thanks.
VB.NET: 
[SIZE=2][COLOR=#008000]'Insert new header data
[/COLOR][/SIZE][SIZE=2]dbInventaire.Open()
sqlstring = [/SIZE][SIZE=2][COLOR=#800000]"select * from HeaderOrder"
[/COLOR][/SIZE][SIZE=2]updater = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
CmdBuilder = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommandBuilder(updater)
CmdBuilder.QuotePrefix = [/SIZE][SIZE=2][COLOR=#800000]"["
[/COLOR][/SIZE][SIZE=2]CmdBuilder.QuoteSuffix = [/SIZE][SIZE=2][COLOR=#800000]"]"
[/COLOR][/SIZE][SIZE=2]updater.InsertCommand = CmdBuilder.GetInsertCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]updater.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"HeaderOrder"[/COLOR][/SIZE][SIZE=2])
table = ds.Tables.Item(0)
row = table.NewRow
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList1.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField1"[/COLOR][/SIZE][SIZE=2]) = DropDownList1.SelectedValue
val(1) = DropDownList1.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList2.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField2"[/COLOR][/SIZE][SIZE=2]) = DropDownList2.SelectedValue
val(2) = DropDownList2.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList3.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField3"[/COLOR][/SIZE][SIZE=2]) = DropDownList3.SelectedValue
val(3) = DropDownList3.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList4.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField4"[/COLOR][/SIZE][SIZE=2]) = DropDownList4.SelectedValue
val(4) = DropDownList4.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList5.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField5"[/COLOR][/SIZE][SIZE=2]) = DropDownList5.SelectedValue
val(5) = DropDownList5.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList6.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField6"[/COLOR][/SIZE][SIZE=2]) = DropDownList6.SelectedValue
val(6) = DropDownList6.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList7.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField7"[/COLOR][/SIZE][SIZE=2]) = DropDownList7.SelectedValue
val(7) = DropDownList7.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList8.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField8"[/COLOR][/SIZE][SIZE=2]) = DropDownList8.SelectedValue
val(8) = DropDownList8.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList9.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField9"[/COLOR][/SIZE][SIZE=2]) = DropDownList9.SelectedValue
val(9) = DropDownList9.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList10.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField10"[/COLOR][/SIZE][SIZE=2]) = DropDownList10.SelectedValue
val(10) = DropDownList10.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList11.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField11"[/COLOR][/SIZE][SIZE=2]) = DropDownList11.SelectedValue
val(11) = DropDownList11.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList12.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField12"[/COLOR][/SIZE][SIZE=2]) = DropDownList12.SelectedValue
val(12) = DropDownList12.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DropDownList13.SelectedValue <> [/SIZE][SIZE=2][COLOR=#800000]"Select"[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row([/SIZE][SIZE=2][COLOR=#800000]"HField13"[/COLOR][/SIZE][SIZE=2]) = DropDownList13.SelectedValue
val(13) = DropDownList13.SelectedIndex
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]table.Rows.Add(row)
newrechead = table.Rows.Count - 1
updater.Update(ds, [/SIZE][SIZE=2][COLOR=#800000]"HeaderOrder"[/COLOR][/SIZE][SIZE=2])
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.Message)
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]
[/SIZE][SIZE=2][COLOR=#008000]'Add Event fields with text chosen from textbox to Event table
[/COLOR][/SIZE][SIZE=2]dbInventaire.Open()
sqlstring = [/SIZE][SIZE=2][COLOR=#800000]"select * from Event"
[/COLOR][/SIZE][SIZE=2]updater2 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
CmdBuilder = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommandBuilder(updater2)
CmdBuilder.QuotePrefix = [/SIZE][SIZE=2][COLOR=#800000]"["
[/COLOR][/SIZE][SIZE=2]CmdBuilder.QuoteSuffix = [/SIZE][SIZE=2][COLOR=#800000]"]"
[/COLOR][/SIZE][SIZE=2]updater2.InsertCommand = CmdBuilder.GetInsertCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]updater2.Fill(ds2, [/SIZE][SIZE=2][COLOR=#800000]"Event"[/COLOR][/SIZE][SIZE=2])
table2 = ds2.Tables.Item(0)
row2 = table2.NewRow
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] TextBox3.Text <> [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row2([/SIZE][SIZE=2][COLOR=#800000]"eventname"[/COLOR][/SIZE][SIZE=2]) = TextBox3.Text
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] TextBox4.Text <> [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row2([/SIZE][SIZE=2][COLOR=#800000]"eventdate"[/COLOR][/SIZE][SIZE=2]) = TextBox4.Text
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] TextBox5.Text <> [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row2([/SIZE][SIZE=2][COLOR=#800000]"eventcategory"[/COLOR][/SIZE][SIZE=2]) = TextBox5.Text
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] TextBox6.Text <> [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]row2([/SIZE][SIZE=2][COLOR=#800000]"eventres"[/COLOR][/SIZE][SIZE=2]) = TextBox6.Text
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'eventidx = CInt(row2("EventID"))
[/COLOR][/SIZE][SIZE=2]table2.Rows.Add(row2)
newrecevent = table2.Rows.Count - 1
updater2.Update(ds2, [/SIZE][SIZE=2][COLOR=#800000]"Event"[/COLOR][/SIZE][SIZE=2])
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.Message)
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'------------------------------------------------------------------------
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Get header index to add to existing table with data
[/COLOR][/SIZE][SIZE=2]dbInventaire.Open()
sqlstring = [/SIZE][SIZE=2][COLOR=#800000]"select * from HeaderOrder"
[/COLOR][/SIZE][SIZE=2]updater = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
[/SIZE][SIZE=2][COLOR=#008000]'CmdBuilder = New OleDb.OleDbCommandBuilder(updater)
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'CmdBuilder.QuotePrefix = "["
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'CmdBuilder.QuoteSuffix = "]"
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'updater.UpdateCommand = CmdBuilder.GetUpdateCommand
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]updater.Fill(ds)
table = ds.Tables.Item(0)
row = table.Rows.Item(newrechead)
headeridx = [/SIZE][SIZE=2][COLOR=#0000ff]CInt[/COLOR][/SIZE][SIZE=2](row([/SIZE][SIZE=2][COLOR=#800000]"HeaderID"[/COLOR][/SIZE][SIZE=2])) 'I get the problem here
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.Message)
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Get event index to add to existing table with data
[/COLOR][/SIZE][SIZE=2]dbInventaire.Open()
sqlstring = [/SIZE][SIZE=2][COLOR=#800000]"select * from Event"
[/COLOR][/SIZE][SIZE=2]updater2 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
[/SIZE][SIZE=2][COLOR=#008000]'CmdBuilder = New OleDb.OleDbCommandBuilder(updater2)
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'CmdBuilder.QuotePrefix = "["
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'CmdBuilder.QuoteSuffix = "]"
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'updater2.UpdateCommand = CmdBuilder.GetUpdateCommand
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]updater2.Fill(ds2)
table2 = ds2.Tables.Item(0)
row2 = table2.Rows.Item(newrecevent)
eventidx = [/SIZE][SIZE=2][COLOR=#0000ff]CInt[/COLOR][/SIZE][SIZE=2](row2([/SIZE][SIZE=2][COLOR=#800000]"EventID"[/COLOR][/SIZE][SIZE=2]))
updater2.Update(ds2, [/SIZE][SIZE=2][COLOR=#800000]"Event"[/COLOR][/SIZE][SIZE=2])
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.Message)
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Fill required eventid to header table
[/COLOR][/SIZE][SIZE=2]dbInventaire.Open()
sqlstring = [/SIZE][SIZE=2][COLOR=#800000]"select * from HeaderOrder"
[/COLOR][/SIZE][SIZE=2]updater = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
CmdBuilder = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommandBuilder(updater)
CmdBuilder.QuotePrefix = [/SIZE][SIZE=2][COLOR=#800000]"["
[/COLOR][/SIZE][SIZE=2]CmdBuilder.QuoteSuffix = [/SIZE][SIZE=2][COLOR=#800000]"]"
[/COLOR][/SIZE][SIZE=2]updater.UpdateCommand = CmdBuilder.GetUpdateCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]updater.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"HeaderOrder"[/COLOR][/SIZE][SIZE=2])
table = ds.Tables.Item(0)
row = table.Rows.Item(newrechead)
row([/SIZE][SIZE=2][COLOR=#800000]"EventID"[/COLOR][/SIZE][SIZE=2]) = [/SIZE][SIZE=2][COLOR=#0000ff]CInt[/COLOR][/SIZE][SIZE=2](eventidx)
updater.Update(ds, [/SIZE][SIZE=2][COLOR=#800000]"HeaderOrder"[/COLOR][/SIZE][SIZE=2])
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.Message)
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Fill required headerid to event table
[/COLOR][/SIZE][SIZE=2]dbInventaire.Open()
sqlstring = [/SIZE][SIZE=2][COLOR=#800000]"select * from Event"
[/COLOR][/SIZE][SIZE=2]updater2 = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlstring, dbInventaire)
CmdBuilder = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDb.OleDbCommandBuilder(updater2)
CmdBuilder.QuotePrefix = [/SIZE][SIZE=2][COLOR=#800000]"["
[/COLOR][/SIZE][SIZE=2]CmdBuilder.QuoteSuffix = [/SIZE][SIZE=2][COLOR=#800000]"]"
[/COLOR][/SIZE][SIZE=2]updater2.UpdateCommand = CmdBuilder.GetUpdateCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]updater2.Fill(ds2, [/SIZE][SIZE=2][COLOR=#800000]"Event"[/COLOR][/SIZE][SIZE=2])
table2 = ds2.Tables.Item(0)
row2 = table2.Rows.Item(newrecevent)
row2([/SIZE][SIZE=2][COLOR=#800000]"HeaderID"[/COLOR][/SIZE][SIZE=2]) = [/SIZE][SIZE=2][COLOR=#0000ff]CInt[/COLOR][/SIZE][SIZE=2](headeridx)
updater2.Update(ds2, [/SIZE][SIZE=2][COLOR=#800000]"Event"[/COLOR][/SIZE][SIZE=2])
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
MsgBox(ex.Message)
dbInventaire.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'--------------------------------------------------------------------------
[/COLOR][/SIZE]
 
Last edited by a moderator:
Sort by date Sort by votes
More specific detail to problem

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
46 ' Value 56 should be added to this new row

here is the code where this happens:
VB.NET: 
'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.
 
Last edited by a moderator:
Upvote 0 Downvote
I am new to all this and will probably be of no use. I have not studied

your code much but would like to make a few comments from my

experience with jet access.

1. counting the recs may not work because clearing the table out
and then starting to add new will usually start the autonumber where
it left off before the clear.
 
Upvote 0 Downvote

Similar threads

D
  • Question
I cannot get the last record in an Access table, only the 2nd to last record.
Replies
2
Views
2K
ddutke
D
Q
  • Question
Populating listbox from dataset problem...
Replies
3
Views
6K
cjard
cjard
P
  • Question
Resolved Two Display Values in Databound Combobox
Replies
1
Views
5K
posix32
P
bobo
  • Question
doesnt add new row in access db
Replies
2
Views
2K
cjard
cjard
D
  • Question
update the db using values in a datagrid
Replies
5
Views
2K
diana_j86
D

Share this page

Latest posts

Back
Top