How to retrieve the last autoincremented ID from a SQLite table?

Answer 1

With SQL Server you'd SELECT SCOPE_IDENTITY() to get the last identity value for the current process.

With SQlite, it looks like for an autoincrement you would do

SELECT last_insert_rowid()

immediately after your insert.

http://www.mail-archive.com/[email protected]/msg09429.html

In answer to your comment to get this value you would want to use SQL or OleDb code like:

using (SqlConnection conn = new SqlConnection(connString))
{
    string sql = "SELECT last_insert_rowid()";
    SqlCommand cmd = new SqlCommand(sql, conn);
    conn.Open();
    int lastID = (Int32) cmd.ExecuteScalar();
}

Answer 2

I've had issues with using SELECT last_insert_rowid() in a multithreaded environment. If another thread inserts into another table that has an autoinc, last_insert_rowid will return the autoinc value from the new table.

Here's where they state that in the doco:

If a separate thread performs a new INSERT on the same database connection while the sqlite3_last_insert_rowid() function is running and thus changes the last insert rowid, then the value returned by sqlite3_last_insert_rowid() is unpredictable and might not equal either the old or the new last insert rowid.

That's from sqlite.org doco

Answer 3

One other option is to look at the system table sqlite_sequence. Your sqlite database will have that table automatically if you created any table with autoincrement primary key. This table is for sqlite to keep track of the autoincrement field so that it won't repeat the primary key even after you delete some rows or after some insert failed (read more about this here http://www.sqlite.org/autoinc.html).

So with this table there is the added benefit that you can find out your newly inserted item's primary key even after you inserted something else (in other tables, of course!). After making sure that your insert is successful (otherwise you will get a false number), you simply need to do:

select seq from sqlite_sequence where name="table_name"