ReorderList Delete problems in ASP.Net and VB

I have a working ReorderList on one of my webpages that displays questions in a survey. The item template contains two labels, one for the QuestionNumber (which is also the SortOrderField specified in the ReorderList control), and the QuestionText. I can reorder and insert items with no problems -- those features are functioning as expected.

However, when I implement the Delete functionality, it does delete a question, but it doesn't renumber the QuestionNumber. For example, if I have 4 questions (with question numbers 1 thru 4), and I delete question 2, I'm left with the question numbers 1, 3, and 4. This is probably by design, but does anyone know of any easy way to automatically renumber the items so that they would be 1, 2, and 3 instead of 1, 3, and 4?  I noticed that if I start dragging and dropping the remaining questions, they will eventually get reordered the way that they should be, but the user isn't going to want to do that.

Any ideas?
LVL 2
jacksonm1234Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GreymanMSCCommented:
You are probably best off maintaining sort order on addition and deletion.

For instance, your DeleteCommand should look something like this.  It determines that the Question exists, and hasn't been reordered by anyone else since you selected it, then attempts to delete the record, followed by renumbering all questions with higher values.  Just in case anything goes wrong with that (eg: records locked on you, et cetera) it will rollback on an exception.
IF (EXISTS (SELECT 1 FROM Questions WHERE QuestionID=@QuestionID AND QuestionNumber=@QuestionNumber)) 
BEGIN 
    BEGIN TRANSACTION BEGIN TRY
        DELETE FROM Questions WHERE QuestionID=@QuestionID AND QuestionNumber=@QuestionNumber; 
        UPDATE Questions SET QuestionNumber=QuestionNumber - 1 WHERE QuestionNumber > @QuestionNumber;
        COMMIT TRANSACTION
    END TRY 
    BEGIN CATCH 
        ROLLBACK TRANSACTION 
    END CATCH
END;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jacksonm1234Author Commented:
This was exactly what I needed. I just wasnt sure how to do this manually. THANK YOU!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.