I often have to get the last ID, so I use say a code snip like this:
Dim InvoiceIDA As Long = 0
' create a new invoice
Dim MyParms As List
MyParms.Initialize
MyParms.Add(gID)
MyParms.Add(BillRate)
sqlite.ExecNonQuery2("INSERT INTO Invoices (CustomerIDA, BillRate) VALUES(?,?)",MyParms)
InvoiceIDA = MyCode.GetLastID
So, I was a bit tired of writing the same code over and over to get that last ID from a insert.
The code for "GetLastID" in my global "general" code module is thus this:
Sub GetLastID As Long
' get laste database insert row ID
Dim PK As Long = 0
Dim rset As ResultSet = Main.sqlite.ExecQuery("SELECT last_insert_rowid() AS ID")
If rset.NextRow Then
PK = rset.GetLong("ID")
End If
rset.Close
Return PK
End Sub
Now, I adopt a standard in my main module that the database object is "sqlite". If you don't have that global database object/handle, then you could I suppose modify the above to include/pass the database object sqlite in above. So in my first "main" routine/activity - I have a global database object "sqlite". I also alias the column name last_insert_rowid() to "ID", and that was done since you can't get columns by their index (say 0), but have to use name.
So, I have general routine - write it once, and then use it for all cases in which I need/require the last inserted auto number PK id. You can use max() as some suggest - but I tend to avoid that approach, since I might be use some custom incrementing code for the last insert (and below the max value).
As a result, such a number not always going to be the max most high value - so I suggest last_insert_rowid() over that of using max() function. And of course I also suggest to write this code one time and use it over and over for the whole application.
Regards,
Albert D. Kallal
Edmonton, Alberta Canada