Getting last autonumber in access

Question

I am trying to get last auto number after inserting record into access database. I am getting zero. Not sure what I am doing wrong. I tried to google but could not find any simple example which I can use to understand.

// Database was opened before.
cmd.ExecuteNonQuery(); // Insert is working fine.
cmd = new OleDbCommand("SELECT @@IDENTITY", con);
id = cmd.ExecuteNonQuery(); // id shows zero in debug

Searching for [access SELECT @@IDENTITY](http://www.bing.com/search?q=access+SELECT+%40%40IDENTITY) - gives you [HOW TO: Retrieve the Identity Value While Inserting Records into Access Database By Using Visual Basic .NET](http://support.microsoft.com/kb/815629) - which uses `ExecuteScalar` instead of `ExecuteNonQuery` (which returns number of affected rows, not a result ). — Alexei Levenkov, Jan 04 '14 at 03:47

paxdiablo · Accepted Answer · 2014-01-04T03:52:56.950

2

ExecuteNonQuery is meant for doing catalog type operations (creating/deleting objects and so forth) and update/insert/delete operations (note that select is not in that list).

For the update/insert/delete, it returns the number of rows affected.

If you want to select something from the database, you will need to get a result set to see what it returns (see ExecuteReader for example).

Altenatively, ExecuteScalar can execute a command and return the first column of the first row, which seems to be what you're after here.

edited Jan 04 '14 at 03:52

answered Jan 04 '14 at 03:46

paxdiablo

854,327
234
1,573
1,953

1

+1 `ExecuteScalar` is the MSDN recommended way of getting the value. – Alexei Levenkov Jan 04 '14 at 03:50
Thanks for your help. For someone like me, the syntax is id = Convert.ToInt32(cmd.ExecuteScalar()); – user2026794 Jan 04 '14 at 03:54

score 0 · Answer 2 · edited May 23 '17 at 12:03

0

See this:

cn.Execute "INSERT INTO TheTable.....", , cmd+ adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , cmd)

edited May 23 '17 at 12:03

Community

1
1

answered Jan 04 '14 at 03:45

score 0 · Answer 3 · answered Jan 04 '14 at 03:48

If DAO use

RS.Move 0, RS.LastModified
lngID = RS!AutoNumberFieldName

If ADO use

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn being a valid ADO connection, @@Identity will return the last Identity (Autonumber) inserted on this connection.

Getting last autonumber in access

3 Answers3

Linked