Report Viewer multiple Datasets

Question

I have a web application that should display a report viewer. Here's my steps:

1) adding a script manager and a report viewer to a new web form

2) adding a report and bind it to a dataset called dataset1

3) create a parameter for the query

4) write code behind for the webform...

   private DataTable GetData(Int64 id_doc)
    {
        DataTable dt = new DataTable();
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringloginDb"].ConnectionString;
        try
        {
            using (var conn = new MySqlConnection(connStr))
            {
                string sSQL = "select * from details_doc where id_doc=@id_doc";
                MySqlCommand cmd = new MySqlCommand(sSQL, conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(new MySqlParameter("@id_doc", Session["id_doc"].ToString()));
                MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                adp.Fill(dt);
            }
        }
        catch (Exception)
        {
            throw;
        }
        return dt;
    }

    protected void showReport()
    {
        DataTable dt = GetData(Convert.ToInt64(Session["id_doc"].ToString()));
        rptViewer.LocalReport.Refresh();
        rptViewer.Reset();
        rptViewer.LocalReport.EnableExternalImages = true;
        this.rptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
        ReportDataSource rds = new ReportDataSource("DataSet1", dt);
        rptViewer.LocalReport.DataSources.Add(rds);
        rptViewer.LocalReport.ReportPath = "ReportInvoice.rdlc";
        ReportParameter rptParam = new ReportParameter("ReportParameter1", Session["id_doc"].ToString());
        rptViewer.LocalReport.SetParameters(rptParam);
        rptViewer.LocalReport.Refresh();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack){
            if (Session["id_doc"] != null)
            {
                GetData(Convert.ToInt64(Session["id_doc"]));
                mostraReport();
            }
        }
    }

Now the code works just fine, but I need the report to display another , second table on it. I am going to add another data set and another report parameter, but then I got stuck regarding the code. Any help will be appreciated.

Answer 1

I eventually managed to pass two parameters for my report viewer. Here's how:

1. In design mode add a table adapter.
2. Drag and drop the tables you need.
3. Press use SQL Instruction, press Query generator and paste you query there. At "Where" condition specify this: WHERE (h.id_doc = @par1) AND (h.id_client= @par2)
4. In report viewer add your two parameters
5. Design your report.rdlc
6. Write code-behind

private DataTable GetData(Int64 id_doc)
    {

        DataTable dt = new DataTable();
        string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringloginDb"].ConnectionString;
        try
        {
            using (var conn = new MySqlConnection(connStr))
            {
                string sSQL = "SELECT c.*, users.*, t.*, d.* FROM head_doc h INNER JOIN doc_details d ON h.id_doc = d.id_doc INNER JOIN clients c ON c.id_client = h.id_client INNER JOIN users ON h.id_user = users.id_user WHERE (h.id_doc = @par1) AND (h.id_client = @par2)";
                MySqlCommand cmd = new MySqlCommand(sSQL, conn);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add(new MySqlParameter("@par1", Session["id_doc"].ToString()));
                cmd.Parameters.Add(new MySqlParameter("@par2", Session["id_client"].ToString()));
                MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
                adp.Fill(dt);
            }
        }
        catch (Exception ex)
        {
            lblError.Text = ex.Message;
        }
        return dt;
    }

    protected void showReport()
    {
        DataTable DataTable1 = GetData(Convert.ToInt64(Session["id_doc"].ToString()));
        report.LocalReport.Refresh();
        report.Reset();
        report.LocalReport.EnableExternalImages = true;
        this.report.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
        //Aggiunta dataset
        ReportDataSource rds2 = new ReportDataSource("DataSet2", DataTable1);
        report.LocalReport.DataSources.Add(rds2);
        //Path
        report.LocalReport.ReportPath = "ReportFatture.rdlc";
        //Parameters
        ReportParameter rptParam = new ReportParameter("par1", Session["id_doc"].ToString());
        ReportParameter rptParam2 = new ReportParameter("par2", Session["id_client"].ToString());
        report.LocalReport.SetParameters(rptParam);
        report.LocalReport.SetParameters(rptParam2);
        report.LocalReport.Refresh();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {

            if ((Session["id_doc"] != null) && (Session["id_client"] != null))
            {
                showReport();
            }
            else
            {
                Response.Redirect("/Pages/Account/Whops.aspx");
            }
        }
    }

I hope this helps. Thanx.