Jonathan Wood
5/22/2008 5:34:00 AM
Cor,
> As you really want a location in your database, then add a placenumber as
> item, however as soon as two users are doing this, then the computer will
> only be busy changing the placenumbers.
>
> Just my opinion.
I'm not sure I get your point here. But you seem to have an issue with the
approach I'm taking.
Please, can you offer even a single way to control item order without a
placeholder column in the table? If so, I'd love to hear it.
Jonathan
>
> "Jonathan Wood" <jwood@softcircuits.com> schreef in bericht
> news:u$7GNn2uIHA.1236@TK2MSFTNGP02.phx.gbl...
>> Cor,
>>
>>> You reply probably that you don't have anything on my answer.
>>
>> I'm unable to parse this. Your last reply to me seemed to indicate that
>> you decided you were not the one to give me an answer. I accepted what I
>> understood you to be telling me.
>>
>>> However, you are probably the only one on earth who want to order his
>>> data fixed in the database.
>>>
>>> The way your data is ordered for a user is a user interface part. Not
>>> even a datalayer part, and for sure not an action in a database table.
>>
>> I'm at a loss as to why you're getting hung up on this particular issue.
>>
>> There is no way to reliably order a table without having a field that is
>> suitable for ordering by. So I needed to create a field for this purpose
>> along with a couple of stored procedures for special handling of this
>> field. That IS a database part.
>>
>> As far as my interface, the only reason I even mentioned the UP/DOWN
>> buttons is because I was asked. To me, they are irrelevant to what I'm
>> asking about these database-design issues.
>>
>> Jonathan
>>
>>> "Jonathan Wood" <jwood@softcircuits.com> schreef in bericht
>>> news:%23xuSVxpuIHA.3604@TK2MSFTNGP03.phx.gbl...
>>>> So... I've implemented a feature to allow users to control the order of
>>>> items in one of my tables. This involves adding a new column to my
>>>> table, SortOrder.
>>>>
>>>> And I have two stored procedures that deal with this new column: One
>>>> that creates a new item with a SortOrder value higher than any that
>>>> exist. And another that swap the SortOrder values between two rows.
>>>>
>>>> But now I need help in making these two stored procedures robust in
>>>> multi-user situations.
>>>>
>>>> My create routine looks like this:
>>>>
>>>>>>>>>
>>>> DECLARE @SortOrder int
>>>> SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails WHERE
>>>> WorkoutID = @WorkoutID)
>>>> IF @SortOrder IS NULL
>>>> SELECT @SortOrder = 1
>>>> ELSE
>>>> SELECT @SortOrder = (@SortOrder + 1)
>>>>
>>>> INSERT INTO mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets,
>>>> Minutes, Comments, SortOrder)
>>>> VALUES (@WorkoutID, @ActivityID, @Reps, @Sets, @Minutes, @Comments,
>>>> @SortOrder)
>>>>>>>>>
>>>>
>>>> And my swap routine looks like this:
>>>>
>>>>>>>>>
>>>> DECLARE @tmp int
>>>> SELECT @tmp = (SELECT SortOrder FROM mc_WorkoutDetails WHERE ID = @ID1)
>>>> UPDATE mc_WorkoutDetails SET SortOrder = (SELECT SortOrder FROM
>>>> mc_WorkoutDetails WHERE ID = @ID2) WHERE ID = @ID1
>>>> UPDATE mc_WorkoutDetails SET SortOrder = @tmp WHERE ID = @ID2
>>>>>>>>>
>>>>
>>>> Any suggestions (even suggestions about my existing approach)
>>>> appreciated!
>>>>
>>>> Thanks.
>>>>
>>>> Jonathan
>>>>
>>>
>>
>