Row Index for Inserting rows in datagrid to be stored in SQL database

[Technobeam]

I have a datagridview in my VB windows forms application that is bound to a dataset and written to a SQL database. The order of the items on the screen must be saved to the database so they can be retrieved in the same order that the user saved them. I currently have a row index column of type integer. when a user adds a row the first row is automatically numbered 100. each additional row added is in increments of 100. However, if a user inserts a row it is numbered in between the original rows. so if you insert between 100 and 200 the row is numbered 150. If you insert again between 100 and the new 150 the row is numbered 125 and so on. Once the different can no longer be split, the entire list is re-numbered from the beginning. Im trying to come up with a method that will limit the number of times renumbering occurs or eliminate it completely. If there are several hundred rows, this renumbering forces them all to be written back to the database which is an expensive and potentially lengthy process.

I can number the rows by thousands or tens of thousands or change the data type to decimal but both of these methods have limitations.

Any suggestions would be greatly appreciated.

Continue reading...