System.IndexOutOfRangeException: Cannot find table 0.5

本文关键字:table find Cannot IndexOutOfRangeException System | 更新日期: 2023-09-27 18:10:06

我正在asmx中制作一个webmethod,其中我从不同的表中获取数据…并填充数据集中的行。但我怎么能填补我的数据集,因为我从不同的表获得数据??请帮助我:

[WebMethod]

 public DataSet getblancesheet(string baranchcode, string fromdate, string todate)
 {
    // List<datalist4> data = new List<datalist4>();
     DataTable dt1 = new DataTable();
     DataTable dt2 = new DataTable();
     DataTable dt3 = new DataTable();
     DataTable dt4 = new DataTable();
     DataTable finaldata = new DataTable();
     Webservice.databaseops dbo = new Webservice.databaseops();
      DataSet ds = new DataSet("Ledger");
     SqlDataAdapter adp = new SqlDataAdapter();
      //adp.Fill(ds);
     bool sheet1, sheet2, sheet3;
     sheet1 = sheet2 = sheet3 = true;
     decimal sheet1bal, sheet2bal, sheet3bal;
     sheet1bal = sheet2bal = sheet3bal = 0;
     dt1 = dbo.getdata("SELECT Bcode,Bdesc FROM Gl_bSheet1 where Compcode='" + baranchcode + "'");
     if (dt1.Rows.Count > 0)
     {
         foreach (DataRow dr1 in dt1.Rows)
         {
             // sheet1
             dt2 = dbo.getdata("SELECT Bncode,Bndesc FROM Gl_bSheet2 where Compcode='" + baranchcode + "' and Bcode='" + dr1[0] + "'");
             if (dt2.Rows.Count > 0)
             {
                 foreach (DataRow dr2 in dt2.Rows)
                 {
                     //sheet2
                     dt3 = dbo.getdata("SELECT Bnicode,Bnidesc FROM Gl_bSheet3 where Compcode='" + baranchcode + "' and Bcode='" + dr1[0] + "' and Bncode='" + dr2[0] + "'");
                     if (dt3.Rows.Count > 0)
                     {
                         foreach (DataRow dr3 in dt3.Rows)
                         {
                             //sheet3
                             dt4 = dbo.getdata("SELECT gpls.Accountno,gd.Acct_Desc FROM Gl_bSheetdetail gpls,GL_Detail gd where gpls.Compcode='" + baranchcode + "' and gpls.Bcode='" + dr1[0] + "' and gpls.Bncode='" + dr2[0] + "' and gpls.Bnicode='" + dr3[0] + "' and gpls.Accountno=gd.AccountNo");
                             if (dt4.Rows.Count > 0)
                             {
                                 foreach (DataRow dr4 in dt4.Rows)
                                 {
                                     //detialsheet
                                     finaldata = dbo.getdata("SELECT  SUM(Dr_Amount)-SUM(Cr_Amount) from gl_transaction where Accountno='" + dr4[0] + "' and Value_Date between '" + fromdate + "' and '" + todate + "'");
                                     if (finaldata.Rows[0].ItemArray[0].ToString().Length > 0)
                                     {
                                         if (sheet1)
                                         {
                                             foreach (DataRow dr in ds.Tables[0].Rows)
                                             {
                                                 DataRow dd = ds.Tables[0].NewRow();
                                                 dd[0] = dr1[1].ToString();
                                                 dd[1] = "sheet1";
                                                 dd[2] = "";
                                                 dd[3] = "";
                                                 ds.Tables[0].Rows.Add(dd);
                                                 sheet1 = false;

                                                 // data.Add(new datalist4(dr1[1].ToString(), "sheet1", "", ""));
                                                 // sheet1 = false;
                                             }
                                         }
                                         if (sheet2)
                                         {
                                             foreach (DataRow dr in ds.Tables[0].Rows)
                                             {
                                                 DataRow dd = ds.Tables[0].NewRow();
                                                 dd[0] = dr2[1].ToString();
                                                 dd[1] = "sheet2";
                                                 dd[2] = "";
                                                 dd[3] = "";
                                                 ds.Tables[0].Rows.Add(dd);
                                                 sheet2 = false;
                                                 //data.Add(new datalist4(dr2[1].ToString(), "sheet2", "", ""));
                                                 //sheet2 = false;
                                             }
                                         }
                                         if (sheet3)
                                         {
                                             foreach (DataRow dr in ds.Tables[0].Rows)
                                             {
                                                 DataRow dd = ds.Tables[0].NewRow();
                                                 dd[0] = dr3[1].ToString();
                                                 dd[1] = "sheet3";
                                                 dd[2] = "";
                                                 dd[3] = "";
                                                 ds.Tables[0].Rows.Add(dd);
                                                 //data.Add(new datalist4(dr3[1].ToString(), "sheet3", "", ""));
                                                 //sheet3 = false;
                                                 sheet3 = false;
                                             }
                                         }
                                         foreach (DataRow dr in ds.Tables[0].Rows)
                                         {
                                             DataRow dd = ds.Tables[0].NewRow();
                                             dd[0] = "-";
                                             dd[1] = dr4[0].ToString().Trim();
                                             dd[2] = dr4[1].ToString().Trim();
                                             dd[3] = dbo.valueparser(decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString()), false);
                                             ds.Tables[0].Rows.Add(dd);


                                         }
                                         //data.Add(new datalist4("-", dr4[0].ToString().Trim(), dr4[1].ToString().Trim(), dbo.valueparser(decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString()), false)));
                                         sheet1bal = sheet1bal + decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString());
                                         sheet2bal = sheet2bal + decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString());
                                         sheet3bal = sheet2bal + decimal.Parse(finaldata.Rows[0].ItemArray[0].ToString());
                                     }
                                 }
                             }
                             if (!sheet3)
                             {
                                 foreach (DataRow dr in ds.Tables[0].Rows)
                                 {
                                     DataRow dd = ds.Tables[0].NewRow();
                                     dd[0] = "sheet3end";
                                     dd[1] = "SubTotal";
                                     dd[2] = dr3[1].ToString();
                                     dd[3] = dbo.valueparser(sheet3bal, false);
                                     ds.Tables[0].Rows.Add(dd);
                                     //data.Add(new datalist4("sheet3end", "Subtotal:", dr3[1].ToString(), dbo.valueparser(sheet3bal, false)));
                                     sheet3 = true;
                                     sheet3bal = 0;
                                 }
                             }
                         }
                     }
                     if (!sheet2)
                     {
                         foreach (DataRow dr in ds.Tables[0].Rows)
                         {
                             DataRow dd = ds.Tables[0].NewRow();
                             dd[0] = "sheet2end";
                             dd[1] = "SubTotal:";
                             dd[2] = dr2[1].ToString();
                             dd[3] = dbo.valueparser(sheet2bal, false);
                             ds.Tables[0].Rows.Add(dd);


                             // data.Add(new datalist4("sheet2end", "Subtotal:", dr2[1].ToString(), dbo.valueparser(sheet2bal, false)));
                             sheet2 = true;
                             sheet2bal = 0;
                         }
                     }
                 }
             }
             if (!sheet1)
                 foreach (DataRow dr in ds.Tables[0].Rows)
                 {
                     DataRow dd = ds.Tables[0].NewRow();
                     dd[0] = "sheet1end";
                     dd[1] = "SubTotal:";
                     dd[2] = dr1[1].ToString();
                     dd[3] = dbo.valueparser(sheet2bal, false);
                     ds.Tables[0].Rows.Add(dd);
                     //data.Add(new datalist4("sheet1end", "Subtotal:", dr1[1].ToString(), dbo.valueparser(sheet1bal, false)));
                     sheet1 = true;
                     sheet1bal = 0;
                 }
         }
     }

     return ds;
 }
//do

System.IndexOutOfRangeException: Cannot find table 0.5

我对你的代码做了一些重构,我想我已经为你找到了一个合理的解决方案。尝试这样做来获取数据:

var data =
    from DataRow dr1 in dbo.getdata("SELECT Bcode,Bdesc FROM Gl_bSheet1 where Compcode='" + baranchcode + "'").Rows
    let Bcode = (string)dr1[0]
    select new
    {
        Bcode,
        Bdesc = (string)dr1[1],
        sheet2s =
            from DataRow dr2 in dbo.getdata("SELECT Bncode,Bndesc FROM Gl_bSheet2 where Compcode='" + baranchcode + "' and Bcode='" + Bcode + "'").Rows
            let Bncode = (string)dr2[0]
            select new
            {
                Bncode,
                Bndesc = (string)dr2[1],
                sheet3s =
                    from DataRow dr3 in dbo.getdata("SELECT Bnicode,Bnidesc FROM Gl_bSheet3 where Compcode='" + baranchcode + "' and Bcode='" + Bcode + "' and Bncode='" + Bncode + "'").Rows
                    let Bnicode = (string)dr3[0]
                    select new
                    {
                        Bnicode,
                        Bnidesc = (string)dr3[1],
                        dataSheets =
                            from DataRow dr4 in dbo.getdata("SELECT gpls.Accountno,gd.Acct_Desc FROM Gl_bSheetdetail gpls,GL_Detail gd where gpls.Compcode='" + baranchcode + "' and gpls.Bcode='" + Bcode + "' and gpls.Bncode='" + Bncode + "' and gpls.Bnicode='" + Bnicode + "' and gpls.Accountno=gd.AccountNo").Rows
                            let Accountno = (string)dr4[0]
                            let finaldata = (string)(dbo.getdata("SELECT  SUM(Dr_Amount)-SUM(Cr_Amount) from gl_transaction where Accountno='" + Accountno + "' and Value_Date between '" + fromdate + "' and '" + todate + "'").Rows[0].ItemArray[0])
                            where !String.IsNullOrEmpty(finaldata)
                            select new
                            {
                                Accountno,
                                Acct_Desc = (string)dr4[1],
                                value = decimal.Parse(finaldata)
                            }
                    }
            }
    };

然后用以下代码生成行:

foreach (var s1 in data)
{
    var dd = ds.Tables[0].NewRow();
    dd[0] = s1.Bdesc;
    dd[1] = "sheet1";
    dd[2] = "";
    dd[3] = "";
    ds.Tables[0].Rows.Add(dd);
    foreach (var s2 in s1.sheet2s)
    {
        //Add Sheet2 Opening Row Here
        foreach (var s3 in s2.sheet3s)
        {
            //Add Sheet3 Opening Row Here
            //Add Data Row Here
            var s3balance = s3.dataSheets
                .Select(z => z.value)
                .Sum();
            //Add Sheet3 Closing Row Here
        }
        var s2balance = s2.sheet3s
            .SelectMany(y => y.dataSheets)
            .Select(z => z.value)
            .Sum();
        //Add Sheet2 Closing Row Here
    }
    var s1balance = s1.sheet2s
        .SelectMany(x => x.sheet3s)
        .SelectMany(y => y.dataSheets)
        .Select(z => z.value)
        .Sum();
    //Add Sheet1 Closing Row Here
}

让查询远离写数据的代码通常是个好主意。