The report is empty. What's wrong with the code?

Question

0.00/5 (No votes)

See more:

This is my load method:
The datasource here is a stored procedure, I am trying to get values from the query and send it as a parameter to the stored procedure, What I am doing wrong?

C#

protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        System.Data.SqlClient.SqlConnection myConnection =
            new System.Data.SqlClient.SqlConnection( "user id=;" +
                    "password=;server=localhost;" +
                    "Trusted_Connection=yes;" +
                    "database=;" + "db;" + 
                    "connection timeout=30");

        myConnection.Open();

        System.Data.SqlClient.SqlCommand myCommand_FirstName =
            new System.Data.SqlClient.SqlCommand( "SELECT [Patient-ID] " +
                 "FROM [Patient-Info] " +
                 "WHERE [First Name]='" + Request.QueryString["param3"] + 
                 "' AND [Last Name]='" + Request.QueryString["param4"] + "';"
                 , myConnection);

        String patient_ID = "";
        if (myCommand_FirstName.ExecuteScalar() != null)
        {
            patient_ID = myCommand_FirstName.ExecuteScalar().ToString();
        }

        CrystalDecisions.CrystalReports.Engine.ReportDocument reportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
        ParameterField paramField = new ParameterField();
        ParameterFields paramFields = new ParameterFields();
        paramField.Name = "@ID";
        ParameterDiscreteValue paramDiscreteValue = new ParameterDiscreteValue();
        paramDiscreteValue.Value = patient_ID;
        paramField.CurrentValues.Add(paramDiscreteValue);
        paramFields.Add(paramField);

        paramField = new ParameterField();
        paramField.Name = "@Med";
        paramDiscreteValue = new ParameterDiscreteValue();
        paramDiscreteValue.Value = Request.QueryString["param1"];
        paramField.CurrentValues.Add(paramDiscreteValue);
        paramFields.Add(paramField);

        paramField = new ParameterField();
        paramField.Name = "@Year";
        paramDiscreteValue = new ParameterDiscreteValue();
        paramDiscreteValue.Value = Request.QueryString["param2"];
        paramField.CurrentValues.Add(paramDiscreteValue);
        paramFields.Add(paramField);

        reportDocument.Load(Server.MapPath("CrystalReport1.rpt"));
        CrystalReportViewer1.ParameterFieldInfo = paramFields;
        CrystalReportViewer1.ReportSource = reportDocument;

        myConnection.Close();
    }
}

What I have tried:

And this is where I have defined the CrystalReportViewer:

ASP.NET

<form id="form1" runat="server">
   <div>
        <CR:CrystalReportViewer  ID="CrystalReportViewer1" width="1000px" height="700px" runat="server" AutoDataBind="True"  ToolPanelView="None" OnInit="CrystalReportViewer1_Init" DisplayToolbar="False" EnableDatabaseLogonPrompt="False" GroupTreeImagesFolderUrl="" ReportSourceID="CrystalReportSource1" ToolbarImagesFolderUrl="" ToolPanelWidth="200px" EnableParameterPrompt="False"    />
        <CR:CrystalReportSource ID="CrystalReportSource1" runat="server">
            <Report FileName="CrystalReport1.rpt">
            </Report>
        </CR:CrystalReportSource>
   </div>
</form>

Posted 4-May-16 1:46am

Member 1246150

Updated 4-May-16 20:41pm

Santosh Kokatnur

v2

Add a Solution

Comments

Richard Deeming 4-May-16 13:57pm

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Particularly worrying in something that refers to "patient information". I hope your employers are ready to pay a massive fine for leaking medical data!

Member 1246150 4-May-16 15:26pm

Haha, thank you for your comment... Don't worry this is just a course project to learn! :-)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

an0ther1 · Answer 1 · 2016-05-04T13:58:00

First thing, confirm you actually have a value in patient_ID. If it is an empty string then your Report will be blank which - unless you have data with a blank record Id - then you will get a blank report.

Second thing, ensure you have a Report document.
Server.MapPath should reference the full path to your report based on the current page path or the root of the web site.
Assume you have the following structure;
www.mysite.com/mywebpage.aspx
www.mywebsite.com/Reports/CrystalReport1.rpt
To run the report from mywebpage you would need access it using the following;

C#

Server.MapPath("~/Reports/CrystalReport1.rpt");

Always reference from the root of your website by using the tilde (~) character.

Third, you are not passing the parameters or values to the report. You are creating discrete parameters but you are adding them to a new ParameterFields collection, not the Reports ParameterFields collection.
If you run the Report in Crystal Report & it prompts for the parameters then you need to do as follows;

C#

foreach(CrystalDecsions.CrystalReports.Engine.ParameterFieldDefinition objParam in reportDocument.DataDefinition.ParameterFields)
{
    // confirm you are accessing a main report parameter and not a sub report parameter
    if(objParam.ReportName == string.empty)
    {
        string strParamName = objParam.ParameterFieldName;
        reportDocument.SetParameterValue(strParamName, "myValue");
    }
}

I personally do not add discreet parameters to a Report because the Report may then load all of the data and then "hide" the data that does not meet the provided values - this is especially true with sub-reports. This is really bad for performance on big datasets & allows for the use of wildcards, which can allow the user to get data that you did not intend them to have.
My Reports typically retrieve data from a stored procedure due to the inherent performance and security benefits.

Kind Regards