Crystal Report viewer Subreport with linked parameter db logon failed

[Former Member]

Hi,

I searched a lot for this but unfortunately i am not able to find a solution. When i am running my report if it was a single report then  I am able to view it but if the report contained a sub-report with linked parameter i get DB logon failed while running it. Below is my code

using System;
using System.Data;
using System.Drawing;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.Web;
//using CrystalDecisions.ReportAppServer;
using CrystalDecisions.ReportAppServer.ClientDoc;
//using CrystalDecisions.ReportAppServer.DataDefModel;
//using CrystalDecisions.ReportAppServer.ReportDefModel;
using System.Data.OracleClient;






namespace Crystalreports_to_text
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected string countpar;
        ISCDReportClientDocument rptClientDoc = new ReportClientDocument();
        ReportDocument cryRpt = new ReportDocument();


        protected void Page_Load(object sender, EventArgs e)
        {


            if (!IsPostBack)
            {

                BindDropDownList();





            }
            else
            {

            }
        }


        protected void Page_Init(object sender, EventArgs e)
        {


        }


        private void BindDropDownList()
        {
            string[] reports = Directory.GetFiles("D:\\Docs\\IBS_reports_Mar_09_2016\\", "*.rpt");
            IDictionary sortedList = new SortedList();
            foreach (string path in reports)
            {
                int reportNamePrefix = path.LastIndexOf(@"\") + 1;
                int reportNameLength = path.Length - reportNamePrefix;
                string reportName = Path.GetFileNameWithoutExtension(path);
                sortedList.Add(path, reportName);
            }


            DropDownList1.DataSource = sortedList;
            DropDownList1.DataTextField = "value";
            DropDownList1.DataValueField = "key";
            DropDownList1.DataBind();



        }
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            string value = DropDownList1.SelectedValue;
            getpar();
        }






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

        }


        protected void setDbInfo()
        {
            CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;
            CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;
            CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;
            CrystalDecisions.CrystalReports.Engine.Database crDatabase;


            TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
            ConnectionInfo crConnectionInfo = new ConnectionInfo();
            Tables CrTables;
            Sections crSections;


            crConnectionInfo.ServerName = "SYSDSN";
            crConnectionInfo.DatabaseName = "";
            crConnectionInfo.UserID = "user";
            crConnectionInfo.Password = "pass";
            crConnectionInfo.IntegratedSecurity = false;


            crDatabase = cryRpt.Database;
            CrTables = crDatabase.Tables;


            foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in CrTables)
            {
                crtableLogoninfo = aTable.LogOnInfo;
                crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                aTable.ApplyLogOnInfo(crtableLogoninfo);
            }
            // THIS STUFF HERE IS FOR REPORTS HAVING SUBREPORTS
            // set the sections object to the current report's section
            crSections = cryRpt.ReportDefinition.Sections;
            // loop through all the sections to find all the report objects
            foreach (Section crSection in crSections)
            {
                crReportObjects = crSection.ReportObjects;
                //loop through all the report objects in there to find all subreports
                foreach (ReportObject crReportObject in crReportObjects)
                {
                    if (crReportObject.Kind == ReportObjectKind.SubreportObject)
                    {
                        crSubreportObject = (SubreportObject)crReportObject;
                        //open the subreport object and logon as for the general report
                        crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);
                        crDatabase = crSubreportDocument.Database;
                        CrTables = crDatabase.Tables;
                        foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in CrTables)
                        {
                            crtableLogoninfo = aTable.LogOnInfo;
                            crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                            aTable.ApplyLogOnInfo(crtableLogoninfo);
                        }
                    }
                }
            }
  }

        private void getpar()
        {


            rptClientDoc = cryRpt.ReportClientDocument;
            cryRpt.Load(DropDownList1.Text);
            int z = rptClientDoc.DataDefController.DataDefinition.ParameterFields.Count;
            if (z > 0)
            {


                int cnt = 0;
                foreach (CrystalDecisions.ReportAppServer.DataDefModel.ParameterField paramfield in rptClientDoc.DataDefController.DataDefinition.ParameterFields)
                {
                    cnt++;
                    Label myLabel = new Label();
                    TextBox tx = new TextBox();
                    myLabel.Text = paramfield.Name.ToString() + ":" + "   " ;
                    myLabel.ID = paramfield.Name.ToString();
                    tx.ID = "Textbox" + cnt;
                    tx.Attributes.Add("runat", "Server");
                    Panel1.Controls.Add(myLabel);
                    Panel1.Controls.Add(tx);
                    Panel1.Controls.Add(new LiteralControl("<br /> <br />"));
                }
            }
        }


        private void setpar()
        {
            rptClientDoc = cryRpt.ReportClientDocument;
            cryRpt.Load(DropDownList1.Text);
            int z = rptClientDoc.DataDefController.DataDefinition.ParameterFields.Count;
            List<string[]> ls = new List<string[]>();
            if (z > 0)
            {
                string controlid = string.Empty;
                int cnt = 0;
                foreach (CrystalDecisions.ReportAppServer.DataDefModel.ParameterField paramfield in rptClientDoc.DataDefController.DataDefinition.ParameterFields)
                {


                    cnt++;
                    controlid = "Textbox" + cnt.ToString();
                    TextBox tb = Panel1.FindControl(controlid) as TextBox;
                    String sValue = Request.Form[controlid];


                    ls.Add(new string[] { paramfield.Name.ToString(), sValue });
                }
                foreach (string[] items in ls)
                {
                            cryRpt.SetParameterValue(items[0], items[1]);
                }

            }
        }


        private void CrV()
        {


           setpar();
           setDbInfo();
           CrystalReportViewer1.ReportSource = cryRpt;
           CrystalReportViewer1.DataBind();              
        }            
    }
}

[former_member11696 · Accepted solution]

The line you commented out does the connection...

If it doesn't work in the KBA then you need to debug, Likely I am setting some value so go line by line.

Or enable CRLogger and see what is being passed.

Don

[former_member11696]

Windows or WEB app?

What run time are you using?

What SP of the runtime?

[Former Member]

Hi Don,

      Web app.

      I am using .net 4.5,  is that the runtime?

[former_member11696]

No, look in Programs and features and look for SAP.... you should have some SDK package installed.

[Former Member]

Hi,

     I got SAP Crystal reports, version for Visual Studio version 13.0.5.891

[former_member11696]

That is old and does not support Current Servers like 2012.

Download SP 17, first link is to integrate into VS and all others are fro redist packages ONLY, do not install them on your DEV PC.

READ all of the warning etc. and you'll find links to the Platform Doc's as well.

Dono

[Former Member]

Hi,

     I have installed the latest, but i am still getting the above error in database if a report contained sub-reports. I am still confused as why it is logging in correctly on a single parameter report and not with a sub report. As i know it should be strait forward, i tried few types of sub report login codes but i get nothing but login failed.

[former_member11696]

Search for KBA 2281780, be sure to select All of SAP ( blue box )

It has a Parameter and Logon app I wrote and should work for you, you'll need to modify the log on routine to call the set parameter function.

Don

[Former Member]

Hi Don,

     I have tried that KBA before but I still get Login failed. I found another post where you had the following:

protected void LogonDb()
        {
 //i did no under stand this so i commented it out.           //rptClientDoc.DatabaseController.SetTableLocationByServerDatabaseName("", "IBSDB", "TestDB", "sa", "sabkme");





            CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;
            CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;
            CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;
            CrystalDecisions.CrystalReports.Engine.Database crDatabase;
            CrystalDecisions.CrystalReports.Engine.Tables crTables;
            TableLogOnInfo crTableLogOnInfo;


            CrystalDecisions.Shared.ConnectionInfo crConnectioninfo = new CrystalDecisions.Shared.ConnectionInfo();


            //pass the necessary parameters to the connectionInfo object
                             crConnectioninfo.ServerName = "Oracle DSN";
                            crConnectioninfo.UserID = "user id";
                            crConnectioninfo.Password = "pass";
                            crConnectioninfo.DatabaseName = " I kept this empty";



            //set up the database and tables objects to refer to the current report
            crDatabase = cryRpt.Database;
            crTables = crDatabase.Tables;


            //loop through all the tables and pass in the connection info
            foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
            {
                crTableLogOnInfo = crTable.LogOnInfo;
                crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
                crTable.ApplyLogOnInfo(crTableLogOnInfo);
            }


            //set the crSections object to the current report's sections
            CrystalDecisions.CrystalReports.Engine.Sections crSections = cryRpt.ReportDefinition.Sections;


            //loop through all the sections to find all the report objects
            foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
            {
                crReportObjects = crSection.ReportObjects;
                //loop through all the report objects to find all the subreports
                foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
                {
                    if (crReportObject.Kind == ReportObjectKind.SubreportObject)
                    {
                        //you will need to typecast the reportobject to a subreport 
                        //object once you find it
                        crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;


                        //open the subreport object
                        crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);


                        //set the database and tables objects to work with the subreport
                        crDatabase = crSubreportDocument.Database;
                        crTables = crDatabase.Tables;


                        //loop through all the tables in the subreport and 
                        //set up the connection info and apply it to the tables
                        foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
                        {
                            crConnectioninfo.ServerName = "Oracle DSN";
                            crConnectioninfo.UserID = "user id";
                            crConnectioninfo.Password = "pass";
                            crConnectioninfo.DatabaseName = " I kept this empty";


                            crTableLogOnInfo = crTable.LogOnInfo;
                            crTableLogOnInfo.ConnectionInfo = crConnectioninfo;
                            crTable.ApplyLogOnInfo(crTableLogOnInfo);
                        }
                    }
                }
            }


            //bool myTEst = rptClientDoc.DatabaseController.VerifyTableConnectivity("Orders");


            GroupPath gp = new GroupPath();
            string tmp = String.Empty;
            try
            {
                rptClientDoc.RowsetController.GetSQLStatement(gp, out tmp);
                output.Text = tmp;
            }
            catch (Exception ex)
            {
                output.Text = "ERROR: " + ex.Message;
                return;
            }
        }