CODEWITHSUNDEEP
javaandroidsqlite
Jon
Jon

Reputation: 79

SQLite Java: update table ID's after deleting item automatically

i'm using SQLite to store data and if i delete the last row, ID is 4 and after that add a new row and the ID is 5 and it should be 4. And when trying to view the data it crashes since it can't find data from ID 5.

Database Inspector

In database inspector left of the ID column is a row number. is this accessible since that would solve the problem or is it just a row number indicator in Android studio.

Thank you and have a nice day!

Upvotes: 0

Views: 469

Answers (2)

Jon
Jon

Reputation: 79

Got it working by adding these:

String strSQL1 = "UPDATE people_table SET id = (id +1) WHERE id < 1";
String strSQL = "UPDATE people_table SET id = (id -1) WHERE id > 1";
db.execSQL(strSQL);
db.execSQL(strSQL1);

The id goes right one even if I delete something between first and last one. And you need to remove autoincrement from id for this to work.

Upvotes: 1

Leo
Leo

Reputation: 56

I think you can use a method to recover the last item that you insert, or the one with the higher id, with that you have the value of the next id that you need to put as an extra. If you need it when there are no values then save it when you delete the last item, or reset the data base so that ir reestart the count of the id

Upvotes: 0

Related Questions