2

i found many answers to this question for other databases, but didn't found any answer for Microsoft access.

I have a table that has the following columns : ID A B C the id is auto number , how do i get the ID of a row i just inserted.

btw : i'm using C#

thanks

OopsUser
  • 4,642
  • 7
  • 46
  • 71
  • Check this http://stackoverflow.com/questions/1628267/autonumber-value-of-last-inserted-row-ms-access-vba – Kapil Khandelwal May 27 '12 at 15:49
  • In Java and JDBC there is a method on Statement to fetch generated IDs after executing the insert command, I believe there must be something similar in C#. – Amir Pashazadeh May 27 '12 at 15:49

3 Answers3

8

See the MSDN article here. In MS Access, you'll want to SELECT @@IDENTITY within the scope of the statement which inserted the record.

David
  • 208,112
  • 36
  • 198
  • 279
4

You can get it with the oldes method. Insert your record and do a select for the last id inserted: 

INSERT INTO table(column1, column2, ...) VALUES(value1, value2)

SELECT TOP 1 id FROM table ORDER BY id DESC

Of course it's not the best one, but it sure works.

Ricardo Souza
  • 16,030
  • 6
  • 37
  • 69
  • Although it's not the best solution, it's the most simple i saw so far, thanks. – OopsUser May 27 '12 at 17:01
  • 4
    Do not forget that in a multiuser environment there will be plenty of time for some other user to have inserted a different record. I would go with David's suggestion. – Fionnuala May 27 '12 at 19:00
  • Sure. If your app has dozens of users doing the same thing you may get problems with this (not so commom, since I've used it for decades on classic ASP for intranet systems and never had a problem, since the connection is the same and the time between those queries is dispresible). – Ricardo Souza May 27 '12 at 21:12
  • Sure. The execution time is almost the same. The choice may came with the preference. – Ricardo Souza May 28 '12 at 15:31
2
//C#
string query = "Insert Into Categories (CategoryName) Values (?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
  using (OleDbCommand cmd = new OleDbCommand(query, conn))
  {
    cmd.Parameters.AddWithValue("", Category.Text);
    conn.Open();
    cmd.ExecuteNonQuery();
    cmd.CommandText = query2;
    ID = (int)cmd.ExecuteScalar();
  }
}
Jomol Michael
  • 31
  • 1