Stored proc not working correctly

[Sanjeet1]

I have a file list inside a datagrid that looks like this:


FileName FileDescription

box box names moveup movedown
cats cat names moveup move down

moveup and move down are links that allow the userto rearrange the fileorder.


This is how the data is displayed in the database for filelist table:

FileId FileName SortOrder
1 Box 1
2 Cat 2
3 Kite 3

When the user clicks the moveup or move down order this stored procedure is called:

oc_OnlineCaseFileSortOrder (FileId, Direction)
Ex. exec oc_OnlineCaseFileSortOrder 2, 1 for moving down
oc_OnlineCaseFileSortOrder 2, -1, for moving up

The files move up and down but sometimes the I have to click several times. The problem is when a file id is updated with a new sortorder #, a sortorder already belongs to an older file so it causes problems in displayin the files in the correct file. I do have some sql logic to handle that by replacing the fileid with the older sortorder with the new sort but it does ot work. Here is my stored proc:



CREATE PROCEDURE [dbo].[oc_OnlineCaseFileSortOrder]
/*****************************************************************************************
oc_OnlineCaseFileSortOrder
**************************************************************************
Description:
Moves the sort order of the file list
Output:
None
**************************************************************************
History:
12/12/05, ssk: Created
*****************************************************************************************/
@FileId int,
@Direction int

AS
SET NOCOUNT ON

/* Declarations */

Declare @CurrentSortOrder int,
@NewSortOrder int,
@AdjFileId int

/*Get Current Info */

SELECT
@CurrentSortOrder = o.SortOrder,
@NewSortOrder = o.SortOrder + @Direction
FROM oc_onlinecasefile o
WHERE o.FileId = @FileId

/*Reorder the file list */


IF @Direction > 0
SELECT TOP 1 @AdjFileId= o.FileId
FROM dbo.oc_onlinecasefile o WITH (nolock)
WHERE o.FileId = @FileId
AND o.SortOrder >= @NewSortOrder
ORDER BY o.SortOrder
ELSE
SELECT TOP 1 @AdjFileId= o.FileId
FROM dbo.oc_onlinecasefile o WITH (nolock)
WHERE o.FileId = @FileId
AND o.SortOrder <= @NewSortOrder
ORDER BY o.SortOrder DESC


/*Set New Sort Order */

UPDATE oc_onlinecasefile
SET SortOrder = @NewSortOrder
WHERE FileId = @FileId

UPDATE oc_onlinecasefile
SET SortOrder = @CurrentSortOrder
WHERE FileId = @AdjFileId


GO

 

[PHV]

Why not simply something like this:
UPDATE oc_onlinecasefile
SET SortOrder = SortOrder + @Direction
WHERE (SortOrder <= @CurrentSortOrder AND @Direction < 0)
OR (SortOrder >= @CurrentSortOrder AND @Direction > 0)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, disregard my previous post.
Completely misunderstood the question :~/