December 7, 2006 at 8:38 am
I have a table that has a column called DisplayOrder which stores an int value denoting the display order in which the items are to appear in the .aspx page.
ColumnText ObjectType DisplayOrder
OrderNumber TextBox 1
OrderDate TextBox 2
ProductName TextBox 3
Now, I need to insert a new row in the middle of the table...
OrderTaker TextBox 3
which will need a display order value of 3, which is already taken. How would I, via a script, renumber the display order values? i.e. existing 3 becomes 4 and so on down the table.
Thanks
December 7, 2006 at 9:05 am
First make space
update mytable
set DisplayOrder = DisplayOrder + 1
where DisplayOrder >= 3
THen Insert
December 7, 2006 at 11:07 am
Thank you very much!
December 8, 2006 at 7:36 am
The solution will work, however, to avoid something like this in the future you could number them differently. Instead of numbering them 1, 2, 3, 4.... you can number them 10, 20, 30, 40.... You can still order them by DisplayOrder, except that if you need to add a new item somewhere in the list its as easy as making it number 11 and that way you won't have to worry about renumbering the other ones. Also comes in handy for things like the tabindex on forms
December 8, 2006 at 9:36 am
It all depends on how often you need to insert data between records. If it is a rare occurance then there is little or no need to keep room available. If it is done often then the optimization to keep space available can help with performance. In any case you would need to create room eventually as the space may fill up. In which case you'll still need to reorder. An now you'll need to have code to detect when the reording needs to be done.
December 8, 2006 at 12:46 pm
Use an Instead Of trigger. The sample code below would be more efficient if there is some unique key field that you could use to join the records instead of using the various other fields.
Create Trigger dbo.TR_ReorderInsert On dbo.DisplayTable
Instead Of Insert
As
Declare @DisplayTable Table (NewOrder int identity(1, 1) not null primary key)
ColumnText nvarchar(100) not null,
ObjectType nvarchar(100) not null,
DisplayOrder decimal (9, 1) not null)
If Not Exists(Select 1 From dbo.DisplayTable DT
Inner Join inserted i On i.DisplayOrder = DT.DisplayOrder)
Begin
-- DisplayOrder does not exist so simply insert without reordering
Insert Into dbo.DisplayTable(ColumnText, ObjectType, DisplayOrder)
Select ColumnText, ObjectType, DisplayOrder
From inserted
End
Else
Begin
Insert Into @DisplayTable (ColumnText, ObjectType, DisplayOrder)
Select ColumnText, ObjectType, Cast(DisplayOrder as Decimal(9, 1)) As theDisplayOrder
From dbo.DisplayTable with(nolock)
Union
Select ColumnText, ObjectType, Cast(DisplayOrder as Decimal(9, 1)) - 0.1
From inserted
Order By theDisplayOrder
Update DT
Set DT.DisplayOrder = tDT.NewOrder
From dbo.DisplayTable DT
Inner Join @DisplayTable tDT On DT.ColumnText = tDT.ColumnText And DT.ObjectType = tDT.ObjectType
Where DT.DisplayOrder <> tDT.NewOrder
Insert Into dbo.DisplayTable(ColumnText, ObjectType, DisplayOrder)
Select ColumnText, ObjectType, NewOrder
From @DisplayTable tDT
Where Not Exists (Select 1 From dbo.DisplayTable with(nolock)
Where ColumnText = tDT.ColumnText
And ObjectType = tDT.ObjectType)
End
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply