Hi,
If I have a listbox which allows the user to drag reorder the rows - how do I get the rows to save in that order, so that the next time the listbox is opened, they appear as the user last dragged them?
I am sure it is something simple, but cannot seem to work out what code I put into the window close event, in order to do this.
Surely when the listbox is re-opened, the primary key will always show the rows in they order they were made, and not in the order to which they were dragged?
Normally what happens is you end up have a sequence field in the table and ordering by the sequence number rather than the primary key (or whatever).
It gets a bit tricky if you have multiple people accessing he data because they’ll all have their own drag order so then you need to have an intermediate many-to-many table that tracks userid and recorded and the users sequence order. Create a view that marries the original table to this many to many and query against it rather than the original table.
When the user closes the window, or after the drag event, update the many-to-many table to reflect the changes (and thus the view).
Bob,
Thanks, but I am a little bit lost regarding your advice.
(My database is only for 1 user).
Are you saying I need to create another column in the database, and name it “sequence”?
If so, what do I save in that column, and when do I save to it?
I am totally lost regarding this - do you know of any example code anywhere which would SHOW me how it works?
I could then take my time, study the code, and learn from it.
I have a more visual approach to learning, and cannot picture in my head at all - how this workaround works
Thank you for your patience and help - I really appreciate it.
If it’s only 1 user than put sequence in the table that feeds the Listbox. Then when saving, put the row number of the Listbox into the sequence field.
BE AWARE: It doesn’t do much error checking (it’s an example, after all!) and you might try database transactions to update many rows in a batch (which the example doesn’t do either).
Alex - that makes it a lot easier to understand, thank you - but I have 1 last question:
I have never used row tags before and I thought they were something that I needed to specifically set, but in your code, you reference it?
DB.SQLExecute("UPDATE anyTable SET position = " + str(i) + " WHERE id = " + lbData.RowTag(i).StringValue)
It’s set in the Action event of the Load button. Rowtag basically is a variant attached to a row - it’s not being displayed. I usually use it to store the record’s id to easily update the corresponding record in another method/event/…
I now understand the principle, but I seem to have a mental block on the row tag.
I understand all of the code below, apart from the last segment.
DB.SQLExecute("UPDATE anyTable SET position = " + str(i) + " WHERE id = " + lbData.RowTag(i).StringValue)
This (to me) seems to set position 1 to the row with an id row tag which is 1 - and then sets position 2 to the row with an id row tag of 2. Therefore, I cannot see how anything is changing??
Sorry for being so dumb, it’s really annoying that I understand it all apart from the very last part:(
Well, i is the position in the listbox whereas the rowtag stores the ID. So we’re just telling the database to update each record (corresponding to the IDs in the rowtag) with the current position (corresponding toi).
Might your mental block relate to the SQL code specifically? Have you ever used SQL “Update” syntax? Hopefully I can give you insights as soon as I know where your mental block originates
Richard, here’s a simple example. Suppose you load 3 rows into the listbox. The ID’s of the rows are 1, 2, 3:
Row : Rowtag
0 : 1
1 : 2
2 : 3
Then you drag the last row to the top. The data now looks like:
Row : Rowtag
0 : 3
1 : 1
2 : 2
Now when you save the data, you get
UPDATE anyTable SET position = 0 WHERE id = 3
UPDATE anyTable SET position = 1 WHERE id = 1
UPDATE anyTable SET position = 2 WHERE id = 2
"position = " uses the Row number of the table
"id = " uses the Rowtag, which holds the ID of the database record
Ok, If I rearrange the rows, so that they are in reverse order - Entry 3 would be at the top:
Entry 3: id = 3 anyrow = goodbye
Entry 2: id = 2 anyrow = world
Entry 1: id = 1 anyrow = hello
So I would expect the line of code below would set position 1 to the row which has the row tag (id) of 1 (which is actually Entry 1, which is now moved to the last row).
DB.SQLExecute("UPDATE anyTable SET position = " + str(i) + " WHERE id = " + lbData.RowTag(i).StringValue)
OH MY GOD I am so annoyed with myself - I am not stupid by any stretch of the imagination, but I cannot understand what the last segment of code is doing:
I fully understand the following:
[quote]Row : Rowtag
0 : 1
1 : 2
2 : 3
Then you drag the last row to the top. The data now looks like:
Row : Rowtag
0 : 3
1 : 1
2 : 2[/quote]
The part my brain refuses to comprehend is how the 2 bold segments amount to the same thing:
WHERE id = " + lbData.RowTag(i).StringValue)
UPDATE anyTable SET position = 0 WHERE id = 3
I think you’re missing the fact that the line of code
DB.SQLExecute("UPDATE anyTable SET position = " + str(i) + " WHERE id = " + lbData.RowTag(i).StringValue)
is in a loop, and will therefore be executed multiple times, once for each row in the listbox. Each time, it gets different values of i and lbData.RowTag(i).
Let me combine our 2 examples:
Row : Rowtag : Data
0 : 1 : hello
1 : 2 : world
2 : 3 : goodbye
Then you rearrange the rows as per your example. The data now looks like:
Row : Rowtag : Data
0 : 3 : goodbye
1 : 2 : world
2 : 1 : hello
The first time SQLExecute runs, i = 0 and RowTag(i) = 3, so you get
UPDATE anyTable SET position = 0 WHERE id = 3
The next time through the loop, i = 1 and RowTag(i) = 2
UPDATE anyTable SET position = 1 WHERE id = 2
The final time through the loop, i = 2 and RowTag(i) = 1
