SQLite and list box

Hi guys,
I made a window where i load a listbox from a dblite table using a select statement to list all the records with the same “name” (which is one of the fields in the table).
Right now i make the changes in the listbox and then i have a save buttom.
When the save buttom is pressed, i delete all the records in that rowset and add them again from the listbox with the updated information but I dont think this is the way to go and it is taking time to do that.
What I am thinking I want to go is update the table after i make all the changes in a particular row of the listbox

Whats the best way of doing this?
How can I get the “row” position in the table to update the correct record?

Thanks in advance

For SQLite, you can return the “rowid” column in the query and put it into an invisible column (or use the RowTagAt() method) in the listbox.
When you detect a change you want to save (for example in the CellAction event handler), use the ExecuteSQL statement to do an UPDATE with a WHERE clause that contains the “rowid” for the changed row.

thanks Mathew,
I just thought of something else, I made the listbox to be able to move the order of the rows and I wanted to save it in the order it shows it on the listbox, I think thats why I deleted all rows originally. Any ideas?

What is the new Row order ?
Alphabetically sorted by Name ?

You’ll need a column in the database. SQL tables don’t have an inherent row order. Obviously if you are deleting the data in the table and reinserting it in order every time, SQLite rowids will be in order, but this is probably inefficient.
Alternately, and maybe better, you could have a separate table that contains the rowid (this would be the rowid from the first table) and the display order. This keeps the display properties separate from the data, unless the order really is a relevant data element.
Either way, you’ll have to think about when and how you do the updates, remembering that some of the other row numbers change, as well.

Thank You guys,
The order can be anything. The users wants to be able to order the rows how they want to see it on the report. The idea you guys gave me is to add another column in the database with the order id, then everytime the user moves a row, just renumber that column so hopefully this will not take as long as it is taking now deleting all the records and re adding them. The list will not have more than 200 records everytime. That way, I can just sort by that column when I do the select statement for the report.

SQL have sorting commands…

Coming back to this, I saw another discussion with an example that hopefully it is still available

I wonder if the loop in the discussion will be fast? Right now, it is taking about 8 seconds to store about 50 rows. Again, what I want to do is change the order in which the rows are shown if the user drags a row to a different position

Thanks

I think this example might also be helpful for you:

Examples/Database/SQLite/SQLiteExample

Thanks Paul,
i look in there but there is nothing about dragging the rows. I have this statement in the DragReorderRows:

msgbox str(i) + " " + Listbox1.RowTagAt(i).StringValue for i = 1 to Listbox1.LastRowIndex app.DB.SQLExecute("UPDATE testrecord SET seq = " + str(i) + " WHERE id = " + listBox1.RowTag(i).StringValue) next

when I drag lets say the last row and I want to put it on the first location, “id” shows the first row “BEFORE” I dragged it so the seq is never changed.

you have the new sort order after this DragReorderRows event method.
and the first row start with 0.

It sounds like you’re trying to do two things at once. I recommend you first focus on getting load/save working the way you want, then you can worry about row re-ordering.

So first get your code working to display and save data using the technique from the database example. The important part is to store the rowid/primary key in the ListBox and then use it when updating the DB.

You can turn on row reordering with the ListBox.AllowRowReordering property.

If you need to remember the sorted order then you would probably want a SortedOrder column in the DB table that you update from the DragReorderRows event (and then also use it in the ORDER BY of the SQL SELECT).

I already have the rows being saved and retrieve to/from the listbox. Lets say I have this:
image
I want to move the model “4693” to the first item on the list. When I drag it to the 1st column, I want to see this in the table so that way I can sort by “Seq” when I do the query to display it in the listbox inthe order the user did it last.
image
“id” is the unique Rowid in the table which I also load to the listbox as the first column (hidden)

I figured it out guys. Thank you for your thoughts.