ReorderList, Database, Identity Questions

 Hi Guys,


The ReorderList control has driven me to desperate ends tonight.  I found that it will only work if my database has a field (OrderID in this case) of type int that it can use to keep track of the order.  The problem is that I'm not using the ReorderList control for inserting into the database.  This requires me to keep track of the OrderID myself and I have found that simply setting the OrderID field as the "Identity" will cause it not to work.


Common sense tells me that using the Identity is the best way to keep track of a ReorderList because it automatically numbers things as ther're inserted.  If something is deleted, it shouldn't matter because it will still be in order even to there's a gap in the numbering.


The only solution I can think of is trying having TWO identity fields in the database, but since that's not possible, a field that mirrors the Identity.  Can anyone advise me on how to do that? The below datasource does not insert anything into the OrderID field:


 

<asp:SqlDataSource ID="sdsItems" runat="server" ConnectionString='<%$ ConnectionStrings:testConnectionString %>'
SelectCommand="SELECT * FROM [Items]"
InsertCommand="INSERT INTO [Items] ([Name]) VALUES (@Name) SET @OrderID = SCOPE_IDENTITY()"
UpdateCommand="UPDATE [Items] SET [Name] = @Name, [OrderID] = @OrderID WHERE [ItemID] = @original_ItemID"
OldValuesParameterFormatString="original_{0}" DeleteCommand="DELETE FROM [Items] WHERE [ItemID] = @original_ItemID">
<InsertParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Direction="Output" Name="OrderID" Type="Int32" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="Name" Type="String" />
<asp:Parameter Name="OrderID" Type="Int32" />
<asp:Parameter Name="original_ItemID" Type="Int32" />
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_ItemID" Type="Int32" />
</DeleteParameters>
</asp:SqlDataSource>