How to update records in the database?

See more:

ASP.NET

SQL-Server-2008R2

Hi guys!

I have this scenario:
1. Add first a checklist
2. Add activities to the Checklist created
3. Assign that checklist to a milestone

Now, I followed the instructions. I got a problem in creating the activity code where if there are more than one activity added to the checklist when assigning a milestone. Because when I assigned a milestone to a checklist, it will generate a checklist code for the checklist and at the same time, the activities that the checklist contained will generate activity code based from the checklist code plus the order number that it was added. But I got same activity code for every activities. Is it possible to make the activity code differ from the other activity?How can I make the activity code differ from one another?

Here is my code:

protected void ImageButton5_Click(object sender, ImageClickEventArgs e)
   {
       connection();
       amicassaCon.Open();
       SqlCommand cmd = new SqlCommand("SELECT MAX(CAST(order_no as INT)) FROM checklists WHERE checklist_name='"+DropDownList2.SelectedValue+"'", amicassaCon);
       SqlDataReader dr = cmd.ExecuteReader();
       string a = "";
       //Order Number Generation for Checklist
       while (dr.Read())
       {
           if (dr[0].ToString() == "" || dr[0].ToString() == null)
           {
               a = "1";
           }
           else
           {
               if (dr[0].ToString() == "1")
               {
                   a = Convert.ToString(Convert.ToInt32(dr[0].ToString()) + 9);
               }
               else
               {
                   a = Convert.ToString(Convert.ToInt32(dr[0].ToString()) + 10);
               }
           }
       }
       dr.Close();
       // Generating Checklist Code
       string dr_cmdcode_get = "";
       SqlCommand cmdcode = new SqlCommand("SELECT milestone_code FROM milestones WHERE milestone_name='" + dd1.SelectedValue + "'", amicassaCon);
       SqlDataReader dr_cmdcode = cmdcode.ExecuteReader();
       while (dr_cmdcode.Read())
       {
           dr_cmdcode_get = dr_cmdcode[0].ToString();
           //dr_cmdord_no = dr_cmdcode[1].ToString();
       }
       dr_cmdcode.Close();
       checklistCode = dr_cmdcode_get + '-' + a;

       SqlCommand apdate = new SqlCommand("UPDATE checklists SET milestone_name='" + dd1.SelectedValue + "', checklist_code='" + checklistCode + "' WHERE checklist_name='" + DropDownList2.SelectedValue + "'", amicassaCon);
       SqlDataReader aps = apdate.ExecuteReader();
       aps.Close();


       SqlCommand cmd2 = new SqlCommand("SELECT MAX(CAST(order_no as INT)) FROM activities WHERE checklist_name = '" + dpdchecklist_activity_add.SelectedValue + "'", amicassaCon);
       SqlDataReader dr2 = cmd2.ExecuteReader();
       string b = "";
       //Order Number for Activity
       while (dr2.Read())
       {
           if (dr2[0].ToString() == "" || dr2[0].ToString() == null)
           {
               b = "1";
           }
           else
           {
               if (dr2[0].ToString() == "1")
               {
                   b = Convert.ToString(Convert.ToInt32(dr2[0].ToString()) + 9);
               }
               else
               {
                   b = Convert.ToString(Convert.ToInt32(dr2[0].ToString()) + 10);
               }
           }
       }
       dr2.Close();

       //Generating Activity Code

       string cget = "";
       SqlCommand cmdd = new SqlCommand("SELECT checklist_code FROM checklists WHERE checklist_name='" + DropDownList2.SelectedValue + "'", amicassaCon);
       SqlDataReader drcode = cmdd.ExecuteReader();
       while (drcode.Read())
       {
           cget = drcode[0].ToString();
           //dr_cmdord_no = dr_cmdcode[1].ToString();
       }
       drcode.Close();
       actCode = cget + '-' + b;

       SqlCommand actC = new SqlCommand("UPDATE activities SET milestone_name='" + dd1.SelectedValue + "', activity_code='" + actCode + "' WHERE checklist_name='" + DropDownList2.SelectedValue + "'", amicassaCon);
       SqlDataReader apss = actC.ExecuteReader();
       drcode.Close();



       amicassaCon.Close();

   }