[VB.NET] How do we move DataGridView Rows up and down with buttons?

[maash]

Help. I need to move rows in a databound datagridview in VB.NET up or down with up and down buttons. Is there a good way of doing this? I was thinking if i could just update the sql server table and then refreshing the screen but I dont know how to go about it

Thanks

[azcodemonkey]

Do your rows have a rank to them? You can't really change how rows are stored in SQL Server, per se, but you can add a column to your data to give a row rank, then order by that. Then in your view, when you move rows, you'd change the rank value, update the db and rebind.

[maash]

Do your rows have a rank to them? You can't really change how rows are stored in SQL Server, per se, but you can add a column to your data to give a row rank, then order by that. Then in your view, when you move rows, you'd change the rank value, update the db and rebind.

yes what i have are 10 columns, 1 of them needs to remain the same when moving the rows up and down. I know that in a databound grid, we cant move rows but we can move them in the sql database and then refresh the grid. So for example i have 4 columns by_order, product_id, descr, by_set

so when I am moving the order of the row up and down , i want the by_order to remain the same, but only change product_id, descr and by_set and ordering the table by the by_order so it looks like the order has changed.

WHats the best way of doing this in sql ?

[azcodemonkey]

OK. Your explanation just hurt my head. LOL

Actually, you only want to update the ordering column, and leave the rest of your row data the same. You don't need to do anything other than that.

e.g.

row def as selected from sql ordered by rank ascending:

id, name, description, rank

-------------------------------

13, widget1, just a widget, 1

15, gadget, just a gadget, 2

21, doo hickey, it does something, 3

If you swap gadget and doo hickey in your view, you'd swap their rank, and nothing else, in your dataset/table. In your move_up method you'd select the rows that match the moving item's rank (3), and its rank minus 1. Then you'd update each row's rank with its new rank (2 for doo hickey, and 3 for gadget), then sort the rows again and rebind. It would be similar in a move_down method. Technically, you wouldn't even need to commit the changes to the database until all the moves were completed and the user chose to save them.

Check out the DataTable documentation and look at the Select method for specifics on how most of this would be done.

I'd give you a code example, but I'm currently at work. :)

[loople]

That's exactly what I do in all my code at work. Virtually all of my tables have an int 'SortOrder' column (equivilent to rank).

When you click the move up (as in up the grid) button on a given id you need to run an sql procedure something like...

// Find the row before the selected row and increment it's sort order

UPDATE myTable SET SortOrder=SortOrder+1 WHERE ID=(SELECT MAX(ID) FROM myTable WHERE ID<@SelectedID)

// Decrement the selected row's sort order

UPDATE myTable SET SortOrder=SortOrder-1 WHERE ID=@SelectedID