1
votes

I have a webform with a single button and one crystal report viewer. Crystal report is based on a MS SQL stored procedure & designed using a connection that uses SQL Server authentication. What I have noticed that, if I call the method for showing the report within the page load, my crystal repot loads perfectly. However, if I trigger the report load through the button click (Which is a must for me to accept parameters for the report at deployment) the below happens:

  1. Crystal report parameters are prompted (Which doesn't occur when report is loaded without a postback).
  2. After the 1st page being loaded, 2nd page navigation prompts for database login. Without providing the login details (usually the password only), cannot go ahead. This logon prompt is only applicable to 2nd page of the report.

To circumvent the situation, I created a new connection with integrated security & postback stopped asking for the parameters and database login. Unfortunately, I cannot use integrated security for deploying the application & pry for a proper solution or workaround.

Fearing the worst, I already have RDLC report designed with the same requirements, however, I see that I have better formatting options like drawing lines over the sub reports to split columns when Crystal Report is used.

Here is what I have I tried until now

ASP page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="c2.aspx.cs" Inherits="CrystalTest.c2" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>

    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Show Report" OnClick="Button1_Click" />

            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="False" GroupTreeImagesFolderUrl="" Height="1202px" ReportSourceID="CrystalReportSource1" ToolbarImagesFolderUrl="" ToolPanelView="None" ToolPanelWidth="200px" Width="1104px" />

        </div>
    </form>
</body>
</html>

Code behind

using CrystalDecisions.CrystalReports.Engine;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CrystalTest
{
    public partial class c2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //ShowReport();
        }

        private void ShowReport()
        {
            ReportDocument oRpt = new ReportDocument();
            oRpt.Load(Server.MapPath(@"~/CrystalReport2.rpt"));


            oRpt.SetParameterValue(0, 2020);
            oRpt.SetParameterValue(1, 1);
            oRpt.SetParameterValue(2, "3");
            oRpt.SetParameterValue(3, "1");
            oRpt.SetParameterValue(4, "1");

            CrystalReportViewer1.Visible = true;
            CrystalReportViewer1.ReportSource = oRpt;
            //oRpt.SetDatabaseLogon("sa", "password", "RT04", "menass");
            oRpt.DataSourceConnections[0].SetConnection("RT04", "menass", true);


        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowReport();
        }
    }
}
2

2 Answers

0
votes

I have referred the following threads from different websites and was able develop a simple solution.

1.https://www.c-sharpcorner.com/forums/crystal-reports-asking-for-database-login-credentials 2.https://forums.asp.net/post/1797759.aspx

All I needed was to pass the logon information back to the report viewer once after POSTBACK happens. Example as below:

ASP Page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="c3.aspx.cs" Inherits="CrystalTest.c3" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Print Button" OnClick="Button1_Click" />
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="false" />
        </div>
    </form>
</body>
</html>

Code Behind

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.Web;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CrystalTest
{
    public partial class c3 : System.Web.UI.Page
    {
        public ReportDocument oRpt;
        protected void Page_Load(object sender, EventArgs e)
        {

            if (IsPostBack)
            {
                ConnectionInfo myConnectionInfo = new ConnectionInfo();

                myConnectionInfo.ServerName = "RT04";
                myConnectionInfo.DatabaseName = "MenaSS";
                myConnectionInfo.UserID = "sa";
                myConnectionInfo.Password = "password";
                SetdblogonforReport(myConnectionInfo);
            }

        }

        private void SetdblogonforReport(ConnectionInfo myConnectionInfo)
        {
            //throw new NotImplementedException
            TableLogOnInfos mytableloginfos = new TableLogOnInfos();
            mytableloginfos = CrystalReportViewer1.LogOnInfo;
            foreach (TableLogOnInfo myTableLogOnInfo in mytableloginfos)
            {
                myTableLogOnInfo.ConnectionInfo = myConnectionInfo;
            }

        }

        private void ShowReport()
        {

            if (!IsPostBack | Session["Report"] == (default))
            {
                oRpt = new ReportDocument();
                oRpt.Load(Server.MapPath(@"~/CrystalReport2.rpt"));
                oRpt.SetParameterValue(0, 2020);
                oRpt.SetParameterValue(1, 1);
                oRpt.SetParameterValue(2, "9");
                oRpt.SetParameterValue(3, "1");
                oRpt.SetParameterValue(4, "1");

                Session.Add("Report", oRpt);
            }
            else
            {
                oRpt = (ReportDocument)Session["Report"];
            }

            CrystalReportViewer1.ReportSource = oRpt;
            oRpt.SetDatabaseLogon("sa", "password", "RT04", "menass");

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowReport();
        }

    }

}

This solution is good enough for sub reports also, as all the authentications are taken care by the single logon call using the method SetdblogonforReport.

Hope this helps few others out there.

0
votes

I've a better solution & not deleting the previous one because it does deal with the postback when page_load method is present in a page. The below answer (which is recommended by SAP) is the one I am currently using for my project.

Resolving this requirement using "Session" and "page_init" as suggested. Please note, my application need to collect many inputs from the end user before generating the report & many of the text inputs have Autopostback set as true. Everytime the autopostback happens, Crystal Report refreshes, hence I recommend using as less possible Autopostback enabled input fields with your web form.

Sample ASP page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="c4.aspx.cs" Inherits="CrystalTest.c4" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.4000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Button ID="Button1" runat="server" Text="Print Report" OnClick="Button1_Click" />
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
        </div>
    </form>
</body>
</html>

and the code behind (copied from a crude test form, please make sure that you follow the coding standards recommended). Please note, this sample doesn't have "page_load" method, hence if you have specific situations where page_load is required, you will have to adapt much tedious approaches or clicking the button to generate the report should be redirected to a page where you have only the report.

using CrystalDecisions.CrystalReports.Engine;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CrystalTest
{
    public partial class c4 : System.Web.UI.Page
    {
        protected void Page_Init(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                CrystalReportViewer1.ReportSource = (ReportDocument)Session["Report"];
            }
        }
        private void ShowReport1()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GETMONTHSALARY", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@pProcessSection", SqlDbType.VarChar).Value = "9";
                    cmd.Parameters.Add("@pProcessSite", SqlDbType.VarChar).Value = "1";
                    cmd.Parameters.Add("@pProcessCatg", SqlDbType.VarChar).Value = "1";
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds, "SalaryDT");
                    ReportDocument oRpt = new ReportDocument();
                    oRpt.Load(Server.MapPath(@"~/dataset/CrystalReport1.rpt"));
                    oRpt.DataSourceConnections.Clear();
                    oRpt.SetDataSource(ds);
                    oRpt.Subreports[0].SetDataSource(FillOverTime());
                    CrystalReportViewer1.Visible = true;
                    CrystalReportViewer1.ReportSource = oRpt;
                    Session["Report"] = oRpt;
                }
            }
        }
        private DataSet FillOverTime()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["menass"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("GetEmployeeOverTime", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@pEmployeeCode", SqlDbType.VarChar).Value = DBNull.Value;
                    cmd.Parameters.Add("@pProcessYear", SqlDbType.Int).Value = 2020;
                    cmd.Parameters.Add("@pProcessMonth", SqlDbType.Int).Value = 1;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataSet ds1 = new DataSet();
                    adapter.Fill(ds1, "OverTimeDT");
                    return ds1;
                }
            }
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            ShowReport1();
        }
    }
}