Grid veiw row select and update and insert in checkbox list itme more than (3 chcekbox)

Question

User1509258523 posted

 protected void Button1_Click(object sender, EventArgs e)
    {
        string diaryno = getvalue("select max(letter_current_diary_no) as letter_current_diary_no from offices where office_code = '" + Session["user_code"].ToString() + "'");

                string offcode = GetOfficecode("select OFFICE_CODE from USERS where USER_CODE='" + Session["user_code"].ToString() + "'");
              
                foreach (GridViewRow row in GridView1.Rows)
                {
                    if (row.RowType == DataControlRowType.DataRow)
                    {
                       
                        CheckBox myCheckBox = row.FindControl("cbSelect") as CheckBox;
                        if (myCheckBox.Checked == true)
                        {
                            Label lblid = row.FindControl("lblid") as Label;
                          
                            
                            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
                            try
                            {

                                int i = 0;

                               
                                if (con.State == ConnectionState.Closed)
                                    con.Open();
                                SqlTransaction trn = con.BeginTransaction();

                                string cmdText1 = "update lettertrans set NEXT_OFFICE_CODE =@NEXT_OFFICE_CODE ,desp_date=@desp_date, desp_time= @desp_time,Desp_flag=@Desp_flag,next_office_receipt_status=@next_office_receipt_status,despatch_date_time=@despatch_date_time ,Transit_flag=@Transit_flag,Remarks=@Remarks where id=@id" ;
                                SqlCommand cmd2 = new SqlCommand(cmdText1, con, trn);
                               cmd2.Parameters.AddWithValue("@id",  lblid.Text.Trim());
                               // cmd2.Parameters.AddWithValue("@diary_no", row.Cells[4].Text);
                              //  cmd2.Parameters.AddWithValue("@office_code", offcode);
                                cmd2.Parameters.AddWithValue("@NEXT_OFFICE_CODE", CheckBoxList.SelectedValue);
                                cmd2.Parameters.AddWithValue("@desp_date", DateTime.Now);
                                cmd2.Parameters.AddWithValue("@desp_time", DateTime.Now);
                                cmd2.Parameters.AddWithValue("@Desp_flag", "1");
                                cmd2.Parameters.AddWithValue("@next_office_receipt_status", "0");
                                cmd2.Parameters.AddWithValue("@despatch_date_time", DateTime.Now);
                                cmd2.Parameters.AddWithValue("@Transit_flag", "1");
                                cmd2.Parameters.AddWithValue("@Remarks", txtremarks.Text);
                                i = cmd2.ExecuteNonQuery();
                                
                                if (i >=0)
                                {
                                    i = 0;

                                    string str = "insert into lettertrans (file_letter, letter_office_code, letter_diary_no,PREV_OFFICE_CODE,PREV_OFFICE_DIARY_NO,  receipt_date, receipt_time,  receipt_flag, transit_flag, receipt_date_time, is_new, ad_file_code,next_office_code,ns_file_code,   desp_date, desp_time, desp_flag, next_office_receipt_status,despatch_date_time,comments )  values(@file_letter,@letter_office_code,@letter_diary_no,@PREV_OFFICE_CODE,@PREV_OFFICE_DIARY_NO, @receipt_date, @receipt_time, @receipt_flag, @transit_flag,@receipt_date_time, @is_new,@ad_file_code,@next_office_code,@ns_file_code, @desp_date, @desp_time,@desp_flag,@next_office_receipt_status,@despatch_date_time,@comments)";
                                   // string str = "insert into lettertrans(diary_no,office_code)values (@diary_no,@office_code)";
                                    SqlCommand cmdlt = new SqlCommand(str, con, trn);
                                    {
                                       // cmdlt.Parameters.AddWithValue("@id",lblid.Text );
                                        cmdlt.Parameters.AddWithValue("@FILE_LETTER", "L");
                                        cmdlt.Parameters.AddWithValue("@LETTER_OFFICE_CODE", row.Cells[2].Text);
                                        cmdlt.Parameters.AddWithValue("@letter_diary_no", row.Cells[4].Text);
                                       cmdlt.Parameters.AddWithValue("@PREV_OFFICE_CODE", offcode);
                                        cmdlt.Parameters.AddWithValue("@PREV_OFFICE_DIARY_NO", diaryno);
                                      //  cmdlt.Parameters.AddWithValue("@diary_no", diaryno);
                                       // cmdlt.Parameters.AddWithValue("@OFFICE_CODE", offcode);
                                        cmdlt.Parameters.AddWithValue("@RECEIPT_DATE", System.DateTime.Now.ToString("yyyy-MM-dd "));
                                        cmdlt.Parameters.AddWithValue("@RECEIPT_TIME", System.DateTime.Now.ToString("yyyy-MM-dd "));
                                        cmdlt.Parameters.AddWithValue("@RECEIPT_FLAG", "1");
                                        cmdlt.Parameters.AddWithValue("@transit_flag", "1");
                                        cmdlt.Parameters.AddWithValue("@Receipt_Date_Time", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        cmdlt.Parameters.AddWithValue("@is_new", "1");
                                        cmdlt.Parameters.AddWithValue("@ad_file_code", "L");
                                        cmdlt.Parameters.AddWithValue("@ns_file_code", "SL");
                                        cmdlt.Parameters.AddWithValue("@next_office_code", CheckBoxList.SelectedValue);
                                        cmdlt.Parameters.AddWithValue("@next_office_receipt_status", "0");
                                        cmdlt.Parameters.AddWithValue("@DESP_Date", System.DateTime.Now.ToString("yyyy-MM-dd"));
                                        cmdlt.Parameters.AddWithValue("@DESP_Time", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                        cmdlt.Parameters.AddWithValue("@DESP_flag", "0");
                                     cmdlt.Parameters.AddWithValue("@despatch_date_time", System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                                     cmdlt.Parameters.AddWithValue("@comments", txtremarks.Text);
                                    i = cmdlt.ExecuteNonQuery();

                                     if (i >= 0)
                                        {
                                            i = 1;
                                        }
                                    }
                               }

                                else
                                {
                                    throw new Exception("Insertion Failed");
                                }
                                if (i == 1)
                                {
                                    trn.Commit();
                                    BindData();
                                    ClientScript.RegisterStartupScript(this.GetType(), "Alert", "alert('Letter dispatch successfully...');", true);
                                }
                                else
                                {
                                    trn.Rollback();
                                    ClientScript.RegisterStartupScript(this.GetType(), "Alert", "alert('Failed');", true);
                                }
                            }
                            catch (Exception ex)
                            {
                                ClientScript.RegisterStartupScript(this.GetType(), "Alert", string.Format("alert('" + ex.Message + "');"), true);
                            }
                            finally
                            {
                                if (con.State == ConnectionState.Open)
                                    con.Close();
                            }
                        }
                    }
                }
                BindData();
    }