Strored proc not working correctly:

[Sanjeet1]

When I added more data to the database the fileorder was not working correctly because the reason was that there is a 1 to many relationship with cases and files. So I have to take caseid into consideration. I modified the stored to handle this but I am still having problems.

When I try to move the file to the top location it does not move. I looked at the data in sql query analyzer and I noticed the sortorder for the file that's 1 does not get udated to 2 but the file id that should get updated to 1 gets updated fine. I don't why this occurs only when I move I ty to move to the top location.

I tested my stored proc to see if the correct file is being udated with the new sort number and the correct file works.

Any suggections?

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 user to 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, CaseId, Direction)
Ex. exec oc_OnlineCaseFileSortOrder 2, 17, 1 for moving down
oc_OnlineCaseFileSortOrder 2, 17, -1, for moving up





Here is my stored proc:

REATE PROCEDURE [dbo].[oc_OnlineCaseFileSortOrder]
/*****************************************************************************************
oc_OnlineCaseFileSortOrder
**************************************************************************
Description:
Moves the sort order of the file list
Output:
None
**************************************************************************
History:
12/14/05, ssk: Created
*****************************************************************************************/
@FileId int,
@CaseId 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
And o.CaseId = @CaseId


/*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.CaseId = @CaseId
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.CaseId = CaseId
AND o.SortOrder <= @NewSortOrder
ORDER BY o.SortOrder DESC



/*Set New Sort Order */

IF @AdjFileId IS NOT NULL

BEGIN

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


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


END




GO