1

I'm writing an INSERT using SQLite, and either I am using an AUTOINCREMENT or just going to use the ROWID as the primary key. Once the INSERT is performed, how do I find out what the primary key is for the row I just inserted?

I know what to do if I were working in Sql Server, but this is SQLite.

Here's my code:

public bool AddPlanet(Planet p)
{
    bool result = false;
    SQLiteConnection sqlConnection;
    using (sqlConnection = new SQLiteConnection(ConnString))
    {
        sqlConnection.Open();
        sqlCommand = new SQLiteCommand(sqlConnection);
        sqlCommand.CommandText =
            String.Format(
                "insert into planet (name, satellites) values ('{0}',{1})"
                , p.Name
                , p.Satellites.ToString()
                );
        sqlCommand.CommandType = CommandType.Text;
        int x = sqlCommand.ExecuteNonQuery();
        // WHAT DO I DO HERE TO FIND OUT THE ROWID OF
        // THE ROW THAT WAS JUST INSERTED?
        if (x > 0) result = true;
        sqlConnection.Close();
    }
    return result;
}
Cyberherbalist
  • 12,061
  • 17
  • 83
  • 121
  • Try to run another query but use SELECT LAST_INSERT_ID(); It is SQL but I dont know if your platform support it – MadeInDreams Jan 12 '18 at 00:02
  • Thanks @MadeInDreams, but it isn't available. :-( – Cyberherbalist Jan 12 '18 at 00:24
  • Have a look here, https://stackoverflow.com/questions/304543/does-sqlite-support-scope-identity – jac Jan 12 '18 at 00:25
  • @MadeInDreams, but you were fairly close -- it helped me find the correct function, which was LAST_INSERT_ROWID. – Cyberherbalist Jan 12 '18 at 00:36
  • No kidding @DourHighArch! Heck, I'd vote to close on it myself -- like I said below, I looked for the answer and couldn't find it. SO can be so difficult to find answers sometimes -- and sometimes not. Puzzling. – Cyberherbalist Jan 12 '18 at 00:58

1 Answers1

0

for SQLite you can use 

SELECT last_insert_rowid()

here is another stackoverflow post with more details

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

Xss
  • 211
  • 1
  • 12
  • This makes my question a DUP, of course. This is what annoys me about SO! I searched several times for the answer which you indicated, but none of the search results contained that already-answered question. Or I didn't scroll down long enough? Thanks for your answer. – Cyberherbalist Jan 12 '18 at 00:39
  • 1
    Yeah this is one of the downside on stack. Question editing tend to raise the standard a bit and make it harder to find if you have no clue what the function is call or even exist. – MadeInDreams Jan 12 '18 at 00:41