Values getting cleared

Question

User-1499457942 posted

Hi

  When i debug and check the database it is updating records but when the process or function which i am calling gets completed values in Database gets cleared . I have not uses any RollBack like condition

        protected void BfbCalculation()

        {

            string m_DocNo = "";

            string m_ItemNo = "";

            decimal Amount = 0;

            decimal Amount0 = 0;

            try

            {

                using (SqlConnection con = new SqlConnection(CommonFunction.connectionString))

                {

                    if (con.State == ConnectionState.Closed)

                    {

                        con.Open();

                    }

                    SqlCommand sqlCommand = new SqlCommand("Select * from [Structure]", con);

                    SqlDataAdapter ad = new SqlDataAdapter(sqlCommand);

                    DataTable dt = new DataTable();

                    ad.Fill(dt);

                    if (dt.Rows.Count > 0)

                    {

                        for (int i = 0; i < dt.Rows.Count; i++)

                        {

                            string m_ColumnName = dt.Rows[i]["PField"].ToString();

                            string strQuery = "";

 

                            if (dt.Rows[i]["Name"] != DBNull.Value && dt.Rows[i]["Department"] != DBNull.Value)

                            {

                                if (!string.IsNullOrEmpty(m_Criteria))

                                {

                                    strQuery = "Select * from [Data] where Name = @Name and Department = @Department and " + m_Criteria;

                                }

                                else

                                {

                                    strQuery = "Select * from [Data] where Name = @Name and Department = @Department ";

                                }

                            }

                            if (dt.Rows[i]["Name"] == DBNull.Value && dt.Rows[i]["Department"] != DBNull.Value)

                            {

                                if (!string.IsNullOrEmpty(m_Criteria))

                                {

                                    strQuery = "Select * from [Data] where Department = @Department and " + m_Criteria;

                                }

                                else

                                {

                                    strQuery = "Select * from [Data] where Department = @Department";

                                }

                            }

 

                            using (SqlCommand sqlCommand0 = new SqlCommand(strQuery, con))

                            {

                                if (dt.Rows[i]["Name"] != DBNull.Value && dt.Rows[i]["Department"] != DBNull.Value)

                                {

                                    sqlCommand0.Parameters.AddWithValue("@Name", dt.Rows[i]["Name"].ToString());

                                }

                                sqlCommand0.CommandType = CommandType.Text;

                                sqlCommand0.Parameters.AddWithValue("@Department", dt.Rows[i]["Department"].ToString());

                                

                                SqlDataAdapter ad0 = new SqlDataAdapter(sqlCommand0);

                                DataTable dt0 = new DataTable();

                                ad0.Fill(dt0);

                                if (dt0.Rows.Count > 0)

                                {

                                    try

                                    {

                                        for (int i0 = 0; i0 < dt0.Rows.Count; i0++)

                                        {

                                            m_DocNo = dt0.Rows[i0]["Document No"].ToString();

                                            m_ItemNo = dt0.Rows[i0]["Item No"].ToString();

                                            Amount = 0;

                                            Amount0 = 0;

 

                                                int m_Discount = Convert.ToInt32(dt0.Rows[i0]["Discount %"].ToString());

                                               Amount = m_discount /10;

                                            }

 

                                            SqlCommand cmdUpdate = new SqlCommand("Sp_BFbProcess", con);

                                            cmdUpdate.CommandType = CommandType.StoredProcedure;

                                            cmdUpdate.Parameters.AddWithValue("@Action", "U");

                                            cmdUpdate.Parameters.Add("@DocNo", SqlDbType.NVarChar).Value = m_DocNo;

                                            cmdUpdate.Parameters.Add("@ItemNo", SqlDbType.NVarChar).Value = m_ItemNo;

                                            cmdUpdate.Parameters.Add("@Amount", SqlDbType.Decimal).Value = Amount;

                                            cmdUpdate.ExecuteNonQuery();

                                        }

                                    }

                                    catch (Exception ex)

                                    {

                                        ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + CommonFunction.ErrorMessage + "');", true);

                                    }

                                }

                            }

                        }

                    }

                }

                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + CommonFunction.DataProcessedSuccessfully + "');", true);

            }

            catch (Exception ex)

            {

                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + CommonFunction.ErrorMessage + "');", true);

            }

        }

Thanks

Answer 1

User283571144 posted

Hi JagjitSingh,

Accoeding to your description and codes, I couldn't directly find the rason why the update rollback.

Could you please post your sql datatable makeup and the "Sp_BFbProcess" StoredProcedure?

If you could post more details information, it will be more easily for us to reproduce the issue and find the solution.

Best Regards,

Brando

Answer 2

User-1499457942 posted

Hi

  Here is the problem . Problem in this code when Value is 0. Why it is RollingBack

Convert.ToDecimal(dt.Rows[i]["FixedPer"].ToString())

m_Amount = (Convert.ToDecimal(dt.Rows[i]["FixedPer"].ToString()) * Convert.ToDecimal(dt0.Rows[i0][m_ColumnName].ToString())) / 100;

when i give m_Amount = 100; it is working . Even if i write only below line then also it rollback

m_Amount = (Convert.ToDecimal(dt.Rows[i]["FixedPer"].ToString()));

I have written below code but it also does not show any error message

try
m_ColumnName = dt.Rows[i]["Percentage_Field"].ToString();
                                                {
                                                    m_BfbAmount = (Convert.ToDecimal(dt.Rows[i]["FixedPer"].ToString()) * Convert.ToDecimal(dt0.Rows[i0][m_ColumnName].ToString())) / (100);
                                                }
                                                catch (Exception ex)
                                                {
                                                    ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + ex.Message + "');", true);
                                                }


CREATE TABLE [dbo].[Test](
	[No] [int] IDENTITY(1,1) NOT NULL,
	[Category] [int] NOT NULL,
	[Name] [nvarchar](35) NOT NULL,
	[Department] [nvarchar](5) NOT NULL,
	[Amount] [decimal](18, 2) NULL,
	[FixedPer] [decimal](18, 2) NULL,
	[Location] [nvarchar](250) NULL
) ON [PRIMARY]

Thanks

Answer 3

User-1499457942 posted

Resolved. Logic Issue