Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# CrystalReports
Dear All,
 
I have one crystal report with multiple Subreport. and I want to link with the c#. but there is a problem that two sub report link is not getting.
 
I have 4 command used for query. and all are link with one main command.
 
i used the main code as below :
 
 
   from_date12 = Request.QueryString["selected_date1"];
            to_date12 = Request.QueryString["selected_date2"];
 
            c.setcon();
            string s = "select name from empl where id='" + Session["user"].ToString() + "'";
            SqlDataAdapter ad = new SqlDataAdapter(s, c.getcon());
            DataSet ds = new DataSet();
            ad.Fill(ds);
 

            string s1 = "select * from munim002.dbo.sales_ctc where doj is not null and munim002.dbo.sales_ctc.refby='" + ds.Tables[0].Rows[0][0].ToString() + "'  union select * from munim004.dbo.sales_ctc where doj is not null and munim004.dbo.sales_ctc.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim005.dbo.sales_ctc where doj is not null and munim005.dbo.sales_ctc.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim008.dbo.sales_ctc where doj is not null and munim008.dbo.sales_ctc.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim017.dbo.sales_ctc where doj is not null and munim017.dbo.sales_ctc.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' order by refby ";
            SqlDataAdapter ad1 = new SqlDataAdapter(s1, c.getcon());
            DataSet ds1 = new DataSet();
            ad1.Fill(ds1);
 
            string path = string.Empty;
            path = Server.MapPath("REPORTS/sales ctc.rpt"); // load the report
            cr = new ReportDocument();
            cr.Load(path);
 
            cr.SetDataSource(ds1.Tables[0]);
            cr.SetParameterValue(0, ds1.Tables[0].Rows[0][0].ToString()); // set the parameter 1
            cr.SetParameterValue(1, from_date12); // 
            cr.SetParameterValue(2, to_date12);
            
            
            if (ds1.Tables[0].Rows.Count != 0)
            {
                string sub_report = "select * from munim002.dbo.sales_ctc1 where doj is not null and munim002.dbo.sales_ctc1.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim004.dbo.sales_ctc1 where doj is not null and munim004.dbo.sales_ctc1.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim005.dbo.sales_ctc1 where doj is not null and munim005.dbo.sales_ctc1.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim008.dbo.sales_ctc1 where doj is not null and munim008.dbo.sales_ctc1.refby='" + ds.Tables[0].Rows[0][0].ToString() + "' union select * from munim017.dbo.sales_ctc1 where doj is not null  and munim017.dbo.sales_ctc1.refby='" + ds.Tables[0].Rows[0][0].ToString() + "'";
                //string sub_report = "select * from munim002.dbo.sales_ctc1 where doj is not null union select * from munim004.dbo.sales_ctc1 where doj is not null union select * from munim005.dbo.sales_ctc1 where doj is not null union select * from munim008.dbo.sales_ctc1 where doj is not null union select * from munim017.dbo.sales_ctc1 where doj is not null ";
                SqlDataAdapter sub_ad = new SqlDataAdapter(sub_report, c.getcon());
                DataSet sub_ds = new DataSet();
                sub_ad.Fill(sub_ds);
 
                cr.Subreports["sales invoice.rpt - 01"].SetDataSource(sub_ds.Tables[0]);
                for (int i = 0; i < sub_ds.Tables[0].Rows.Count; i++)
                {
                    cr.SetParameterValue(0, sub_ds.Tables[0].Rows[i][1].ToString());
                    cr.SetParameterValue(1, from_date12);
                    cr.SetParameterValue(2, to_date12);
                }
 
                string sub_rep2 = "select * from munim002.dbo.sales_ctc2 where doj is not null union select * from munim004.dbo.sales_ctc2 where doj is not null  union select * from munim005.dbo.sales_ctc2 where doj is not null union select * from munim008.dbo.sales_ctc2 where doj is not null union select * from munim017.dbo.sales_ctc2 where doj is not null  order by refby ";
                SqlDataAdapter sub_ad2 = new SqlDataAdapter(sub_rep2, c.getcon());
                DataSet sub_ds2 = new DataSet();
                sub_ad2.Fill(sub_ds2);
 
                cr.Subreports["sales invoice.rpt - 02"].SetDataSource(sub_ds2.Tables[0]);
                for (int j = 0; j < sub_ds2.Tables[0].Rows.Count; j++)
                {
                    cr.SetParameterValue(0, sub_ds2.Tables[0].Rows[j][1].ToString());
                    cr.SetParameterValue(1, from_date12);
                    cr.SetParameterValue(2, to_date12);
                }
 
                string sub_report4 = " select * from munim002.dbo.sales_ctc3 where doj is not null  union select * from munim004.dbo.sales_ctc3 where doj is not null union select * from munim005.dbo.sales_ctc3 where doj is not null union select * from munim008.dbo.sales_ctc3 where doj is not null union select * from munim017.dbo.sales_ctc3 where doj is not null   order by refby";
                SqlDataAdapter sub_ad4 = new SqlDataAdapter(sub_report4, c.getcon());
                DataSet sub_ds4 = new DataSet();
                sub_ad4.Fill(sub_ds4);
 
                cr.Subreports["sales invoice.rpt - 03"].SetDataSource(sub_ds4.Tables[0]);
                for (int i = 0; i < sub_ds4.Tables[0].Rows.Count; i++)
                {
                    cr.SetParameterValue(0, sub_ds4.Tables[0].Rows[i][1].ToString());
                    cr.SetParameterValue(1, from_date12);
                    cr.SetParameterValue(2, to_date12);
                }
 
                // here one 
                //string sub_rep3 = "select * from munim002.dbo.se_exp_qry where munim002.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' union all select * from munim004.dbo.se_exp_qry where munim004.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' union all select * from munim005.dbo.se_exp_qry where munim005.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' union all select * from munim008.dbo.se_exp_qry where munim008.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "'  union all select * from munim017.dbo.se_exp_qry where munim017.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' ";
                string sub_rep3 = "select * from munim002.dbo.se_exp_qry union all select * from munim003.dbo.se_exp_qry union all select * from munim005.dbo.se_exp_qry union all select * from munim008.dbo.se_exp_qry union all select * from munim017.dbo.se_exp_qry";
                SqlDataAdapter sub_ad3 = new SqlDataAdapter(sub_rep3, c.getcon());
                DataSet sub_ds3 = new DataSet();
                sub_ad3.Fill(sub_ds3);
 
                cr.Subreports["sales expense.rpt"].SetDataSource(sub_ds3.Tables[0]);
                cr.SetParameterValue(0, ds1.Tables[0].Rows[0][0].ToString());
                cr.SetParameterValue(1, from_date12);
                cr.SetParameterValue(2, to_date12);
 
                string sub_report5 = "select * from munim002.dbo.se_exp_qry where munim002.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' union all select * from munim004.dbo.se_exp_qry where munim004.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' union all select * from munim005.dbo.se_exp_qry where munim005.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' union all select * from munim008.dbo.se_exp_qry where munim008.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "'  union all select * from munim017.dbo.se_exp_qry where munim017.dbo.se_exp_qry.prtname='" + ds.Tables[0].Rows[0][0].ToString() + "' ";
                SqlDataAdapter sub_ad5 = new SqlDataAdapter(sub_report5, c.getcon());
                DataSet sub_ds5 = new DataSet();
                sub_ad5.Fill(sub_ds5);
 
                cr.Subreports["sales expense.rpt - 01"].SetDataSource(sub_ds5.Tables[0]);
                for (int i = 0; i < sub_ds5.Tables[0].Rows.Count; i++)
                {
                    string wq = sub_ds.Tables[0].Rows[i][1].ToString();
                    cr.SetParameterValue(0, wq);
                    cr.SetParameterValue(1, from_date12);
                    cr.SetParameterValue(2, to_date12);
                }
 
                string qry_pyrl = "SELECT PL_EMPCD, PL_EMPNAME, PL_MONTH, PL_YEAR,  PL_TOTALPAY FROM MUNIM002.DBO.PAYROLLGEN UNION ALL SELECT PL_EMPCD, PL_EMPNAME, PL_MONTH, PL_YEAR, PL_TOTALPAY FROM MUNIM004.DBO.PAYROLLGEN UNION ALL SELECT PL_EMPCD, PL_EMPNAME, PL_MONTH, PL_YEAR, PL_TOTALPAY FROM MUNIM005.DBO.PAYROLLGEN UNION ALL SELECT PL_EMPCD, PL_EMPNAME, PL_MONTH, PL_YEAR,  PL_TOTALPAY FROM MUNIM008.DBO.PAYROLLGEN UNION ALL SELECT PL_EMPCD, PL_EMPNAME, PL_MONTH, PL_YEAR, PL_TOTALPAY FROM MUNIM017.DBO.PAYROLLGEN";
                SqlDataAdapter ad_qry = new SqlDataAdapter(qry_pyrl, c.getcon());
                DataSet ds_qry = new DataSet();
                ad_qry.Fill(ds_qry);
 
                cr.Subreports["sales payroll.rpt - 01"].SetDataSource(ds_qry.Tables[0]);
 
                cr.SetParameterValue(0, sub_ds2.Tables[0].Rows[0][0].ToString());
                cr.SetParameterValue(1, from_date12);
                cr.SetParameterValue(2, to_date12);
 
                CrystalReportViewer1.ReportSource = cr;
                CrystalReportViewer1.DataBind();
 
                }
 

 
any help for this.
 

Thanks in advance.
Mitesh
Posted 9-Jan-13 23:56pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

if a Parameter has the value NULL set it to DBNull.Value. A NULL valued parameter is not send to the database where a DBNull.Value is send to the database.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 424
1 ridoy 400
2 DamithSL 230
3 OriginalGriff 179
4 Suvendu Shekhar Giri 150
0 OriginalGriff 8,033
1 DamithSL 6,179
2 Sergey Alexandrovich Kryukov 5,538
3 Maciej Los 5,508
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100