Issue of Updating Serial Number in Database Table

[erncd1]

My entries are saved in a table having a field of Serial No alongwith other fields to keep them in sorted order. However when we reorder the record in MSFlexgrid/Listbox, what is the economical procedure adopted by professional to update that record order in the table.

The example is given below:

Sr. No., Saved Order, Changed Order

1, A, A
2, B, C
3, C, B
4, D, D
5, E, E

The letter C was placed previously at Sr. No.3. After shifting, it goes to Sr. No.2. My procedure is to store the previous index of letter “C” in a variable, open the table order by Sr. No. Move the record to InxdexVariable and change the value of field Sr. No. = IndexVariable -1 in case of shifting Up or Index Variable + 1 in case of Shifting Down. In a similar way I amend the second affecting record “B”.

I would like also to know the procedure in case of removing a record and then updating the rest of Sr. Nos. in a table.

[dglienna]

Loop from the END to the BEGINNING if you remove something. Doing it the wrong way will erase the record you're ABOUT to delete.

[erncd1]

That is ok for removing an item and updating serial. But what about shifting the Record Serial?

[dglienna]

Why shift the SR? Just delete it. Use an autonumber field, and it will be created automatically

[erncd1]

I think the use of an autonumber field won't be helpful if I am to insert a record in the middle. Insofar as the autoNumber field, that will insert the record at the last position. So that is why I am persisting with Manual Serial Number field.

I didn't use autonumber field in my programme but these are my assumptions.

[DataMiser]

When you insert a record into the database it goes to the end regardless of where it may appear on your display.

[dglienna]

I had a strange problem adding records to a collection. It seemed to add them in the MIDDLE, not matter how I stored the field. My controller was spitting out 1,3,2,4 or 3,1,2,4.

I was using DateTime fields, and the solution turned out to be to always manually SORT the collection, and bind the sort to the grid.

[erncd1]

When you insert a record into the database it goes to the end regardless of where it may appear on your display.

"Insert a record in the middle" was asked in the sense that when I will use autonumber field it will give last number to my newly added record, whereas I add the record in middle of MSFlexgrid/Listbox and want to give it relative Serial Number not Autonumber.

In simple, It is the matter of Just Serial Number and not placing of Record into Database. When I add record with its Serial Number (say) "5", there is also another record having serial number 5, which needs to be increased "6" and so on.

Hope this will further explain my problem.

[dglienna]

You NEVER want to change the serial number in a table, and definitely not when the DB decides to change it.

[vb5prgrmr]

So then your table design needs to be something like...

iID Autonumber used to keep records unique
iSortID Number this is what we will be sorting on when we request records from this table
vText Text/Varchar this is the data to be displayed.

Then your process would be to...

sqlString = "UPDATE tablename SET [tablename.iSortID]=[tablename.iSortID]+1 WHERE iSortID >= 5"
adoCn.Execute sqlString
sqlString = "INSERT INTO tablename(SortID, vText) VALUES(5, 'Do Hicky Name')"
adoCn.Execute sqlString

Then of course, to pull the data out...

sqlString = "SELECT vText FROM tablename ORDER BY iSortID"

Good Luck

[DataMiser]

You NEVER want to change the serial number in a table, and definitely not when the DB decides to change it.

Agreed, never change serial numbers and especially never insert one that would cause others to change. Serial number is normally a unique field used to identify an item quite often it is also barcoded on that item. If you change this at will then it is pretty much useless.