Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
fsteeman

fsteeman

Programmer
Jul 17, 2002
103
DK
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 Genera:parentTaxon[/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
 
Sort by date Sort by votes
That's one of those inconsistent MS things. Why are some things 0 based and other 1. Even indexes can be either 0 or 1 based.

Can be frustrating at times.

Dale
 
Upvote 0 Downvote
ADO and ADO.Net are 0 based indecies. Access and SQL Server are 1 based identity fields (auto numbering unique keys). When working with a data table or record set you should always start at 0. With the exception of DAO (And possibly RDO) which I think (it's been a looooong time) may be 1 based.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
[monkey]I believe in killer coding ninja monkeys.[monkey]
 
Upvote 0 Downvote
Yes, and when you add COM objects such as Excel or Visio libraries, when you reference a worksheet or page, the index (indices(?)) are 1 based.

I'm no MS basher, but you'd think they could have been a little more consistent.

Dale
 
Upvote 0 Downvote
In SQLServer and MSDE, you can set the autoincrement start point to be any value in the range of a 32-bit integer. -2147483647 is just as valid as 1.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
 
Upvote 0 Downvote
it likes whatever you give it

remember

fieldname int identity(1,1) is normal

fieldname int identity(0,1) is possible

fieldname int identity(0,0) is not possible

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

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
 
Upvote 0 Downvote
Did I ever say I knew the answer??? I was hoping someone was stupid enough to mek the calculations for me, and guess what I found two idiots that did it.

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
 
Upvote 0 Downvote
[rofl]

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
 
Upvote 0 Downvote
Ahh, but an identity column can be int, tinyint, smallint, bigint, decimal or numeric.

Dale
 
Upvote 0 Downvote
it clearliy states

fieldname int identity(0,222222)

so rick's solution should give you the correct answer allthough I think that int32.max / 222222 will be more correct. 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.

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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
SORT DATE in msflexgrid not correct
Replies
4
Views
327
sal21
sal21
sal21
  • Locked
  • Question
CREATE msflexgrid as in xlsx file 2
Replies
8
Views
544
strongm
strongm
sal21
  • Locked
  • Question
WHY the sort dont work in msflexgrid
Replies
1
Views
234
strongm
strongm
S
  • Question
Replace the chart in the array and substitute the name in textboxt multiline(Vb6)
Replies
0
Views
63
satriano
S
2009luca
  • Locked
  • Question
SORT msflexgrid column 1 with decimal number 1
Replies
4
Views
629
2009luca
2009luca

Part and Inventory Search

Sponsor

Back
Top