Question 344826 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – I want to exit both foreach loop and while loop when opdnew<0

protected void btnOPDDetect_Click(object sender, EventArgs e)
{
    SqlConnection con1 = GetConnection();
    SqlConnection con = GetConnection();
    DataTable dt = new DataTable();

    double opdnew;
    string batchNo = lblIdforReport.Text;

    SqlCommand cmd = new SqlCommand("Select ExpenseSheetNo,FurtherDetail,FurtherDetailBy,Id,VoucherStatus,Convert(nvarchar(50),Date,106) As Date,ProcessType,ProgramDepartment,CompanyPerson,PreparedBy,RecommendedStatus,Convert(nvarchar(50),PreparedDate,106) As PreparedDate,SubmittedBy,Convert(nvarchar(50),SubmittedDate,106) As SubmittedDate,RecommendedBy,Convert(nvarchar(50),RecommendedDate,106) As RecommendedDate,FurtherDetailBy,Supervisior,TotalAmount From tb_ManageExpenseSheetOne where IsCheckedAdvance='true' and FinanceOfficer='" + User.Identity.Name + "' and SubmittedStatus='Submitted' and   VoucherStatus='Voucher Created' and RecommendedStatus='Recommended' and  BatchNo='" + Convert.ToInt64(batchNo) + "'  order by ExpenseSheetNo", con);

    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(dt);

    foreach (DataRow row in dt.Rows)
    {
        string PreparedBy = row["PreparedBy"].ToString();
        string TotalAmount = row["TotalAmount"].ToString();
        string constr = ConfigurationManager.ConnectionStrings["HhrdApprovalSystem"].ConnectionString;

        string selectSql = "SELECT * from tb_Person where Name='" + PreparedBy + "'";

        SqlCommand cmdcheck = new SqlCommand(selectSql, con1);

        using (SqlCommand cmd1 = new SqlCommand())
        {
            try
            {
                con1.Open();

                using (SqlDataReader reader = cmdcheck.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        TextBox13.Text = (reader["RemainingOPD"].ToString());
                        TextBox14.Text = TotalAmount.ToString();

                         opdnew = ((Convert.ToDouble(TextBox13.Text)) - (Convert.ToDouble(TextBox14.Text)));

                         if (opdnew < 0)
                         {
                             lblWarning.Text = "'" + PreparedBy + "' OPD is not enough";
                             break;
                         }
                     }
                }
            }
            finally
            {
                con1.Close();
            }

            cmd1.CommandText = "Update tb_Person Set remainingOPD= '" + ((Convert.ToDouble(TextBox13.Text)) - (Convert.ToDouble(TextBox14.Text))) + "'WHERE Name ='" + PreparedBy.ToString() + "'";
            cmd1.Connection = con;

            con.Open();
            cmd1.ExecuteScalar();
            con.Close();
        }
    }

    btnArchive.Visible = true;
}

I have RemainingOPD column in tb_Person and totalAmount column in tb_ManageExpenseSheetOne. I have to subtract totalAmount from RemainingOPD and update tb_Person‘s remainopd column.

For example,

tb_ManageExpenseSheetOne

Name totalAmount
A 4000
B 5000
C 0

The foreach loop gets PreparedBy and totalAmount of all persons from tb_ManageExpenseSheetOne.

Then while loop to get RemainingOPD by subtracting totalAmount and also check if after subtracting NewOPD is less than 0.

If less than 0, then I want to show a message without updating any record in tb_Person.
Otherwise, I want to update tb_Person against all record from tb_ManageexpensesheetOne.

Tags:

2

Answers


  1. 0

    You can move the whole foreach loop into a separate method and use the return to exit all the nested loops. For example:

        //new method
    private void CheckOPD()
    {
        foreach (DataRow row in dt.Rows)
        {
            ... your loop code

            while(reader.Read())
            {
                ...code
                if (opdnew < 0)
                {
                    return; // here you use a return
                }
            }
        }
    }

    And call this method inside the btnOPDDetect_Click.

    Login or Signup to reply.
  2. 0

    You have to modify your code to achieve desired result:

    protected void btnOPDDetect_Click(object sender, EventArgs e)
    {
    bool status = false;
    SqlConnection con1 = GetConnection();
    SqlConnection con = GetConnection();
    DataTable dt = new DataTable();

    double opdnew;
    string batchNo = lblIdforReport.Text;

    SqlCommand cmd = new SqlCommand("Select ExpenseSheetNo,FurtherDetail,FurtherDetailBy,Id,VoucherStatus,Convert(nvarchar(50),Date,106) As Date,ProcessType,ProgramDepartment,CompanyPerson,PreparedBy,RecommendedStatus,Convert(nvarchar(50),PreparedDate,106) As PreparedDate,SubmittedBy,Convert(nvarchar(50),SubmittedDate,106) As SubmittedDate,RecommendedBy,Convert(nvarchar(50),RecommendedDate,106) As RecommendedDate,FurtherDetailBy,Supervisior,TotalAmount From tb_ManageExpenseSheetOne where IsCheckedAdvance='true' and FinanceOfficer='" + User.Identity.Name + "' and SubmittedStatus='Submitted' and   VoucherStatus='Voucher Created' and RecommendedStatus='Recommended' and  BatchNo='" + Convert.ToInt64(batchNo) + "'  order by ExpenseSheetNo", con);

    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(dt);

    foreach (DataRow row in dt.Rows)
    {
        string PreparedBy = row["PreparedBy"].ToString();
        string TotalAmount = row["TotalAmount"].ToString();
        string constr = ConfigurationManager.ConnectionStrings["HhrdApprovalSystem"].ConnectionString;

        string selectSql = "SELECT * from tb_Person where Name='" + PreparedBy + "'";

        SqlCommand cmdcheck = new SqlCommand(selectSql, con1);

        using (SqlCommand cmd1 = new SqlCommand())
        {
            try
            {
                con1.Open();

                using (SqlDataReader reader = cmdcheck.ExecuteReader())
                {
                    while(reader.Read())
                    {
                        TextBox13.Text = (reader["RemainingOPD"].ToString());
                        TextBox14.Text = TotalAmount.ToString();

                         opdnew = ((Convert.ToDouble(TextBox13.Text)) - (Convert.ToDouble(TextBox14.Text)));

                         if (opdnew < 0)
                         {
                             lblWarning.Text = "'" + PreparedBy + "' OPD is not enough";
                             status = true;
                             break;
                         }
                     }
                     
                     if(status)
                     {
                         break;
                     }
                     
                }
            }
            finally
            {
                con1.Close();
            }

            cmd1.CommandText = "Update tb_Person Set remainingOPD= '" + ((Convert.ToDouble(TextBox13.Text)) - (Convert.ToDouble(TextBox14.Text))) + "'WHERE Name ='" + PreparedBy.ToString() + "'";
            cmd1.Connection = con;

            con.Open();
            cmd1.ExecuteScalar();
            con.Close();
        }
    }

    btnArchive.Visible = true;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search