Reorder Listbox by Dragging Rows, and Write New Order to the Database

Good morning!

I am attempting to do something that I think should be simple, but I have been failing for days and I think my approach must be flawed and I was hoping for some input. Please excuse the length of the post, I wanted to be as detailed as possible in the steps I have taken and my reasoning.

I want to be able to reorder a list in a listbox by dragging rows, update the integer that I am using to sort the list by, and write the new order to the database. This seems really simple, but I have not been able to achieve it nor can I find any examples for this - all the listbox dragging examples I have been able to find are for much more complicated cases. If I have overlooked a sample or example file (likely) I would be happy to be pointed in the right direction.

I have a non-hierarchal listbox, with a list of categories. There are two columns - the categoryName and the categoryRank. When the listbox is populated from the database, the recordID is written to the rowTag, and the name and category are written to cellTags for cells 0 and 1 respectively. The listbox is sorted by categoryRank (an integer). The cellTextPaint event copies the data to the corresponding cells to be visible to the user. This all works fine.

In the open event, I have EnableDragReorder = True. When I run the file, I am able to drag and reorder the rows. What I can’t seem to figure out is how to then correctly adjust the categoryRank to reflect the new order.

In the DragReorderRows event, I have the following:

// in the initial load, categoryRank is sequential 
// and equal to row number +1 - row 0 = rank 1, row 1 = rank 2, etc

me.CellTag(newPosition, 1) = newPosition +1 // compare the categoryRank to the new row
me.cell(newPosition, 1) = me.celltag(newPosition, 1) // write the new cellTag value to the corresponding cell so it is visible

MsgBox(me.CellTag(newPosition, 1)) // I have this in temporarily to get some visual feedback

If I run just this code, it seems to work mostly as I expect. I can drag a row to a new spot in the list, and the msgBox tells me that the cell tag value (categorySort) is what I expect it to be. The value visible in the cell, however, does not update.

Now that I have moved a row, I want to renumber everything else, so that the values for categoryRank are once again sequential with no repeats, update the listbox to reflect the new order, and update the database. To do that, I added the code below, which follows the above code in the DragReorderRows event:

for i as integer = 0 to categoryListbox.ListCount -1 // loop thru the listbox
  if i +1 <>  categoryListbox.CellTag(i,1) then // check the categorySort value against the current position
    categoryListbox.CellTag(i,1) = i +1 // if they don't align, make the rank equal the apparent row number
    me.cell(i, 1) = me.celltag(i,1) // write the new cellTag value to the corresponding cell so it is visible
  
    app.db.sqlExecute("BEGIN TRANSACTION") // update the database record
    app.db.SQLExecute ("UPDATE itemCategories SET categoryRank =  '%"+ categoryListbox.CellTag(i,1) +"%' WHERE IDX = '%"+ categoryListbox.RowTag(i) +"%' ")
    app.db.Commit

  end if
next

me.Sort
me.Refresh

With this code added, the following happens:
If I drag the first row (item 1, listbox row 0, apparent row 1, with a rank of 1) to the last position (listbox row 4, apparent row 5), the msgBox tells me the cellTag corresponding to rank is now 5, and the listbox looks like this:
Item 2 | Rank 2
Item 3 | Rank 3
Item 4 | Rank 4
Item 5 | Rank 5
Item 1 | Rank 1

The list is in the order I wanted, but the visible values are from the previous state.

If I then drag the new first row (item 2, listbox row 0, apparent row 1) to the last position (listbox row 4, apparent row 5), the msgBox tells me the cellTag corresponding to rank is now 5, and the listbox looks like this:
Item 3 | Rank 2
Item 4 | Rank 3
Item 5 | Rank 4
Item 1 | Rank 5
Item 2 | Rank 1

Again, the list is in the order I wanted, but the visible values are from the previous state.

If I do that same action again, I get the same thing:
Item 4 | Rank 2
Item 5 | Rank 3
Item 1 | Rank 4
Item 2 | Rank 5
Item 3 | Rank 1

So it seems like maybe the renumbering is happening, but not refreshing right. The database is also not updating, so maybe the data is not being changed at all and only the UI is being changed.

To try and determine which of those was true, I added a second msgBox to get some feedback, and now the whole block of code looks like this:

// in the initial load, categoryRank is sequential 
// and equal to row number +1 - row 0 = rank 1, row 1 = rank 2, etc

me.CellTag(newPosition, 1) = newPosition +1 // compare the categoryRank to the new row
me.cell(newPosition, 1) = me.celltag(newPosition, 1) // write the new cellTag value to the corresponding cell so it is visible

MsgBox(me.CellTag(newPosition, 1)) // I have this in temporarily to get some visual feedback

for i as integer = 0 to categoryListbox.ListCount -1 // loop thru the listbox
  if i +1 <>  categoryListbox.CellTag(i,1) then // check the categorySort value against the current position
    categoryListbox.CellTag(i,1) = i +1 // if they don't align, make the rank equal the apparent row number
    me.cell(i, 1) = me.celltag(i,1) // write the new cellTag value to the corresponding cell so it is visible
    MsgBox( "listcount = " + i.totext + " and cell tag = " + val(categoryListbox.CellTag(i,1)).totext )

    app.db.sqlExecute("BEGIN TRANSACTION") // update the database record
    app.db.SQLExecute ("UPDATE itemCategories SET categoryRank =  '%"+ categoryListbox.CellTag(i,1) +"%' WHERE IDX = '%"+ categoryListbox.RowTag(i) +"%' ")
    app.db.Commit

  end if
next

me.Sort
me.Refresh

With this code, if I drag the first row to the bottom, the first msgBox fires and tells me the new cellTag is 5, as expected. The second msgBox does not fire. The database does not update.

If I repeat that, and drag item 2 (which is now at the top) to the bottom, I am told that it’s cellTag is now 5, and NOW the second msgBox starts firing, and as far as I can tell it is reporting the state of everything as I expect. However the visible numbers do not update.

If I comment out the if / then, and just let the loop fire on everything no matter what, it fires on the first attempt to reorder but the listbox still does not accurately display the numbers nor does the database update.

So - as I said at the beginning, I feel like this should be simple, so I think my approach is fundamentally flawed, but I don’t see where. Any feedback would be greatly appreciated.

Thanks

Andrew

1 Like

after DragReorderRows event, just do a loop ( for i as integer = 0 to categoryListbox.ListCount -1 ), change the rank using categoryListbox.cell(i, 2) = cstr(i), read the values using categoryListbox.cell(i, 2) and update the table using sql update
i think is a simple problem, maybe i misundertood something? (my english level is not advanced)

1 Like

I think that is what I was doing, though your code is less complicated and more elegant than mine. This does not seem to change anything though - if I make the code in the DragReoderRows event to be only:

for i as integer = 0 to categoryListbox.ListCount -1
  categoryListbox.CellTag(i,1) = i+1
  me.cell(i, 1) = me.celltag(i,1)
next

me.Refresh
me.Sort

the value for the categoryRank still does not update, everything behaves exactly as I described above.

1 Like

could you make a copy of the project (just for precaution, to not break your code) and fill the listbox with the new values using just the following code?

for i as integer = 0 to categoryListbox.ListCount -1 categoryListbox.Cell(i,1) = i+1 next

1 Like

why do you use sort if the user already has ordered the rows?

1 Like

even using just those lines makes no difference, the numbers do not update.

I added the sort in as a kind of test - when I started trying to do this, I was not showing the rank in the listbox and I figured if the rank was not updating than the sort would show that to me.

1 Like

I made a quick file that is just a listbox, no database, with the same general structure as what I am trying to do in my larger program. It behaves as described above. The file is here if anyone cares to look at it:

1 Like

Direct download link:
https://www.dropbox.com/s/5k2s1cflq4ai4ev/LBTest.xojo_binary_project?dl=1

I downloaded your projectÂ… and I do not understand what to do with it.

Manual reorder works fine, but that is all (if I can say that).

I added the Change Event and paste your DragReorderCode into it: this works.

Your project do works the same in 2015r1Â… DragReorderRow is fired.

Sorry, I do not found the error too.

1 Like

I’ve run into this problem when trying to renumber a listbox after dragging rows. My solution was to start a timer (e.g. 100ms) in the DragReorderRows event. In the timer’s Action event you do the renumbering and your db stuff.

1 Like

Emile, thanks for looking at it.

The manual reorder does work. What I want to have happen after the manual drag is for the numbering in the second column to update to reflect the new order. So, for example, if I drag Item One to the third row, I want it to result in:
Item 2 | Rank 1
Item 3 | Rank 2
Item 1 | Rank 3
Item 4 | Rank 4
Item 5 | Rank 5

instead of what I am getting, which is:
Item 2 | Rank 2
Item 3 | Rank 3
Item 1 | Rank 1
Item 4 | Rank 4
Item 5 | Rank 5

1 Like

Julia, thank you for the input. I had wondered if I needed to use some other event to make this work. I tried mouseUp but that didn’t help and anyway seemed like it would cause me other problems at some point. I will try a timer.

Julia, thank you, that works exactly I as want it to.

1 Like

Excellent! :smiley:

1 Like

From the docs for DragReorderRows:

[quote]For non-hierarchical ListBoxes, row reordering is done for you if you return False. If you want to do the reordering by yourself, you should return True.
[/quote]
That indicates that the event fires before the row order is actually changed, which is why your code didn’t do what you expected - the dragged row isn’t in the NewPosition yet, so you’re operating on the wrong row. Putting the code in a timer causes it to run after the rows have been reordered.

1 Like

And there’s the rub - doing the drag reordering yourself is a massive PITA. Much easier to let Xojo do the reorder and then handle renumbering later via timer.

Thanks for reminding me of why I did it this way, oh so many years ago :slight_smile:

1 Like