Move Cursor to last added row in SQLite

[RichardN]

My Db table has [RecNum] as INTEGER PRIMARY KEY, but the recordset is sorted by other columns.

When I add a new row to the recordset (INSERT INTO....) I want to populate my view using that new row as the current record. Naturally as records have been added/removed the values of [RecNum] have become non-contigious so that poses problems.

So what is the best SQL query to isolate he last added record so I can.... Cursor.Position= ??? and make it the current record when the INSERT INTO process is complete ???

Thanks

 

[NJDude]

You should use "Last Insert Rowid"

 

[RichardN]

Thanks........ But how do you return that function in B4A syntax ?????

 

[Erel]

See this link: Find last inserted row

 

[RichardN]

Thanks very much......

Rather than using this method to return a recordset with only one record....

Cursor = SQL1.ExecQuery("SELECT last_insert_rowid() as LastID FROM table")

Can it be employed to a refreshed recordset AFTER an add/edit operation to position the cursor somewhere in the middle of the recordset to make that last added record the current record ?

Of course you could scroll through the recordset looking for a clue but there must be a smarter/quicker method than that ?

PS Sorry for talking in Microsoft !!!

 

[Erel]

Are you looking for an auto increment primary key feature? SQLite does support it.

PS Sorry for talking in Microsoft !!!

?

 

[RichardN]

Perhaps I have not explained very well....

TABLE Employees
RecNum INTEGER PK
First STRING
Last STRING

I gain access to the table via the Cursor object by executing a SELECT clause....

Cursor = SQL1.ExecQuery("SELECT * FROM Employees ORDER BY Last, First")

Then I add a row using the INSERT clause:

SQL1.ExecNonQuery2("INSERT INTO Employees ('First','Last') VALUES (?, ?)", Array As Object("Joseph", "Bloggs"))

The row is added to the underlying database BUT the new row is not visible programmaticly until you execute another SELECT clause.....

Cursor = SQL1.ExecQuery("SELECT * FROM Employees........ORDER...

At this point you have lost any reference to the row that was just added as any ORDER clause has placed the row somewhere in the middle of the recordset.

I want to come out of the INSERT + SELECT process with a pointer to the last added row so that I can immediately make that row the current Cursor position in the ORDERed recordset. Something like a method to set the Cursor.Position at the highest [RecNum] ?????

At the moment I add a row then reposition using this code but it is not very elegant or efficient...

SQL1.ExecNonQuery2("INSERT INTO Employees ('First','Last') VALUES (?, ?)", Array As Object("Joseph", "Bloggs"))

Cursor = SQL1.ExecQuery("SELECT * FROM Employees ORDER by Last, First")

Dim count as Int

For count = 0 To Cursor.RowCount - 1
   Cursor.Position= count
   If LastAddedSurname = Cursor.GetString("Last") Then Exit
Next

PopulateViewFieldsWithLastAddedRow  '(!)

Maybe a wishlist ????

Cursor.MoveFirst
Cursor.MoveLast
Cursor.MoveNext
Cursor.MovePrevious
Cursor.MoveLastAdded