WP8/C#/SQLite: get last inserted id?

Your SELECT last_insert_rowid() call does not work because you are running it in a different database connection.

Anyway, you should just read the ID from the inserted ShoppingItem object, like this:

var si = new ShoppingItem() {  Name = anItem,  Shop = aShop,  isActive = aIsActive,};db.Insert(si);return si.Id;

This is what i do to retrieve the id of the last inserted item. The provided code snippet works in my Windows 8 App using SQLite 3.7.XX (SQLite).

    public class ExpenseDataMapper    {        SQLiteConnection connection;        /// <summary>        /// Constructor        /// </summary>        public ExpenseDataMapper()        {            connection = new SQLiteConnection(StaticResources.DATABASE_PATH_NAME);            connection.CreateTable<FinancialListBoxExpenseItem>();        }        /// <summary>        /// Method #1: Inserts an FinancialListBoxExpenseItem into Database        /// </summary>        /// <param name="item"></param>        /// <returns>Primary key of inserted item</returns>        public int insertExpenseItem(FinancialListBoxExpenseItem item)        {            int primaryKey = 0;            connection.RunInTransaction(() =>                            {                                connection.Insert(item);                                primaryKey = item.expenseID;                            });            return primaryKey;        }    /// <summary>    /// Method #2: Inserts an FinancialListBoxExpenseItem into Database    /// </summary>    /// <param name="item"></param>    /// <returns>Primary key of inserted item</returns>    public int insertExpenseItem2(FinancialListBoxExpenseItem item)    {        int primaryKey = 0;        connection.RunInTransaction(() =>                        {                            connection.Insert(item);                            primaryKey = connection.ExecuteScalar<int>("SELECT last_insert_rowid()");                        });        return primaryKey;    }}

The id property in the FinancialListBoxItem class looks like this:

 public class FinancialListBoxExpenseItem : Money.Common.BindableBase    {        private int _expenseID = 0;        [AutoIncrement, PrimaryKey]        public int expenseID        {            get            {                return _expenseID;            }            set            {                this.SetProperty<int>(ref _expenseID, value);            }        }}

I would recommend that you choose a different name for your pk column 'Id'. I don't know if Id is something like an internal keyword. EDIT: Okay it is not a SQLite keyword but id isn't a proper name anyway (Source: SQLite Keywords)

   public List<int[]> CreateSymbolByName(string SymbolName, bool AcceptDuplicates)    {        if (! AcceptDuplicates)  // check if "AcceptDuplicates" flag is set        {            List<int[]> ExistentSymbols = GetSymbolsByName(SymbolName, 0, 10); // create a list of int arrays with existent records            if (ExistentSymbols.Count > 0) return ExistentSymbols; //(1) return existent records because creation of duplicates is not allowed        }        List<int[]> ResultedSymbols = new List<int[]>();  // prepare a empty list        int[] symbolPosition = { 0, 0, 0, 0 }; // prepare a neutral position for the new symbol        try // If SQL will fail, the code will continue with catch statement        {            //DEFAULT und NULL sind nicht als explizite Identitätswerte zulässig            string commandString = "INSERT INTO [simbs] ([En]) OUTPUT INSERTED.ID VALUES ('" + SymbolName + "') "; // Insert in table "simbs" on column "En" the value stored by variable "SymbolName"            SqlCommand mySqlCommand = new SqlCommand(commandString, SqlServerConnection); // initialize the query environment                SqlDataReader myReader = mySqlCommand.ExecuteReader(); // last inserted ID is recieved as any resultset on the first column of the first row                int LastInsertedId = 0; // this value will be changed if insertion suceede                while (myReader.Read()) // read from resultset                {                    if (myReader.GetInt32(0) > -1)                     {                        int[] symbolID = new int[] { 0, 0, 0, 0 };                        LastInsertedId = myReader.GetInt32(0); // (2) GET LAST INSERTED ID                        symbolID[0] = LastInsertedId ; // Use of last inserted id                        if (symbolID[0] != 0 || symbolID[1] != 0) // if last inserted id succeded                        {                            ResultedSymbols.Add(symbolID);                        }                    }                }                myReader.Close();            if (SqlTrace) SQLView.Log(mySqlCommand.CommandText); // Log the text of the command            if (LastInsertedId > 0) // if insertion of the new row in the table was successful            {                string commandString2 = "UPDATE [simbs] SET [IR] = [ID] WHERE [ID] = " + LastInsertedId + " ;"; // update the table by giving to another row the value of the last inserted id                SqlCommand mySqlCommand2 = new SqlCommand(commandString2, SqlServerConnection);                 mySqlCommand2.ExecuteNonQuery();                symbolPosition[0] = LastInsertedId; // mark the position of the new inserted symbol                ResultedSymbols.Add(symbolPosition); // add the new record to the results collection            }        }        catch (SqlException retrieveSymbolIndexException) // this is executed only if there were errors in the try block        {            Console.WriteLine("Error: {0}", retrieveSymbolIndexException.ToString()); // user is informed about the error        }    CreateSymbolTable(LastInsertedId); //(3) // Create new table based on the last inserted id    if (MyResultsTrace) SQLView.LogResult(LastInsertedId); // log the action    return ResultedSymbols; // return the list containing this new record}