SQLite and Listview problem. Help needed!

[Zoldex]

Hi everybody!

I'm trying to use SQLite for the first time into an app with a listview.

I've already succesful managed to create a database, insert and update items, also on the listview!

My problem is about deleting records.

I mean i can easily delete a record from a database with

_SQLite_Exec(-1, "DELETE FROM Database WHERE KEY = " & $selected_row_on_listview & ";")

and also from listview with

_GUICtrlListView_DeleteItemsSelected($listview)

but the difference is that if I have 5 records into the Database and I delete 1 in the middle (say KEY=3) I will have KEY values 0,1,2,4

BUT Listview updates its rows 0,1,2,3 so I couldn't use $selected_row_on_listview anymore, since if I click on row 3 it would try to delete a missing record.

Sorry if my question sounds silly but I'm really stuck now.

Thanks for any hints and forgive my poor english.

Also, any good example with SQLite and listview would be greatly appreciated.

Edited September 27, 2012 by Zoldex

[NiVZ]

Zoldex,

I read the data from the SQLite query into an array. Then I have a function that loops through the array to populate the listview.

When I delete a selected row, I delete that row number from the array (and from the database) and then re-call the function to update the listview.

That way the array always has the rows in the same order as the listview, but in the delete statement to the database I can pass the KEY that is held in one of the array columns.

Another method would be to hold the database KEY value in a hidden column in the ListView.

Hope that gives you an idea.

NiVZ

Edited September 28, 2012 by NiVZ

[Zoldex]

NiVZ,

thanks for your answer.

If I understand what you say, I should read again the whole table from database to update listview, each time I delete a record?

That's what I thought when I had the problem but don't know what would happen with very large databases.

I mean that it would become a slow process if I need to delete several rows...

Another method would be to hold the database KEY value in a hidden column in the ListView.

My KEY value is not displayed on the listview and is used only as a progressive counter, that would serve me to identify the row in the database.

Anyways, even if not displayed on listview, when I delete the row from the database it's really gone forever and creates a "hole" in the counter.

The listview, instead, updates itself all the index rows...

I would need a way to tell SQLite to "reorganize" the rows when a KEY in the middle is missing... like Listview does.