Click here to Skip to main content
12,444,790 members (52,156 online)
Rate this:
 
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 22: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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160811.3 | Last Updated 11 Jan 2013
Copyright © CodeProject, 1999-2016
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