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

  • 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();
        }

    Friday, July 31, 2015 8:43 AM

Answers

  • User1686483761 posted

    Hi, sush rani

    sush rani

     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();
        }

    when  we click the button1,Here I am looping through the GridView Rows and checking whether the CheckBox is checked for that Row.

    If the CheckBox is checked ,and if that row is already exist, the record is updated in the database ,

    but  if that row is not exist,the record is inserted in the database.

    Could  you tell me what problem you have? so that we could better help you solve the problem.  

    for your code, I have a suggestion that you could put the operation of database in a class file ,so that your code looks more clear .

    Besides, About the use of gridview and checkbox ,

    you could refer to  http://www.aspsnippets.com/Articles/Bulk-Edit-Update-Multiple-Rows-in-ASPNet-GridView-using-CheckBoxes.aspx

    I hope it's helpful for you

    Best Regards

    cheng  zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 3, 2015 1:33 AM