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..

[f1 fan]

no easy way sorry.
You have to get an array of datarows where the surveyid = the surveyid of the row you deleted (140 in your example) and sorted on the question number column. Then run through each one and redo the question number using a count
e.g.

DataRow[] rows = QuestionTable.Select("SurveyID = " + deletedrow["surveyid"], "QuestionNum asc");
for (int i=0; i < rows.length; i++)
{
  rows[i]["QuestionNum"] = i+1;
}