iOS Question iSQLiteLight insert a new record between existing ones ?

[MitchBu]

I am adapting the code from Klaus example "iSQLiteLight Three simple SQLite projects" to my own need : a spreadsheet like display with 7 columns.

It works very nicely, so I am confident I will be able to used this in my current check register project.

The only feature I miss is to insert a record between existing ones.

Say I want to insert a new record between record 4 and 5. How will I proceed ?

Thank you in advance.

iSQLiteLight Three simple SQLite projects

The three projects are portations of the three BA4 SQLiteLight programs to B4i. These examples are explained in the B4XSQLiteDatabase Booklet. - iSQLiteLight1 A very simple project with a very simple user interface with following functions: At the first run the database is empty and the user...

www.b4x.com

 

[klaus]

There is no Insert method in these examples because these are simple database demonstration programs.
In a database, when you insert a new entry it is added into the database and the internal rowID is automatically incremented.
What you want to do is a display problem not directly related to the database.
You could use your own ID column for filtering and sorting, but it is up to you to manage it.
Then, when you read the database you can filter and sort almost whatever you want for the display.

 

[MitchBu]

Thank you Klaus.

So, basically, what I have to do is to manipulate the ID field ?

For instance, if I want to insert a record between4 and 5, I renumber all records from 5 on, by incrementing 1, and I set the inserted one as ID = 5, right ?

Besides your booklets, would you have any suggestion for a book or web site that teaches SQLite ?

 

[klaus]

For instance, if I want to insert a record between4 and 5, I renumber all records from 5 on, by incrementing 1, and I set the inserted one as ID = 5, right ?

Yes.

Besides your booklets, would you have any suggestion for a book or web site that teaches SQLite ?

Book no, I have looked a lot at this site: https://www.sqlite.org/index.html
And more specifically this part: https://www.sqlite.org/syntax.html

 

[MitchBu]

Thank you, Klaus

 

[aeric]

For instance, if I want to insert a record between4 and 5, I renumber all records from 5 on, by incrementing 1, and I set the inserted one as ID = 5, right ?

I recommend you just leave the auto increment field. Add another column for e.g. SortID or SequenceNo and then follow the steps you mentioned above, renumbering of ID 5 onwards by increment 1. Then use this field after ORDER BY in your SQL query.

 

[klaus]

I recommend you just leave the auto increment field. Add another column for e.g. SortID or SequenceNo

This is what I meant.
Thank you for clarifying it.
I don't use any auto incremented ID column, I use the internal rowid for this.