imtiaz imtiaz - 5 months ago 15
Javascript Question

List objects adding same data multiple times that were added before

UPDATE:

I'm trying to fetch lists for UnionCouncil and BEMISCode(Schoolid) from EMIS_DB(DataBase1) and PBHId(BudgetId) list from DSS_DB(DataBase2) and show sum of AlocatedBudget for each UnionCouncil if it has any.

So far i am getting the values of listobjects.counts() but the sum of AlocatedBudget is showing correctly only for first UnionCouncil and then it adds the sum of AlocatedBudget into others.

for example: AllocatedBudget for UnionCuncil abc is 208000 AllocatedBudget for UnionCouncil xyz will be double of previous AllocatedBudget like 416000.

I have tried almost everything but couldn't find what is missing, any kind of help is much much appreciated.

Client side

<div class="panel-body">

<div id="tblschoolinfo" style="background-color: #F8F8F8; color: rgb(1, 51, 1);">


Function

function LoadBudgetSumCouncilvise()
{
var table = $("#tblschoolinfo");
$.ajax({
type: "POST",
url: "wbservices/GetBudgetEstimatedSumByUnionCouncil.asmx/getunioncouncil",
//data: jsnDta,
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (r) {

var rtnData = r.d; //all returned data...
var respDta = [];
$.map(rtnData, function (item, index) {
var j = [
item.status,
item.msg,
];
respDta.push(j);

});
$.each(respDta, function (key, value) {
var status = value[0];
var msg = value[1];

alert(msg);
if (status == true) {

table.html(msg);

} else {

var eMsg = '<div style="color:white;background:#FF4040" ><i style="margin-left:5px" class="fa fa-exclamation-triangle"></i>"' + msg + '"</div>';
table.html(eMsg);

}

}); //1st out loop ends here...


},
error: function (jqXHR, textStatus, errorThrown) {
// $("#responseMovDetails").html(jqXHR + textStatus + errorThrown);
alert("error while loading Records of Requests" + jqXHR + textStatus + errorThrown);

});
}


ASMX

public List<RequestResponse> getunioncouncil()
{


List<RequestResponse> list = new List<RequestResponse>();
RequestResponse r = new RequestResponse();

string District = Session["districtName"].ToString();

Pro_DbCon obj2 = new Pro_DbCon();

//EMIS conncection
SqlConnection conemis = obj2.dbconnectionEMIS();
string rows = "";


List<string> uc = new List<string>();
List<string> bemiscode = new List<string>();
List<string>pbhid=new List<string>();
List<string> pbdid = new List<string>();
try
{
conemis.Open();
//get unoincouncil
string getuc = "select distinct(UnionCouncil) from Locations where District = @dis";
SqlCommand cmduc = new SqlCommand(getuc,conemis);
cmduc.Parameters.AddWithValue("@dis",District);
SqlDataReader ucdr = cmduc.ExecuteReader();

while(ucdr.Read())
{
uc.Add(ucdr[0].ToString());
}

ucdr.Close();

for (int i = 0; i < uc.Count();i++ )
{
//get bemiscode by unioncouncil
string getbemiscode = "select VchBEMISCode from SchoolsParameter where VchUC = @uc";
SqlCommand getcode = new SqlCommand(getbemiscode,conemis);
getcode.Parameters.AddWithValue("@uc",uc[i]);
SqlDataReader bemiscodedr = getcode.ExecuteReader();

while(bemiscodedr.Read())
{
bemiscode.Add(bemiscodedr[0].ToString());
}

bemiscodedr.Close();

}

}
catch(Exception ex)
{
r.status = false;
r.msg = "Could not get data from EmisDb!" + ex.ToString();
list.Add(r);
}
finally
{
conemis.Close();
}


//Dss connection
string constr = obj2.dbconnection();
SqlConnection con = new SqlConnection(constr);
try
{
con.Open();
for (int a = 0; a < uc.Count(); a++)
{

//----------------------------------
Decimal EstimatedAmountTaat = 0;
Decimal EstimatedAmountScience = 0;
Decimal EstimatedAmountReadWriting = 0;
Decimal EstimatedAmountFurniture = 0;

Decimal UtilizedAmountTaat = 0;
Decimal UtilizedAmountScience = 0;
Decimal UtilizedAmountReadingWriting = 0;
Decimal UtilizedAmountFurniture = 0;
//----------------------------------

for (int i = 0; i < bemiscode.Count(); i++)
{
//check bemiscode exists in budgetview

//get budgethead by bemiscode
string getbudgethead = "select * from Pro_BudgetView where BEMSCode = @scode";
SqlCommand bheadcmd = new SqlCommand(getbudgethead, con);
bheadcmd.Parameters.AddWithValue("@scode", bemiscode[i]);
SqlDataReader dr = bheadcmd.ExecuteReader();
while (dr.Read())
{
if (dr[1].ToString() == bemiscode[i].ToString())
{
pbhid.Add(dr[0].ToString());
}

}
dr.Close();

}
//-------------------------------------------
for (int j = 0; j < pbhid.Count(); j++)
{

//get budgetdetail by budgethead
string getbudgetdetail = "select * from Pro_BudgetView where PBHId = @pbhid";
SqlCommand getbudgetdetailcmd = new SqlCommand(getbudgetdetail, con);
getbudgetdetailcmd.Parameters.AddWithValue("@pbhid", pbhid[j]);
SqlDataReader bdreader = getbudgetdetailcmd.ExecuteReader();

while (bdreader.Read())
{
pbdid.Add(bdreader[5].ToString());

if(bdreader[6].ToString()=="1")
{
EstimatedAmountTaat = EstimatedAmountTaat + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountTaat = UtilizedAmountTaat + Convert.ToDecimal(bdreader[8].ToString());
}

if (bdreader[6].ToString() == "2")
{
EstimatedAmountScience = EstimatedAmountScience + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountScience = UtilizedAmountScience + Convert.ToDecimal(bdreader[8].ToString());
}

if (bdreader[6].ToString() == "3")
{
EstimatedAmountReadWriting = EstimatedAmountReadWriting + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountReadingWriting = UtilizedAmountReadingWriting + Convert.ToDecimal(bdreader[8].ToString());
}

if (bdreader[6].ToString() == "4")
{
EstimatedAmountFurniture = EstimatedAmountFurniture + Convert.ToDecimal(bdreader[7].ToString());
UtilizedAmountFurniture = UtilizedAmountFurniture + Convert.ToDecimal(bdreader[8].ToString());
}

}

bdreader.Close();

}

string row = "<tr><td colspan='4' style='text-align:center;'>" + uc[a].ToString() + "</td></tr> <tr><td><b>AlocatedBudget(sum)</b></td><td>" + EstimatedAmountTaat + "</td><td>" + EstimatedAmountScience + "</td><td>" + EstimatedAmountReadWriting + "</td><td>" + EstimatedAmountFurniture + "</td></tr> <tr><td><b>UtiliazedAmount(sum)</b></td><td>" + UtilizedAmountTaat + "</td><td>" + UtilizedAmountScience + "</td><td>" + UtilizedAmountReadingWriting + "</td><td>" + UtilizedAmountFurniture + "</td></tr>";
rows = rows + row;

}
con.Close();
if(rows!="")
{
r.status = true;
r.msg = rows + "";
list.Add(r);
}

}
catch (Exception ex)
{
r.status = false;
r.msg = "Error" + ex.ToString();
list.Add(r);
}
finally
{
con.Close();
}

return list;
}

Answer

Although its logical problem but this kind of mistake we often make and it can save much much time for others.

Changes

  1. Declare List Where its needed (Scope).
  2. looping through Listobjects and only nest where query is dependent on 1 to many.

Here is what I did.

    public class GetBudgetEstimatedSumByUnionCouncil : System.Web.Services.WebService
{

    [WebMethod (EnableSession=true)]
    public List<RequestResponse> getunioncouncil()
    {


        List<RequestResponse> list = new List<RequestResponse>();
        RequestResponse r = new RequestResponse();

        string District = Session["districtName"].ToString();

        Pro_DbCon obj2 = new Pro_DbCon();

        //EMIS conncection
        SqlConnection conemis = obj2.dbconnectionEMIS();
        string rows = "";

        //Dss connection
        string constr = obj2.dbconnection();
        SqlConnection con = new SqlConnection(constr);

        List<string> uc = new List<string>();

        //List<string>pbhid=new List<string>();

        try
        {
            conemis.Open();
            //get unoincouncil
            string getuc = "select distinct(UnionCouncil) from Locations where District = @dis";
            SqlCommand cmduc = new SqlCommand(getuc,conemis);
            cmduc.Parameters.AddWithValue("@dis",District);
            SqlDataReader ucdr = cmduc.ExecuteReader();

            while(ucdr.Read())
            {
                uc.Add(ucdr[0].ToString());
            }

            ucdr.Close();
            conemis.Close();
            for (int i = 0; i < uc.Count();i++ )
            {
                //----------------------------------
                Decimal EstimatedAmountTaat = 0;
                Decimal EstimatedAmountScience = 0;
                Decimal EstimatedAmountReadWriting = 0;
                Decimal EstimatedAmountFurniture = 0;

                Decimal UtilizedAmountTaat = 0;
                Decimal UtilizedAmountScience = 0;
                Decimal UtilizedAmountReadingWriting = 0;
                Decimal UtilizedAmountFurniture = 0;
                //----------------------------------

                List<string> bemiscode = new List<string>();
                List<string> pbdid = new List<string>();
                //get bemiscode by unioncouncil
                conemis.Open();
            string getbemiscode = "select VchBEMISCode from SchoolsParameter where VchUC = @uc";
            SqlCommand getcode = new SqlCommand(getbemiscode,conemis);
            getcode.Parameters.AddWithValue("@uc",uc[i]);
            SqlDataReader bemiscodedr = getcode.ExecuteReader();

                while(bemiscodedr.Read())
                {
                    bemiscode.Add(bemiscodedr[0].ToString());
                }

                bemiscodedr.Close();
                conemis.Close();
                //----------------------------------
                con.Open();
                for (int b = 0; b < bemiscode.Count(); b++)
                {

                    //get budgethead by bemiscode
                    string getbudgethead = "select * from Pro_BudgetView where BEMSCode = @scode";
                    SqlCommand bheadcmd = new SqlCommand(getbudgethead, con);
                    bheadcmd.Parameters.AddWithValue("@scode", bemiscode[b]);
                    SqlDataReader dr = bheadcmd.ExecuteReader();
                    while (dr.Read())
                    {
                            pbdid.Add(dr[5].ToString());                           

                    }

                    dr.Close();

                }

                //----------------------------
                for (int j = 0; j < pbdid.Count(); j++)
                {                        
                    //get budgetdetail by budgethead
                    string getbudgetdetail = "select PBHId,EstimatedAmount,UtilizedAmount,CId,PBDId from Pro_BudgetView where PBDId = @pbdid and BudgetStatus = @bs";
                    SqlCommand getbudgetdetailcmd = new SqlCommand(getbudgetdetail, con);
                    getbudgetdetailcmd.Parameters.AddWithValue("@pbdid", pbdid[j]);
                    getbudgetdetailcmd.Parameters.AddWithValue("@bs", "2");
                    SqlDataReader bdr = getbudgetdetailcmd.ExecuteReader();

                    while (bdr.Read())
                    {
                        //pbdid.Add(bdr[4].ToString());
                        if (bdr[3].ToString() == "1")
                        {
                            EstimatedAmountTaat = EstimatedAmountTaat + Convert.ToDecimal(bdr[1].ToString());
                            UtilizedAmountTaat = UtilizedAmountTaat + Convert.ToDecimal(bdr[2].ToString());
                        }
                        if (bdr[3].ToString() == "2")
                        {
                            EstimatedAmountScience = EstimatedAmountScience + Convert.ToDecimal(bdr[1].ToString());
                            UtilizedAmountScience = UtilizedAmountScience + Convert.ToDecimal(bdr[2].ToString());
                        }
                        if (bdr[3].ToString() == "3")
                        {
                            EstimatedAmountReadWriting = EstimatedAmountReadWriting + Convert.ToDecimal(bdr[1].ToString());
                            UtilizedAmountReadingWriting = UtilizedAmountReadingWriting + Convert.ToDecimal(bdr[2].ToString());
                        }
                        if (bdr[3].ToString() == "4")
                        {
                            EstimatedAmountFurniture = EstimatedAmountFurniture + Convert.ToDecimal(bdr[1].ToString());
                            UtilizedAmountFurniture = UtilizedAmountFurniture + Convert.ToDecimal(bdr[2].ToString());
                        }

                    }

                    bdr.Close();
                }
                string row = "<tr><td style='text-align:center;font-size: large;'><b>UnionCouncil</b></td><td colspan='5' style='text-align:center;font-size: x-large;'>" + uc[i].ToString() + "</td></tr> <tr><td style='text-align:center;font-size: large;'><b>AlocatedBudget(sum)</b></td><td style='text-align:center;'>" + EstimatedAmountTaat + "</td><td style='text-align:center;'>" + EstimatedAmountScience + "</td><td style='text-align:center;'>" + EstimatedAmountReadWriting + "</td><td style='text-align:center;'>" + EstimatedAmountFurniture + "</td></tr>  <tr><td style='text-align:center;font-size: large;'><b>UtiliazedAmount(sum)</b></td><td style='text-align:center;'>" + UtilizedAmountTaat + "</td><td style='text-align:center;'>" + UtilizedAmountScience + "</td><td style='text-align:center;'>" + UtilizedAmountReadingWriting + "</td><td style='text-align:center;'>" + UtilizedAmountFurniture + "</td></tr>";
                    rows = rows + row;
                con.Close();
                if(rows!="")
                {
                    r.status = true;
                    r.msg = rows + "";
                    list.Add(r);
                }
            }              
        }
        catch(Exception ex)
        {
            r.status = false;
            r.msg = "Could not get data from EmisDb!" + ex.ToString();
            list.Add(r);
        }
        finally
        {
            conemis.Close();
            con.Close();
        }

        return list;
    }