Android Question Trying to get the last inserted rowid

[Alberto Michelis]

I need to get the autonumber id when inserting a row, Im trying with

Cursor2 =SQL1.ExecQuery("Select last_insert_rowid();")
IdApp = Cursor2.GetInt2(0)

But I get the following error:

error UpdateRecordPos android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1

Ive tryed with Cursor2.GetInt2(0) and Cursor2.GetInt2(1)
and always the same error

Thanks

 

[asales]

I'm using this code:

'Return Last sequence id from the selected table
Sub GetLastSequence(SQL As SQL, TableName As String) As Int
   Dim c As Cursor
   Dim ret As Int
  
   Try
       c = SQL.ExecQuery("SELECT * FROM sqlite_sequence WHERE [name] = '" & TableName & "'")
       c.Position = 0
       ret = c.GetInt("seq")
'       Log(TableName & " Sequence (ID): " & ret)
   Catch
       Log(LastException)
       ret = 0
   End Try
  
   Return ret   
End Sub

 

[MarkusR]

i doing it this way.
a id=0 have the meaning of creating a record.

Sub SaveOne(Item As Inspection)
   
    Dim db1 As DB
    db1.Initialize
   
    Dim sql1 As SQL = db1.Open
       
    If Item.InspectionId=0 Then
       
        Log("INSERT")
       
        'Autokey      
        sql1.ExecNonQuery2("INSERT INTO Inspection (Title) VALUES (?);", Array As Object("New") )
        Item.InspectionId = sql1.ExecQuerySingleResult("SELECT last_insert_rowid() FROM Inspection")
       
        Log ("New InspectionId = " & Item.InspectionId)
   
    End If
       
    Log("UPDATE")

    Dim arg As List
    arg = Array As Object( _
    Item.InspectionId, Item.Title, Item.Description, Item.Notes, Item.Customer, _
    Item.Project, Item.Order, _
    Item.OrderPos, Item.Name, _
    Item.Address, Item.Zip, _
    Item.City, Item.State, _
    Item.Country, Item.Latitude,  _
    Item.Longitude,    Item.IsGPSCoordinates, _
    Item.Date, Item.AppId )

    ' without '' at strings!!! just ? as parameter !!!
    sql1.ExecNonQuery2("UPDATE Inspection SET InspectionId=?, Title=?, Description=?, Notes=?, Customer=?, Project=?, [Order]=?, OrderPos=?, Name=?, Address=?, Zip=?, City=?, State=?, Country=?, Latitude=?, Longitude=?, IsGPSCoordinates=?, Date=?, AppId=? WHERE InspectionId=" & Item.InspectionId, arg)
   
    db1.Close

End Sub

 

[Jeffrey Cameron]

Honestly, I think all you need to do (after your INSERT statement) is:

"SELECT last_insert_rowid();"

and then execute the joined statements as a scalar or query and it should return you the primary key value that was assigned.

 

[Mahares]

error UpdateRecordPos android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 1

Your problem is quite simple. You forgot to have this line in your code: cursor2.Position=0
Therefore your code should look like this:

cursor2 =SQL1.ExecQuery("Select last_insert_rowid();")
    cursor2.Position=0     '<----- this line
    IdApp = cursor2.GetInt2(0)
    Log("last inserted: " & IdApp)

ADDED ANOTHER WAY: You could also get it this way:

IdApp=sql1.ExecQuerySingleResult("SELECT last_insert_rowid()")
    Log("last inserted: " & IdApp)

 

[Alberto Michelis]

Thanks, that was it... now...
Its not working or Im not clear or confused about it.
If I do
"Insert into MyTable values(null,.......)"
The app inserts the record with id=28
But doing
"SELECT last_insert_rowid()"
I get 45
I need the ID of the last MyTable added record

 

[Alberto Michelis]

trying not to use max(id)

 

[Mahares]

I need the ID of the last MyTable added record

The last_insert_rowid() is not always the same as the ID. You need something like this:

Dim c As Cursor=SQL1.ExecQuery("SELECT ID from MyTable WHERE  ID=(SELECT last_insert_rowid())")
    c.Position=0
    Log(c.Getstring2(0))

Suppose you have a table with 14 records, then you delete the 9th record, you are left with 13 records, but the last_insert_rowid() is still 14. It did not change back to 13.

Here is a link that explains it in more details: https://www.sqlite.org/autoinc.html