delete data, then shift its number....

[shy_wani]

hi..good day to all

i have a table: Question
atttributes: SurveyID(FK),QuestionID(PK),QuestionNum,Question and QuestionTypeID

if i have a dropdown list specifying the list of QuestionNum in my ASPX page and a delete button besides the list, how can i delete one QuestionNum? after delete, how can i shift the Question Number without changing the QuestionID?

examples: SurveyID=140 has QuestionNum=10
so, QuestionNum would be from 1-10...
if i were to delete QuestionNum=3...i want to delete QuestionNum=3 and at the same time, i want other QuestionNum after the deleted ones (means, QuestionNum=4 to QuestionNum=10) to shift their value...means...Question=4 is now QuestionNum=3,QuestionNum=5 to be QuestionNum=4, and so on...

how can i perform this? please help me..

[MidiMagic]

You would have to sort on QuestionNum, and then refill the field sequentially

[cfAllie]

--- delete selected question
delete from Questions
where SurveyID = 140
and QuestionNum = 3

--- decrease subsequent numbers
update Questions
set QuestionNum = QuestionNum -1
where SurveyID = 140
and QuestionNum > 3