Failed to open the connection. Details: [Database Vendor Code: 12154 ]

[Former Member]

Dear All,

We have created almost 15 crystal reports designed in Delphi XE4 application which points to different schema.

We revamped the same application Visual Studio 2013 application. Everything is converted and working fine in the application

except Crystal reports. We are now using different schema in our Visual studio application.

I tried to connect using the same rpt file with different schema it is showing error like.

Database logon failed.  Failed to open the connection.

So how to resolve the issue without pointing the new instance.

We are working for logistics company so we will get so many instances. so we don't want to map the Data source, user Id and password.

Is there anyway to resolve the issue. While i'm pointing to the schema then it works.

How to get the connection string from Web.config file.

  private void ViewCystalReport(bool exportall, string crystalid, string crystalname)
    {
        try
        {
            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
            Tables CrTables;
            DataSet Ds = new DataSet();
            string dbDataSource = builder.DataSource;
            string userId = builder.UserID;
            string pass = builder.Password;
            ReportDocument cryRptdocUMENT = new ReportDocument();
            string sRptFolder = Server.MapPath("~/CrystalReports");
            string ReportName = crystalid;
            string path = sRptFolder + @"\" + @ReportName + ".rpt";
            cryRptdocUMENT.Load(path);


            ConnectionInfo crConnectionInfo = new ConnectionInfo();
            crConnectionInfo.ServerName = builder.DataSource;
            crConnectionInfo.DatabaseName = string.Empty;
            crConnectionInfo.UserID = builder.UserID;
            crConnectionInfo.Password = builder.Password;


            crConnectionInfo.IntegratedSecurity = false;
            crConnectionInfo.Type = ConnectionInfoType.CRQE;
            CrTables = cryRptdocUMENT.Database.Tables;
            cryRptdocUMENT.SetDatabaseLogon(crConnectionInfo.UserID, crConnectionInfo.Password, crConnectionInfo.ServerName, string.Empty);

            //Getting Value from the Array       
            Database database = cryRptdocUMENT.Database;
            Tables tables = database.Tables;


            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
            {
                TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
                tableLogOnInfo.ConnectionInfo = crConnectionInfo;
                table.ApplyLogOnInfo(tableLogOnInfo);             
            }



            foreach (InternalConnectionInfo internalConnectionInfo in cryRptdocUMENT.DataSourceConnections)
            {
                // Must set the UseDSNProperties flag to True before setting the database connection
                if (internalConnectionInfo.LogonProperties.ContainsKey("UseDSNProperties"))
                {
                    internalConnectionInfo.LogonProperties.Set("UseDSNProperties", true);
                }
                // Sets the database connection for all objects in the report (ie. main report, tables, sub reports)
                internalConnectionInfo.SetConnection(crConnectionInfo.ServerName, crConnectionInfo.DatabaseName, crConnectionInfo.UserID, crConnectionInfo.Password);               
            }           
            if (ParameterArrayList.Count != 0)
            {
                //Getting Value From the Report (Parameter and Formula Feild)               
                int ParameterFieldCount = cryRptdocUMENT.DataDefinition.ParameterFields.Count;
                for (int i = 0; i < ParameterFieldCount; i++)
                {
                    string paramname = cryRptdocUMENT.DataDefinition.ParameterFields[i].Name;
                    switch (paramname.ToUpper())
                    {
                        case "TRADINGTYPE":
                            cryRptdocUMENT.SetParameterValue(paramname, "T");
                            break;
                        case "HFT":
                            cryRptdocUMENT.SetParameterValue(paramname, Convert.ToBoolean(ParameterArrayList["HFT"]) == true ? "T" : "");
                            break;
                        case "AFS":
                            cryRptdocUMENT.SetParameterValue(paramname, Convert.ToBoolean(ParameterArrayList["AFS"]) == true ? "A" : "");
                            break;
                        case "HTM":
                            cryRptdocUMENT.SetParameterValue(paramname, Convert.ToBoolean(ParameterArrayList["HTM"]) == true ? "H" : "");
                            break;
                        case "M_HFT":
                            cryRptdocUMENT.SetParameterValue(paramname, Convert.ToBoolean(ParameterArrayList["HFT"]) == true ? "T" : "");
                            break;
                        case "M_AFS":
                            cryRptdocUMENT.SetParameterValue(paramname, Convert.ToBoolean(ParameterArrayList["AFS"]) == true ? "A" : "");
                            break;
                        case "M_HTM":
                            cryRptdocUMENT.SetParameterValue(paramname, Convert.ToBoolean(ParameterArrayList["HTM"]) == true ? "H" : "");
                            break;                       
                        default:
                            if (!paramname.Contains("Pm-?"))
                            {
                                if (paramname == "ActualBreach" || paramname == "HistoricalAvgVaR")
                                    break;
                                var pValue = ParameterArrayList.FirstOrDefault(x => x.Key.ToUpper() == paramname.ToUpper());
                                cryRptdocUMENT.SetParameterValue(paramname, pValue.Value);
                            }
                            break;
                    }
                }
            }
            //Set generated report document as Crystal Report viewer report source           
            crystalreport1.ReportSource = cryRptdocUMENT;
            crystalreport1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
            crystalreport1.DataBind();
            //crystalreport1.EnableParameterPrompt = false;
        }
        catch (Exception e)
        {
        }
    }

Here some of the parameter are not mandatory but it is listed all the parameter from the rpt file.

Kindly send me the quick reply to resolve the issue.

Thank you for your valuable time and help,

Deepan

[DellSC · Accepted solution]

When you see a "Database Vendor Code" in an error message, that is the error message that came through from the database, which is not a specific Crystal error.

When I google "Oracle Error 12154", I found this page:  ORA-12154: TNS tips.  This talks about the cause of this error and what you can do to fix it.

-Dell

[Former Member · Accepted solution]

Hi Christy ,

Thank you for your reply,

I'm getting two Issues to share:

• Dynamically set Datasource and Schema
• Parameter's count are increased if we are using Sub Reports

               It is asking for 71 parameters but there actually 11 mandatory parameters are there in the report.

I have attached the screenshot for all the issues.

I have verified the connection it is working in the application.

The issue is i want to point Datasource and Schema dynamically to Crystal Report from Web.config file. We are having three different Datasource and schema and almost 95 reports.

I don't want to set the datasource for all the 95 report if i didn't set Datasource Location then it is displaying error like below,

Once i set Datasource Location then i'm getting the result from the Database is there any possible way to accomplish this.

While Verifying the parameter there are only 12 mandatory parameter as like below in the image but it is displaying all the

Kindly send me the solution and save my time.

[DellSC · Accepted solution]

What type of connection are you using in the reports - native Oracle or ODBC?  I MUCH prefer native Oracle as it is faster and more reliable.  If that's what you're using, you need to have each of the three aliases configured in the tnsnames.ora file.  If you're using ODBC, DO NOT use the Microsoft Oracle ODBC connector - you must install and use Oracle's ODBC connector.  Also, there are issues with the Oracle 12c client and Crystal - you'll need to downgrade to the Oracle 11g client (which will still connect to a 12C database...).  If your web application is 32-bit, you'll need just the 32-bit client.  If it's 64-bit, you'll need the 32-bit client on the developer workstation for designing reports and the 64-bit client on the web server for running them.

When you're processing the parameters, you can ignore any that have "pm-" at the front of the parameter name - those are the subreport params that will get values from the report.

-Dell