5 posts in this topic

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 ?

Share this post


Link to post
Share on other sites

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. :)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.