Move Cursor to last added row in SQLite

R

RichardN

Well-Known Member
Licensed User
Longtime User
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
 
Sort by date Sort by votes
R

RichardN

Well-Known Member
Licensed User
Longtime User
Thanks very much......

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

B4X: 
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 !!!
 
Upvote 0
R

RichardN

Well-Known Member
Licensed User
Longtime User
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....

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

Then I add a row using the INSERT clause:

B4X: 
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.....

B4X: 
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...

B4X: 
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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
Android Question android.database.CursorWindowAllocationException: Cursor window allocation of 2097152 bytes failed.
Replies
3
Views
352
teddybear
Mashy Teaches WebApp/Website Development with BANanoVuetifyAD3 - The New Series
7 8 9
Replies
163
Views
67K
Mashiane
  • Locked
  • Article
Android Tutorial [B4X] Comment Links
Replies
7
Views
16K
Erel
C
  • Question
Android Question MSSQL-JDBC Minimalistic Example
2
Replies
27
Views
23K
MrKim
  • Question
Android Question CursorIndex Out Of Bounds Exception
2
Replies
36
Views
8K
Erel