Get id from the last entry

Question

I need to insert a product into a ProductDB table and at the same time get the id from the product I just inserted, so I can use it in the next query as a Foreign Key I have been looking at different methods like "select last_insert_rowid()" and "SCOPE_IDENTITY()" but I can't get it to work, how do I get it to work

public static void SaveProduct(ProductModel product)
{  
   using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString()))
   {
      cnn.Execute("INSERT INTRO ProductDB (Name, Price) VALUES (@Name, @Price);", 
      product);

      string ForeignKey = "the id from the last entry from the query above";

      cnn.execute("INSERT INTO ImageDB (Filename, Path, FK_Product) VALUES (@Filename, @Path," + ForeignKey + " )");
   }
}

This isn't a question about using Visual Studio, so I've removed that tag. — ProgrammingLlama, Oct 23 '21 at 12:50
See [SELECT last_insert_rowid()](https://stackoverflow.com/a/2128593/10318835). Your table must of course have an autoincrement field in the table ProductDB for this to work. — Steeeve, Oct 23 '21 at 13:02
See also the [RETURNING](https://sqlite.org/lang_returning.html) clause — Mark Benningfield, Oct 23 '21 at 13:26

score 0 · Answer 1 · answered Nov 06 '21 at 08:08

In SQL Server has 3 functions (methods) for getting the last inserted id from the table.

IDENT_CURRENT() - returns the last-inserted identity value for a given table.

SCOPE_IDENTITY() - returns the last identity value inserted into an identity column in any table in the current session and current scope.

@@IDENTITY - returns the last inserted identity value in any table in the current session, regardless of scope.

We need SCOPE_IDENTITY(), so ATTENTION!!! This function must be used in the current scope, which located insert command.

Example:

declare 
    @new_id integer;
INSERT INTRO ProductDB (Name, Price) VALUES (@Name, @Price);
SET @new_id = SCOPE_IDENTITY();

score 0 · Answer 2 · edited Mar 30 '22 at 06:33

If you would use System.Data.SQLite you could use the LastInsertRowId property.

Example:

    using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=.\Test.db"))
    {
        conn.Open();

        SQLiteCommand cmd = conn.CreateCommand();
        cmd.CommandText = $"INSERT INTO Test (name) values ('{Guid.NewGuid().ToString()}');";

        cmd.ExecuteNonQuery();
        id = conn.LastInsertRowId;

        conn.Close();
    }

Get id from the last entry

2 Answers2