Error occurs when using RPT with Oracle Server data source

[Former Member]

Hi,

Our application is using RPT files witch have data source of "Oracle Server".

When we run the application and preview the report, an error occurs that reads "failed to load database information".

(If we use an RPT file with its data source defined as "ADO.NET(XML)" or "ODBC(RDO)"  instead of "Oracle Server",the application works well) 

Does anyone know the way to solve the problem?

Environment info on the PC where our application is running

  1. Windows 8, 8.1 x86/x64

  2. .NET Famework4.0/4.5

  3. Crystal Reports .NET runtime SP12

  4. no Oracle Client

The problem can be reproduced by running the following codes.

---------------

ReportDocument rd = new ReportDocument();

rd.Load(<RPT file with Oracle Server as its data source>, CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault);

System.Data.DataSet ds = new System.Data.DataSet();

ds.ReadXml(<xml file with schema info and data in it>);

rd.SetDataSource(ds);

crystalReportViewer1.ReportSource = rd;

crystalReportViewer1.Show();

---------------

Thanks in advance.

--Yuyang

[former_member11696 · Accepted solution]

Hello,

How are you filling the DS if no Oracle Client is installed?

Try a new report using the XML file and then compare the original report to the new report data structure.

It's likely they do not match which is causing the error.

If CR cannot map the fields exactly then it deletes those fields it can't match and can cause all sorts of errors.

Don

[Former Member · Accepted solution]

Hi Don

Thank you for quick replay.

3 files are attached here.

(Notice: the extension of the zip file has been changed from "zip" to "txt". so before unzip the file, please change its extension to "zip")

one is the xml data file named "untpr01.xml" which is used as data file for the 2 RPTs during runtime.

and the another two are RPT files named as "untpr01-ora.rpt" and "untpr01-xml.rpt",which have Oracle Server data source and ADO.NET(XML) data source respectively.

Using the following codes, the problem can be reproduced.

ReportDocument rd = new ReportDocument();

rd.Load("untpr01-xml.rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault);

  //if using "untpr01-ora.rpt", the error occurs.

System.Data.DataSet ds = new System.Data.DataSet();

ds.ReadXml("untpr01.xml");

rd.SetDataSource(ds);

crystalReportViewer1.ReportSource = rd;

crystalReportViewer1.Show();

by the way, the table info in both RPTs are the same.

Thanks in advance.

UNTPR01.txt.zip

[former_member11696 · Accepted solution]

Hi Yuyang,

Thank you for the report and XML.

Here's the code to set for XML, no need to load it into a dataset:

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;

CrystalDecisions.Shared.TableLogOnInfo tLogonInfo;

btnSQLStatement.Text = "";

try
{
    foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in rpt.Database.Tables)
    {
        tLogonInfo = rptTable.LogOnInfo;
        tLogonInfo.ConnectionInfo.ServerName = btrDataFile.Text; // D:\Atest\Yuyang Wang
        tLogonInfo.ConnectionInfo.DatabaseName = btrSearchPath.Text; //  UNTPR01.xml
        tLogonInfo.ConnectionInfo.UserID = "";
        tLogonInfo.ConnectionInfo.Password = "";
        tLogonInfo.TableName = rptTable.Name;

        dtStart = DateTime.Now;

        try
        {
            rptTable.ApplyLogOnInfo(tLogonInfo);
            rptTable.Location = btrDataFile.Text + "\\" + btrSearchPath.Text;
        }
        catch (Exception ex)
        {
            MessageBox.Show("ERROR: " + ex.Message);
            //return;
        }

        difference = DateTime.Now.Subtract(dtStart);

        btnSQLStatement.Text += /*rptTable.Name.ToString() +*/ " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";

    }
}
catch (Exception ex)
{
    MessageBox.Show("ERROR: " + ex.Message);
    //return;
}

It works for me...

For the Oracle report you must use RAS and the ReplaceConnection API to update the report source. Search this forum and KBA for sample code on how to.

If it doesn't work it's because the Tables and fields definition differences between XML and Oracle will stop this from working. You'll have to test to find field types defined in Oracle and match XML field types.

Thanks again

Don

[Former Member · Accepted solution]

Hi Don

Thank you again for your reply and code.

From the screen shot attached, it seems to me that it works for you in the case when you are using the RPT file of ADO.NET data source (that is to say, the RPT file "UNTPR01-XML.RPT").

What I really want to know is how to do this for the RPT file of OracleServer data source,( "UNTPR01-ORA.RPT").

In this case, could you show me the way to change the database connection at runtime for using XML data (or simply using a DataSet with XML data inside)?

Thanks indeed.

Yuyang

[former_member11696 · Accepted solution]

You never read or searched for ReplaceConnection

Here's the code:

// Access

rptClientDoc = rpt.ReportClientDocument;

//Create a new Database Table to replace the reports current table.

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

CrystalDecisions.ReportAppServer.DataDefModel.Table subboTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Get the Database Tables Collection for your report

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

boTables = rptClientDoc.DatabaseController.Database.Tables;

// Get the old connection info

oldConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);

boTable.ConnectionInfo = boConnectionInfo;

oldConnInfo = oldConnInfos[0];

// RAS XML - need to use ReplaceConnection

#region RASXMLReplaceconnection;

if (chkUseRAS.CheckState == CheckState.Checked)

{

    rptClientDoc = rpt.ReportClientDocument;

    //Get the Database Tables Collection for your report

    boTables = rptClientDoc.DatabaseController.Database.Tables;

    // Get the old connection info

    oldConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);

    boTable.ConnectionInfo = boConnectionInfo;

    oldConnInfo = oldConnInfos[0];

        PropertyBag logonDetails = new PropertyBag();

        PropertyBag QeDetails = new PropertyBag();

        //boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object

        PropertyBag boMainPropertyBag = new PropertyBag();

        //boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

        //In the main property bag (boMainPropertyBag)

        PropertyBag boInnerPropertyBag = new PropertyBag();

        //Set the attributes for the boInnerPropertyBag

        boInnerPropertyBag.Add("File Path ", btrDataFile.Text + "\\" + btrSearchPath.Text);

        boInnerPropertyBag.Add("Server Path", btrSearchPath.Text);

        boInnerPropertyBag.Add("Database Name", btrSearchPath.Text);

        boInnerPropertyBag.Add("Database Type", "ADO.NET (XML)");

        //Set the attributes for the boMainPropertyBag

        boMainPropertyBag.Add("Database DLL", "crdb_adoplus.dll");

        boMainPropertyBag.Add("QE_DatabaseName", btrDataFile.Text);

        boMainPropertyBag.Add("QE_DatabaseType", "ADO.NET (XML)");

        //Add the QE_LogonProperties we set in the boInnerPropertyBag Object

        boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);

        boMainPropertyBag.Add("QE_ServerDescription", btrSearchPath.Text);

        boMainPropertyBag.Add("QE_SQLDB", "false");

        boMainPropertyBag.Add("SSO Enabled", "False");

        // ReplaceConnection method This works also

        for (int I = 0; I < oldConnInfos.Count; I++)

        {

            // CrystalDecisions.ReportAppServer.DataDefModel.Table tbl;

            for (int S = 0; S < boTables.Count; S++)

            {

                oldConnInfo = oldConnInfos[I];

                newConnInfo.Attributes = boMainPropertyBag;

                newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindDBFile;

                try

                {

                    rptClientDoc.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

                }

                catch (Exception ex)

                {

                    MessageBox.Show("ERROR: " + ex.Message); //  + " ;" + ex.InnerException.Message.ToString());

                }

            }

        // check for subreports

        //loop through all the report objects to find all the subreports

        foreach (string crSubreportDocument1 in rptClientDoc.SubreportController.GetSubreportNames())

        {

            SubreportClientDocument SubRCD = rptClientDoc.SubreportController.GetSubreport(crSubreportDocument1);

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newSubConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldSubConnInfo;

            CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldSubConnInfos;

            PropertyBag SublogonDetails = new PropertyBag();

            PropertyBag SubQeDetails = new PropertyBag();

            oldSubConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);

            btnReportObjects.Text += "\nSubreport: " + crSubreportDocument1.ToString() + "\n";

            for (int I = 0; I < oldSubConnInfos.Count; I++)

            {

                oldSubConnInfo = oldSubConnInfos[I];

                newSubConnInfo.Attributes = boMainPropertyBag;

                newSubConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindDBFile;

                // this works also

                for (int S = 0; S < boTables.Count; S++)

                {

                    oldSubConnInfo = oldSubConnInfos[I];

                    newSubConnInfo.Attributes = boMainPropertyBag;

                    newSubConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindDBFile;

                    try

                    {

                        rptClientDoc.DatabaseController.ReplaceConnection(oldSubConnInfo, newSubConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

                    }

                    catch (Exception ex)

                    {

                        MessageBox.Show("ERROR: " + ex.Message);

                    }

                }

            }

            IsRpt = false;

        }

    }

}

Have fun

Don

[Former Member · Accepted solution]

Hi Don

Sorry.

(Actually, I searched for a while but could not find anything useful...)

By using the utility "CodeBuilder-RasConnectionInfo" which I got from the site you show me, the problem was solved.

The utility create the code showing what is needed to change the database connection info for an RPT with specific data source.

Thank you very much indeed!!

yuyang