Crystal Reports not selecting any records when I add a parameter to the report

Question

I'm using Crystal Reports inside of Visual Studio 2010 with ASP.NET.

I've got a DataSet that pulls data from a database, and it seems to load into the report just fine. When I view the report in my browser, all records load correctly.

I have to add a parameter for the user to enter in order to filter out entries in the report. The problem is that when I add any sort of parameter to the report, the viewer can't find any records. This is BEFORE I do anything with the selection wizard.

I added a drop down list parameter just containing the numbers 1, 2, and 3. Without anything in the selection expert this shouldn't affect anything. But for whatever reason, when this is in the report no records get selected. As soon as I delete the parameter it works again.

I'm adding the parameter and its values through the field explorer if that makes a difference.

The odd thing is that is implementation worked when I had the report pull its data directly from the database, instead of creating a DataSet first. The reason I can't use this implementation is that it kept asking for the database login information. Even when I predefined the information. I switched to a DataSet so I would avoid the login prompt.

I'm figuring that there has to be something wrong with my DataSet implementation. The relevant code is here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;


public partial class reportPage : System.Web.UI.Page
{

    private ReportDocument rpt;
    private string mapPath;

    protected void Page_Load(object sender, EventArgs e)
    {

        if (!Page.IsPostBack) // If the page is loaded for the first time
        {

            mapPath = "CrystalReport1.rpt";
            ConfigureCrystalReport();

        }

    }

    private void ConfigureCrystalReport() // Creates the instance of the report file and binds it to the viewer on the page
    {

        myDataSet ds = new myDataSet();

        myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();
        dsTA.Fill(ds.myTable);

        // Initializing the report file
        rpt = new ReportDocument();
        string reportPath = Server.MapPath(mapPath);
        rpt.Load(reportPath);

        DataTable dt = ds.myTable;
        Response.Write("<script>alert('Table has "+ dt.Rows.Count.ToString() +" rows');</script>");
        rpt.SetDataSource(dt); 

        // Binding the report file to the report viewer on the page
        CrystalReportViewer1.ReportSource = rpt;
        CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
        CrystalReportViewer1.HasToggleGroupTreeButton = false;

    }

The response.write makes an alert that pops up on the page stating how many rows are in the table being sent to the report. It counts it correctly, so I know that the data IS being sent to the report, but it isn't selecting any of it.

I honestly have no idea why it isn't selecting anything.

EDIT: After some quick testing it looks like I can add the parameter to the list without it breaking the report. It's when I put the actual parameter element on the page, OR when I use it as part of the selection expert that the report fails to load rows.

In other words, the report works only when it doesn't bring up the parameter prompt.

EDIT 2: I tried changing up my DataSet code in order to have it actually connect to the database. I thought i was using DataSets to avoid that... but I saw it on a tutorial video.

The code is here:

        string sConnectionString;
        sConnectionString = "Password=mYp@ssw0rd*;User ID=sa;" + "Initial Catalog=databaseName;" + "Data Source=serverName";
        SqlConnection conn = new SqlConnection(sConnectionString);
        conn.Open();
        SqlDataAdapter sDA = new SqlDataAdapter("Select * from myTable", conn);
        conn.Close();

        myDataSet ds = new myDataSet();

        sDA.Fill(ds, "myTable");
        // Initializing the report file
        rpt = new ReportDocument();
        string reportPath = Server.MapPath(mapPath);
        rpt.Load(reportPath);

        rpt.SetDataSource(ds);

And after all of that: nothing. It still loads all of the data from the table if there are no parameters at work. As soon as I add a parameter (to the page, or as part of the selection expert) no records load.

EDIT 3: I've tried passing in a parameter programatically with this:

rpt.SetParameterValue("par1", 1);

Interestingly, it did load all of the data in the DataSet... until I passed it a new parameter. Then no records loaded, even after I set the parameter back to 1. It seems like once the viewer has to handle a parameter it drops all of the records.

I really don't know where to look now. As far as I can tell, this is a problem with rpt.SetDataSource() or CrystalReportsViewer1.ReportSource = rpt.

EDIT 4: As user4663200 pointed out there's an issue with the mapPath being null when the page is posted back. I tried fixing it, but ran into a new error, a popup shows up and says 'Please wait while the document is being processed.' forever. I looked into this error and multiple sources told me to transfer the loading code to Page_Init() from Page_Load().

The important part is that this error is somehow cause when the page posts back after the user enters a parameter. Apparently the page would have loaded fine if it didn't post back.

I tried implementing a simpler version of my code in just Page_Init, but it doesn't work, there's still that unending load popup.

Here's my Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;  



private ReportDocument rpt;
private myDataSet ds;

protected void Page_Init(object sender, EventArgs e)
    {

        if (!Page.IsPostBack)
        {

            ds = new myDataSet();


            myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();

            dsTA.Fill(ds.myTable);
            // Initializing the report file
            rpt = new ReportDocument();
            string reportPath = Server.MapPath("myReport.rpt");
            rpt.Load(reportPath);
            Session.Add("dataSet", ds);
            Session["dataSet"] = ds;

            rpt.SetDataSource(ds);

            CrystalReportViewer1.ReportSource = rpt;

        }
        else
        {

            ds = (myDataSet)Session["Report"];
            rpt = new ReportDocument();
            string reportPath = Server.MapPath("myReport.rpt");
            rpt.Load(reportPath);
            rpt.SetDataSource(ds);
            CrystalReportViewer1.ReportSource = rpt;

        }

    }

The issue is that the other solutions call for the REPORT to be saved in session, but for that to happen, Sessionstate needs to be set to "InProc" mode. I can't take this website off of "StateServer" mode. I tried a workaround by saving the DataSet in session, and then binding that to a new report each time. But this didn't work either.

EDIT 4.5 I just tried changing the sessionState to InProc, and this still doesn't work. Even when I make it so the report is what's being saved in session.

EDIT 4.75 I tried putting a breakpoint in the Page_Init(), and it revealed some interesting things.

I could see that Session["Report"]; was correctly being saved during the initial run through the code.

But after entering the parameter I watched Page_Init() again, and when it tried doing rpt = (ReportDocument)Session["Report"]; everything in Session["Report"] was null. It threw lots of null exceptions, but these were only visible in the breakpoint debugger, they never seemed to come up during a normal run. I'm pretty sure that's why it keeps loading forever after the post back.

So the problem now is that the post back after entering a parameter causes all data in the session to be lost.

EDIT 4.825: You can disregard the previous edit. I commented out this code:

    protected void Page_UnLoad(object sender, EventArgs e)
    {
        try
        {
            rpt.Close();
            rpt.Dispose();
        }
        catch { }
    }

After getting rid of this code, I looked through the Session["Report"] variable again and it is no longer null.

Interestingly, Page_UnLoad is being called BEFORE even getting to the parameter entry. If rpt.Close(); is dumping all references to rpt, then it's as if the Session variable is acting like a pointer, instead of storing an actual rpt object. So when rpt is Disposed(), Session["Report"] appears to just be a null pointer.

So now it with the right stuff being passed around... it still has the infinite 'document processing' screen.

EDIT 5: I finally got it to work.

It turns out that sessions were the solution, but I couldn't find any good implementations of it.

I got most of my relevant code from here. But you can't just copy and paste that.

Here's my implementation

    private ReportDocument rpt;
    private myDataSet ds;

    protected void Page_Init(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {

            Session["Report"] = null;

        }

        if (Session["Report"] == null)
        {

            ds = new myDataSet();
            myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();
            DataTable dt = dsTA.GetData();

            rpt = new ReportDocument();
            rpt.Load(Server.MapPath("myReport.rpt"));
            rpt.SetDataSource(dt);
            Session.Add("Report", rpt);
            CrystalReportViewer1.ReportSource = rpt;
            CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
            CrystalReportViewer1.HasToggleGroupTreeButton = false;

        }

    }

    protected void Page_Load(object sender, EventArgs e)
    {

        if (Page.IsPostBack)

        {

            rpt = (ReportDocument)Session["Report"];
            CrystalReportViewer1.ReportSource = rpt;
            CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
            CrystalReportViewer1.HasToggleGroupTreeButton = false;

        }

    }

    protected void butReport_Click(object sender, EventArgs e)
    {

        if (Session["Report"] == null) // Report is not in session (previously loaded) so load report, set params, view and place in session
        {

            ds = new myDataSet();
            myDataSetTableAdapters.myTableTableAdapter dsTA = new myDataSetTableAdapters.myTableTableAdapter();
            DataTable dt = dsTA.GetData();

            rpt = new ReportDocument();
            rpt.Load(Server.MapPath("myReport.rpt"));
            rpt.SetDataSource(dt);
            Session.Add("Report", rpt);
            CrystalReportViewer1.ReportSource = rpt;
            CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
            CrystalReportViewer1.HasToggleGroupTreeButton = false;

        }
        else
        {

            rpt = (ReportDocument)Session["Report"];
            CrystalReportViewer1.ReportSource = rpt;
            CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.ParameterPanel;
            CrystalReportViewer1.HasToggleGroupTreeButton = false;

        }

    }

The big difference is this line: DataTable dt = dsTA.GetData(); Before I was passing in the entire DataSet to rpt. I'm not sure why, but creating a new DataTable with GetData()'s results lets the report stay in session.

Now my report is kept in session even with all of the postbacks. I was encountered and solved a few other issues, like the session not resetting when the user leave the page and comes back.

Answer 1

Solved. See edit 5 for an explanation.

Answer 2

Your SQL query is missing the where clause, so no matter what you put in for a parameter it will always return all of the records. Likely the reason on subsequent calls that it fails is that your mapPath is blank, you create it on every Page_Load, but only set it if its not a postback. This means that after the initial page load, mapPath is null, so there is no report for it to pull.