locked
Save Record RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I have below code and want that if error comes in between then Data should not be Updated in any table. It should be updated only if no error comes in between

    Try {
    con.Open();                 SqlCommand cmd;                 cmd = new SqlCommand("update [Temp] set [Status]=@Stat where [No_]=@Cu", con);                 cmd.CommandType = CommandType.Text;                 cmd.Parameters.AddWithValue("@Cust", lblcu.Text);                 cmd.Parameters.AddWithValue("@Stat", lblstat.Text);                 cmd.ExecuteNonQuery();                 cmd.Dispose();                 con.Close();                                 con.Open();                 HiddenField hNo = item.FindControl("hNo") as HiddenField;                 cmd = new SqlCommand("update [tbl_Test] set [Status]='V' where [ENo]=@hNo", con);                 cmd.CommandType = CommandType.Text;                 cmd.Parameters.AddWithValue("@ENo", hNo.Value);                 cmd.ExecuteNonQuery();                 cmd.Dispose();                 con.Close();
    }
    catch (Exception ex)
    {

    }

    Thanks

    Wednesday, June 27, 2018 8:51 AM

Answers

  • User-1171043462 posted
    1. When you use "using" there is no need to call Dispose.
    2. con.Open() => Opens connection

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 28, 2018 8:03 AM

All replies

  • User-1171043462 posted

    You need to use SqlTransaction class

     
                try
                {
                    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["const"].ConnectionString))
                    {
                        SqlTransaction tran = con.BeginTransaction();
                        string sql = "update [Temp] set [Status]=@Stat where [No_]=@Cu";
                        using (SqlCommand cmd = new SqlCommand(sql, con, tran))
                        {
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.AddWithValue("@Cust", lblcu.Text);
                            cmd.Parameters.AddWithValue("@Stat", lblstat.Text);
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                        }
    
                        sql = "update [tbl_Test] set [Status]='V' where [ENo]=@hNo";
                        using (SqlCommand cmd = new SqlCommand(sql, con, tran))
                        {
                            HiddenField hNo = item.FindControl("hNo") as HiddenField;
                            cmd.CommandType = CommandType.Text;
                            cmd.Parameters.AddWithValue("@ENo", hNo.Value);
                            con.Open();
                            cmd.ExecuteNonQuery();
                            con.Close();
                        }
                        tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                }
            }

    Wednesday, June 27, 2018 9:01 AM
  • User-1499457942 posted

    Hi

      What is the difference between below 2 Statements

    1. using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["const"].ConnectionString))
    2. con.Open();

    Thanks

    Thursday, June 28, 2018 5:37 AM
  • User-1171043462 posted
    1. When you use "using" there is no need to call Dispose.
    2. con.Open() => Opens connection

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 28, 2018 8:03 AM