Fetching data problem from multiple tables in Crystal Report

Last Reply 2 days ago By dharmendr

Posted 12 days ago

I have a following table in the database

tblFeesGenerate

GFID Year Month Class Section AdmissionNo Fees HeadName Price Sname
1 2019 10-Oct One A R-01 1300 Sports Fee 500 A
2 2019 10-Oct One A R-01 1300 Library Fee 600 A
3 2019 10-Oct One A R-01 1300 Exam Fee 700 A
4 2019 10-Oct One A R-02 1300 Sports Fee 500 B
5 2019 10-Oct One A R-02 1300 Library Fee 600 B
6 2019 10-Oct One A R-02 1300 Exam Fee 700 B
7 2019 10-Oct One A R-03 1300 Sports Fee 500 C
8 2019 10-Oct One A R-03 1300 Library Fee 600 C
9 2019 10-Oct One A R-03 1300 Exam Fee 700 C

 This fees is generated first time so there is no record in the tblFeesCollection and table is like this.

tblFeesCollection

FID AdmissionNo FeesMonth Recievable CDiscount Arrears NetBal Paid RemBal
                 

Now I want to generate fees voucher in crystal report like this

AdmissionNo: R-01     Sname: A   Month: 10-Oct

Sr         HeadName   Price

1          Fees             1300

2          Sports Fees    500

3          Library Fees    600

4          Exam Fees      700

Cdiscount: 0

Arrears: 0

Recievable: 3100

Net Balance: 3100

I have created the following dataset

https://i.imgur.com/8iViyWW

and designed the following report like this

https://i.imgur.com/SwM4qUM

and my code to fetch data from multiple tables is 

private void btnVoucher_Click(object sender, EventArgs e)
{
    DisplayReports(); //https://www.aspforums.net/Threads/334444/Populate-Crystal-Report-using-dataset-with-multiple-tables-using-C-in-ASPNet/
}
protected void DisplayReports()
{
    rptVouch crystalReport = new rptVouch();
    DSVoucher dsCustomers = GetData();
    crystalReport.SetDataSource(dsCustomers);

    this.crystalReportViewer1.ReportSource = crystalReport;
    this.crystalReportViewer1.RefreshReport();

}
private DSVoucher GetData()
{
    string constr = @"Data Source=.;Initial Catalog=Name;uid=sa;pwd=2005;";
    using (SqlConnection con = new SqlConnection(constr))
    {
        // where sm.AdmissionNo=@AdmissionNo OR @AdmissionNo IS NULL
        using (SqlCommand cmd = new SqlCommand(@"select Year,Month,IssueDate,DueDate,AcademicName,ClassName,SectionName,fg.AdmissionNo,SName,FName,RollNo,FPhone,Fees,HeadName,Price from tblFeesGenerate as fg
            inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo
            inner join tblAcademicYear as ay on ay.YearID=fg.YearID
            inner join tblDefClass as dc on fg.ClassID=dc.ClassID
            inner join tblDefSection as ds on fg.SectionID=ds.SectionID
            inner join tblFeesHead as fh on fg.HeadID=fh.HeadID order by AdmissionNo asc
            select fg.AdmissionNo,fg.Year,Fees,sum(Price) 'Others',ISNULL(Fees,0) + ISNULL(sum(Price),0) 'Recievable',RemBal 'Arrears'
            ,isnull(( ISNULL(Fees,0) + ISNULL(sum(Price),0)),0) + isnull((RemBal),0) 'NetBal',fg.Month
            from tblFeesGenerate as fg left join tblFeesCollection as fc on fg.AdmissionNo=fc.AdmissionNo inner join tblStdReg as sr on fg.AdmissionNo=sr.AdmissionNo
            inner join tblDefClass as dc on fg.ClassID=dc.ClassID inner join tblDefSection as ds on fg.SectionID=ds.SectionID
            where fc.AdmissionNo is null or fc.AdmissionNo is not null and
            fg.Year = '2019' and fg.Month='10-Oct' and fg.YearID='23' and
            fc.AdmissionNo not IN(SELECT AdmissionNo FROM tblFeesCollection WHERE FeesMonth = '10-Oct' AND Paid >= 0 )
            and FeeID IN (SELECT MAX(FeeID) FROM tblFeesCollection group by AdmissionNo)
            Group by fg.AdmissionNo,fg.Month,RemBal,fg.Year,Fees order by AdmissionNo asc;"))       // where fa.SessionID=@SesId and fa.SetExamID=@EId and fa.ClassID=@CId and fa.SectionID=@SId
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                //cmd.Parameters.AddWithValue("@AdmissionNo", txtAdNo.Text.Trim());
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DSVoucher dsCustomers = new DSVoucher())
                {
                    sda.Fill(dsCustomers, "VoucherDT");
                    return dsCustomers;
                }
            }
        }
    }
}

how to show the Arrears, Recievable and NetBalance?

You are viewing reply posted by: dharmendr 4 days ago.
Posted 4 days ago
dharmendr says:
crystalReport.Database.Tables["Head_Name"].SetDataSource(dsCustomers.Tables["Head_Name"])

 Check the above line of code. What you are getting in dsCustomers.Tables["Head_Name"]