Find last inserted row

[winelover]

Hi,

I need to find out what the last inserted row_id is.
I found the SQL-statement "last_insert_rowid()" and a query like this:

SELECT last_insert_rowid() as id FROM tablename

How do I get the value of "id"?

Greetz

Mischa
:sign0104:

 

[winelover]

I found it.
I should have used ExecQuerySingleResult.

I have to change my thinking! It's still too much MySQL based.

Greetz Mischa

 

[stefanoa]

dim tmpID as int
Dim Cursor As Cursor

Cursor = SQL1.ExecQuery("SELECT last_insert_rowid() as LastID FROM table")
If Cursor.RowCount > 0 Then
Cursor.Position = 0
tmpID=Cursor.GetString ("LastID")
End If

Log("LastID: " & tmpID)

bye

 

[chrisleeuk2013]

Additional modification for auto increment

Hi all,

Liking DBUtils, in fact enjoying Basic4Android having started using it in the last few days.

Thanks for the autoincrement suggestion billy047

I wanted to use auto increment because it takes the hassle out of generating your own ID's so I've been going down the same route.

I was looking at the InsertMaps routine and decided I wanted to make it return the ID's of the newly inserted auto increment ID's

I added an extra parameter usingautoincrement.

If the usinguautoincrement is set to true then this version will add an ID column of the inserted row into ListOfMaps. That way it's available for use back in the calling function.

'Inserts the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List, UsingAutoIncrement As Boolean)
   Dim sb, columns, values As StringBuilder
   'Small check for a common error where the same map is used in a loop
   If ListOfMaps.Size > 1 AND ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
      Log("Same Map found twice in list. Each item in the list should include a different map object.")
      ToastMessageShow("Same Map found twice in list. Each item in the list should include a different map object.", True)
      Return
   End If
   SQL.BeginTransaction
   Try
      For i1 = 0 To ListOfMaps.Size - 1
         sb.Initialize
         columns.Initialize
         values.Initialize
         Dim listOfValues As List
         listOfValues.Initialize
         sb.Append("INSERT INTO [" & TableName & "] (")
         Dim m As Map
         
         Dim StringArgs() As String
         
         m = ListOfMaps.Get(i1)
         For i2 = 0 To m.Size - 1
            Dim col As String
            Dim value As Object   
            col = m.GetKeyAt(i2)
            value = m.GetValueAt(i2)
            If i2 > 0 Then
               columns.Append(", ")
               values.Append(", ")
            End If
            columns.Append("[").Append(col).Append("]")
            values.Append("?")
            listOfValues.Add(value)
         Next
         sb.Append(columns.ToString).Append(") VALUES (").Append(values.ToString).Append(")")
         If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
         SQL.ExecNonQuery2(sb.ToString, listOfValues)
         Dim returnval As Int
         
         If UsingAutoIncrement Then
            'Additional code to obtain the ID of the newly inserted auto increment row
            'and append it to the map, this will then be available from the calling module.
            sb.Initialize
            sb.Append("SELECT last_insert_rowid() as ID from ").Append(TableName)
            returnval=SQL.ExecQuerySingleResult(sb.ToString)
            m.Put("ID", returnval)
            ListOfMaps.Set(i1,m)
            Log(returnval)
         End If
         
      Next
      SQL.TransactionSuccessful
   Catch
      ToastMessageShow(LastException.Message, True)
      Log(LastException)
   End Try
   SQL.EndTransaction
End Sub

You can then call it and get the ID like this...

   Dim ListOfMaps As List
   ListOfMaps.Initialize

   Dim m As Map
   m.Initialize
   m.Put("Title", Title)
   
   ListOfMaps.Add(m)
   DBUtils.InsertMaps(SQLInstance, "Chapter", ListOfMaps,True)
   
   Log(m.Get("ID"))