Autonumber starts with 0 in ADO.NET, 1 in MS Access?

[fsteeman]

Now here is something special:

I discovered that when generating new rows in a VB.NET application, the autonumbering starts with 0, whereas in the database itself, MS Access, it starts with 1. This is rather annoying as I am working on a routine to move and normalize data from one database to another. Whenever I write the foreign key in one table, in Access the primary key in the referred table is incremented by one!

For example:
[navy] [green]
'*Normalize Genus to Genera[/green]
If srcRow.Item("Genus").ToString() <> "" Then
[green]'locate entry in Genera-table (on name)[/green]
fndRows = Me.GMDB2DataSet.Genera.Select("GenusName = '" & srcRow.Item("Genus").ToString & "'")
[green]'if not found then add new record, copy Genus to Genera:GenusName, copy HigherTaxa reference to GeneraarentTaxon[/green]
If fndRows.Length = 0 Then
nrmRow = Me.GMDB2DataSet.Genera.NewGeneraRow
nrmRow.Item("GenusName") = srcRow.Item("Genus")
nrmRow.Item("ParentTaxon") = hitaxon
nrmRow.Item("ParentGenus") = 0
Me.GMDB2DataSet.Genera.Rows.Add(nrmRow)
[green]'remember genus foreign key[/green]
genus = nrmRow.Item("genus_ID")
Else
[green]'entry found, remember genus foreign key[/green]
genus = fndRows(0).Item("genus_ID")
End If
Else
genus = 0
End If
[/navy]

Of course there is a simple ad-hoc solution to this by simply adding 1 to the foreign key that needs to be entered in another table:

[navy]genus = fndRows(0).Item("genus_ID")[red] + 1[/red][/navy]

However, it is very strange that ADO.NET does not apply the same rules as the source database.


Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[earthandfire]

fieldname int identity(0,222222) well get you in trouble real quick extra points who guesses how many records you can have

and now ...

And as always it's not the result that's important it's the way you get to the result that will give you the points

So you have now moved the goalposts, chrissie?

 

[chrissie1]

nope the goalposts are still in the same place, you just din't see them I never asked for a number.

You still have much to learn Padewan.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[AccordingToDale]

it clearliy states

fieldname int identity(0,222222)

ooops. must be my selective eyesight kicking in again. ;>)

as a matter of fact, if i recall correctly, none of this ever occurred. LOL.

Dale

 

[ThatRickGuy]

I am not the droid you are looking for.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

Nope I was thinking more of the wookie for you.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting