Edit Table

9 Mar 2006 8:36 PM

gjtired

Hi

I am using a table to fill a dropdown box. The contents of the dropdown
box are ordered by a seq_no. I edit the seq_no and want to refresh the
dropdown box and show the new order. I can edit the table but the
changes to the seq_no is not changing the order of the dropdown box
when it is refreshed. Can anyone give me ideas on how to fix this?
Below is the code that I'm using.

//edit Question_Seq_No for record that is moved up
string sqlQuestionTempEditUP="UPDATE QuestionTemp SET Question_Seq_No =
'"+valSeqNum +"' " +
"WHERE Question_ID = "+valQuestionIDUp+" ";

SqlCommand sqlStatusCmdUP = new
SqlCommand(sqlQuestionTempEditUP,sqlConnection1);
sqlStatusCmdUP.CommandType = CommandType.Text;
daQuestionTemp.UpdateCommand =sqlStatusCmdUP;

sqlConnection1.Open();
rowcountUP=sqlStatusCmdUP.ExecuteNonQuery();
sqlConnection1.Close();

//Get the BTForm_Number
string sqlBTFormFind="SELECT * " +
"FROM BTForm " +
"WHERE BTForm_Status = 'Active' And ( BTForm_ID = "+valFormID+" )" ;

//sqlConnection1.Open();
SqlDataAdapter daBTFormFind=new SqlDataAdapter(sqlBTFormFind,
sqlConnection1);
dsMain1.BTForm.Clear();
daBTFormFind.Fill(dsMain1.BTForm);
DataTable dtBTForm = dsMain1.Tables["BTForm"];

DataRow BTFormRow;
BTFormRow = dtBTForm.Rows[0];

valBTFormNumberFind = (int)BTFormRow["BTForm_Number"];

drpQuestion.Items.Clear();

//Refresh the Question dropdown box
string sqlQuestionEdit="SELECT * " +
"FROM QuestionTemp " +
"WHERE (Question_Status = 'Active') And ( BTForm_Number =
"+valBTFormNumberFind+")" +
"Order By Question_Seq_No";

SqlDataAdapter daQuestionEdit=new SqlDataAdapter(sqlQuestionEdit,
sqlConnection1);
dsMain1.QuestionTemp.Clear();
daQuestionEdit.Fill(dsMain1.QuestionTemp);
DataTable questionDT = dsMain1.Tables["QuestionTemp"];

//fills the drpSelectQuestion
foreach (DataRow questionRow in questionDT.Rows)
{

   ListItem newquestion= new ListItem();

   newquestion.Text=questionRow["Question_Content"].ToString().Trim();
   newquestion.Value=questionRow["Question_ID"].ToString().Trim();
   drpQuestion.Items.Add(newquestion);
} //end foreach loop

9 Mar 2006 8:57 PM

Nicholas Paldino [.NET/C# MVP]

gjtired,

Instead of populating the items manually, why not bind to a DataView
where the sort order is set to the sequence number? Then, you can change
the order in the underlying data table, and it should update the list
automatically.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

Show quoteHide quote

"gjtired" <jon***@boystown.org> wrote in message
news:1141936599.561305.28390@p10g2000cwp.googlegroups.com...
> Hi
>
> I am using a table to fill a dropdown box. The contents of the dropdown
> box are ordered by a seq_no. I edit the seq_no and want to refresh the
> dropdown box and show the new order. I can edit the table but the
> changes to the seq_no is not changing the order of the dropdown box
> when it is refreshed. Can anyone give me ideas on how to fix this?
> Below is the code that I'm using.
>
> //edit Question_Seq_No for record that is moved up
> string sqlQuestionTempEditUP="UPDATE QuestionTemp SET Question_Seq_No =
> '"+valSeqNum +"' " +
> "WHERE Question_ID = "+valQuestionIDUp+" ";
>
> SqlCommand sqlStatusCmdUP = new
> SqlCommand(sqlQuestionTempEditUP,sqlConnection1);
> sqlStatusCmdUP.CommandType = CommandType.Text;
> daQuestionTemp.UpdateCommand =sqlStatusCmdUP;
>
> sqlConnection1.Open();
> rowcountUP=sqlStatusCmdUP.ExecuteNonQuery();
> sqlConnection1.Close();
>
> //Get the BTForm_Number
> string sqlBTFormFind="SELECT * " +
> "FROM BTForm " +
> "WHERE BTForm_Status = 'Active' And ( BTForm_ID = "+valFormID+" )" ;
>
> //sqlConnection1.Open();
> SqlDataAdapter daBTFormFind=new SqlDataAdapter(sqlBTFormFind,
> sqlConnection1);
> dsMain1.BTForm.Clear();
> daBTFormFind.Fill(dsMain1.BTForm);
> DataTable dtBTForm = dsMain1.Tables["BTForm"];
>
> DataRow BTFormRow;
> BTFormRow = dtBTForm.Rows[0];
>
> valBTFormNumberFind = (int)BTFormRow["BTForm_Number"];
>
> drpQuestion.Items.Clear();
>
> //Refresh the Question dropdown box
> string sqlQuestionEdit="SELECT * " +
> "FROM QuestionTemp " +
> "WHERE (Question_Status = 'Active') And ( BTForm_Number =
> "+valBTFormNumberFind+")" +
> "Order By Question_Seq_No";
>
> SqlDataAdapter daQuestionEdit=new SqlDataAdapter(sqlQuestionEdit,
> sqlConnection1);
> dsMain1.QuestionTemp.Clear();
> daQuestionEdit.Fill(dsMain1.QuestionTemp);
> DataTable questionDT = dsMain1.Tables["QuestionTemp"];
>
> //fills the drpSelectQuestion
> foreach (DataRow questionRow in questionDT.Rows)
> {
>
>   ListItem newquestion= new ListItem();
>
>   newquestion.Text=questionRow["Question_Content"].ToString().Trim();
>   newquestion.Value=questionRow["Question_ID"].ToString().Trim();
>   drpQuestion.Items.Add(newquestion);
> } //end foreach loop
>