I was very happy when I discovered that in Xojo it’s possible to simply drag rows up and down, something that was very difficult to do in Filemaker. However when restarting the project, that order is being reset to a default it seems. How can I avoid this from happening and let the project remember the row order I drag them into?
What is it that defines the position of each row? If you’re getting that from a database you’ll have to update the relevant information in the database as you drag the row.
1 Like
Yeh if that order is based on a key in the database when populating the rows then you need to update whatever your order is based upon on the changed record and write it back to the database.
I’m afraid that the word “key” could induce newbies on error (rowid). Create a table column, let’s call it “row_order”, make it indexed for fast reorder, change it’s contents as Mark stated, load your list querying with ORDER BY row_order.
1 Like
Thanks for the explanation, Rick. I created the column named ChapterSorting and Indexed it. I don’t know where I change the contents to Mark stated though. Is this something I do in the DB Bowser program as well? This is what I have:
As I said, you update the database as you drag the rows around. So do it in the dragrow event.
Nope. Pure code, update the row_order column (whatever you want name it) responding to reordering events as Tim said above.
If you’re not doing this already, you can make things easier on yourself by storing the write.id either by itself or in a class in a celltag or rowtag in your listbox. So when dragrow is called, you can just zip through the rows. (Basic example without knowing too much about how you’ve got things set up.)
for i = 0 to listbox1.lastindex
sql = "update write set ChapterSorting = ? where id = ?"
db.executesql(sql, i, listbox1.rowtagat(i))
next
1 Like
I added a Sort column to the tables in the Database and added numbers in the rows manually. The order is being read correctly into the Listbox, so that part works now. Now I need to save the order created by the row dragging to the database. I notice that there are 2 Event in the Listbox to choose from: DragOrderRows and DragRows, what is the difference?
DragRow is called when the user begins the drag. DragReorderRows is called when the user finishes the drag.
I see, so to write the dragged new position of a row to the database, I need to use both events and place the Update sql code in them?
Ah ok, I understand now that this isn’t something Xojo does behind the screen, how could it also… I’ve been watching to many Harry Potter movies in the past 
No, just the DragReorderRows event. Loop through the listbox and update every single row with a new ChapterSorting value. (You don’t have to update the ones that haven’t changed, of course.)
var i, old as integer
for i = 0 to listbox1.lastindex
old = listbox1.cellvalueat(i, columnforsortvalue).tointeger
if old <> i then
listbox1.cellvalueat(i, columnforsortvalue) = i.tostring
// update the database
end
next
There will be rows before and after the drag area that will not be affected.
The thing to grasp is that the listbox and the database have nothing to do with each other. They are not “connected” in some way such that, if you change one, the other is changed for you.
You have to do any reading from the database and writing to the listbox, or reading from the listbox and writing to the database, yourself.
So after having pretty much finished my app, I now am back to fix this problem: Dragging rows in a listbox and having it saved to the database table. I feel I am close to have this working correctly, but there is still one thing I cannot get right.
The row order in the database table is being read into the listbox correctly, but when I drag a listbox row to another position, this new order is ONLY being saved to the database after I drag again a row to another position. It seems that the saving moment needs a reminder to really save the order.
This is the code I am using in the DragReorderRows event handler in the listbox, is there anything that could create this behaviour?
// Renumber the Sequence value
For i As Integer = 0 To ListChapters.ListCount - 1
ListChapters.Cell(i, 4) = Str(i + 1) // Assuming Sequence is in the 5th column
Next
// Save the new Sequence data to the existing database
For i As Integer = 0 To ListChapters.ListCount - 1
app.db.SQLExecute("UPDATE Write SET Sequence = " + ListChapters.Cell(i, 4) + " WHERE ID = " + ListChapters.Cell(i, 1))
If app.db.Error Then MsgBox("DB Error: " + app.db.ErrorMessage)
Next
'Reload the database data
Dim rs As RecordSet = app.db.SQLSelect("SELECT * FROM Write ORDER BY Sequence")
// Clear the listbox and place the new data in it
ListChapters.RemoveAllRows
If rs <> Nil Then
While Not rs.EOF
ListChapters.AddRow(rs.Field("ChapterTitle").StringValue, rs.Field("ID").StringValue, rs.Field("TextWriteChaptertext").StringValue, rs.Field("FKStory").StringValue, rs.Field("Sequence").StringValue)
ListChapters.RowTagAt(ListChapters.LastAddedRowIndex) = ListChapters.Cell(ListChapters.LastAddedRowIndex, 1)
rs.MoveNext
Wend
End If
// Commit the transaction
app.db.CommitTransaction
ListChapters.Invalidate
The new row order isn’t accessible until the event loop after DragReorderRows. The old documentation has more information about the true purpose of this event ListBox.DragReorderRows - Xojo Documentation
It’s a shame Xojo is going to remove all of that great information for the sake of vanity in just a couple of weeks.
1 Like
I usually find more/better information faster on the old docs. Too bad they are going away.
Why renumber? Just use the index Value of each row.
Why reload? You already have everything in order. 
Just no… 
If we’re talking about a local database file or just a few dozen lines in the list, I recommend:
app.db.SQLExecute("UPDATE Write SET Sequence = " + i.ToString + " WHERE ID = " + ListChapters.Cell(i, 1))
just before the list is “closed”. Everything else just costs time and energy.
And while we’re at it, use the database Sequence column only to load the entries in the correct order. You don’t have to store them in the ListBox; that just requires memory.
I read the documentation, but still don’t understand how to get the new row order committed to the database immediately. I read suggestions that using a timer would help, creating a delay between the drag and the actually running the saving script and tried this, but it doesn’t work. How can I get the drag change committed immediately to the database?
Could you not update the database when the window is closed rather than every time a drag is performed?