Cry.Reports 08,VB.NET on Oracle - Failed to retrieve data from the database

[Former Member]

CrystalDecisions.CrystalReports.Engine.ReportDocument

I have a Crystal Report (2008) that i launch from a VB.NET

I have problem when i try to change CrystalDecisions.Shared.ConnectionInfo .

when i'm using Oracle connection (the report worked fine on SQL).

Getting this error when launch the report.

Error message :

Crystal Report Windowes Forms Viewer:

{bold} Failed to retrieve data from the database{bold}

The report is developed using crystal version 2008.

The code i am using to run the Crystal Report (similar worked fine on SQL) is as follows:

CrystalDecisions.Shared.ConnectionInfo

Oracle Provider :

dbAttributes = New CrystalDecisions.Shared.DbConnectionAttributes

dbAttributes.Collection.Set(u201DServeru201D, u201COracleServiceNameu201C)

dbAttributes.Collection.Set(u201DTrusted_Connectionu201D, False)

u2019setup the connection

crConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo

crConnectionInfo.LogonProperties.Clear()

crConnectionInfo.Attributes.Collection.Clear()

crConnectionInfo.DatabaseName = u201Cu201D

crConnectionInfo.ServerName = u201COracleServiceNameu201C

crConnectionInfo.UserID = u201CReportsUserNameu201C

crConnectionInfo.Password = u201CReportsPasswordu201D

crConnectionInfo.Attributes.Collection.Set(u201DDatabase DLLu201D, u201Ccrdb_oracle.dllu201D)

crConnectionInfo.Attributes.Collection.Set(u201DQE_DatabaseNameu201D, u201Cu201D)

crConnectionInfo.Attributes.Collection.Set(u201DQE_DatabaseTypeu201D, u201COracle Serveru201D)

crConnectionInfo.Attributes.Collection.Set(u201DQE_LogonPropertiesu201D, dbAttributes)

crConnectionInfo.Attributes.Collection.Set(u201DQE_ServerDescriptionu201D, config.ServerName)

crConnectionInfo.Attributes.Collection.Set(u201DQE_SQLDBu201D, True)

crConnectionInfo.Attributes.Collection.Set(u201DSSO Enabledu201D, False)

crConnectionInfo.LogonProperties = dbAttributes.Collection

Is better use :

(Dataset DLL, "crdb_ado.dll"), ¿?

("Provider", "MSDAORA"), ¿?

("QE_DatabaseType", "OLE DB (ADO)"))¿?

Regards

Ismael

[former_member11696 · Accepted solution]

It depends on which database driver you used to create the report. Crdb_oracle.dll is the native Oracle DB driver, crdb_ado.dll is the OLE DB driver, crdb_odbc.dll is the ODBC driver.

Are you changing the database connection/driver at run time or was this report created using Oracle as ther report data source?

Try creating a new report off the Oracle Server and test again. This removes any update/changes made from the original report. When refreshed it should prompt you for log on info also which you can use to verify if the logon info is correct.

[Former Member]

What you are trying to do, is achievable through the RAS SDK.

Please find below a snippet code in VB.net using the unmanaged RAS:

Public Sub CreateReport()
        ' create an instance or Report Client Document
        m_crReportDocument = New ReportClientDocument()

        ' set RAS server location
        m_crReportDocument.ReportAppServer = "127.0.0.1"

        ' open a report
        ' copy this report to "Report Directory" in RAS Configuration Manager
        m_crReportDocument.Open(Server.MapPath("ChangeTableLocation.rpt"))
    End Sub

    Public Sub ChangeTableLocation()

        ' Obtain reference to Table
        m_crOldTable = m_crReportDocument.DatabaseController.Database.Tables.Item(0)

        ' Create copy to manipulate
        m_crNewTable = m_crOldTable.Clone(True)

        ' Change properties of the New Table

        ' Point to the new fully qualified table location

        m_crNewTable.QualifiedName = "Northwind.dbo.Orders"

        ' Now we need to change to connection info - so get a reference to the connection info
        m_crConnInfo = m_crNewTable.ConnectionInfo

        ' set user id and password
        m_crConnInfo.UserName = "vantech"
        m_crConnInfo.Password = "vantech"


        ' get database connection attributes
        m_crAttributes = m_crConnInfo.Attributes

        ' check if connection info is using Crystal Reports Query Engine
        If (m_crConnInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE) Then
            ' get logon info properties
            m_crLogonInfo = m_crAttributes.Item("QE_LogonProperties")

            ' set server and database name
            m_crLogonInfo.Item("Data Source") = "dbconn1"
            m_crLogonInfo.Item("Initial Catalog") = "Northwind"
        End If



        ' Obtain reference to DB controller
        m_crDbController = m_crReportDocument.DatabaseController

        ' Now Pass the Changed info back in
        m_crDbController.SetTableLocationEx(m_crOldTable, m_crNewTable)

    End Sub

For more information about the RAS sdk for your Crystal Report version (2008) refer to this link:

https://boc.sdn.sap.com/node/10969

Also if you want to get the logon properties embedded in the report you can try run this C# code against the report in question

private System.Windows.Forms.TextBox textBox_Filename;
		private System.Windows.Forms.Button button_OpenReport;
		private System.Windows.Forms.OpenFileDialog openFileDialog;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.Button button_ShowProperties;
		private System.Windows.Forms.ListBox listBox_Properties;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.textBox_Filename = new System.Windows.Forms.TextBox();
			this.button_OpenReport = new System.Windows.Forms.Button();
			this.openFileDialog = new System.Windows.Forms.OpenFileDialog();
			this.label1 = new System.Windows.Forms.Label();
			this.listBox_Properties = new System.Windows.Forms.ListBox();
			this.button_ShowProperties = new System.Windows.Forms.Button();
			this.SuspendLayout();
			// 
			// textBox_Filename
			// 
			this.textBox_Filename.Location = new System.Drawing.Point(8, 24);
			this.textBox_Filename.Name = "textBox_Filename";
			this.textBox_Filename.Size = new System.Drawing.Size(272, 20);
			this.textBox_Filename.TabIndex = 0;
			this.textBox_Filename.Text = "";
			// 
			// button_OpenReport
			// 
			this.button_OpenReport.Location = new System.Drawing.Point(288, 24);
			this.button_OpenReport.Name = "button_OpenReport";
			this.button_OpenReport.Size = new System.Drawing.Size(24, 23);
			this.button_OpenReport.TabIndex = 1;
			this.button_OpenReport.Text = "...";
			this.button_OpenReport.Click += new System.EventHandler(this.button_OpenReport_Click);
			// 
			// openFileDialog
			// 
			this.openFileDialog.FileOk += new System.ComponentModel.CancelEventHandler(this.openFileDialog_FileOk);
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(8, 8);
			this.label1.Name = "label1";
			this.label1.TabIndex = 2;
			this.label1.Text = "Select report ...";
			// 
			// listBox_Properties
			// 
			this.listBox_Properties.Dock = System.Windows.Forms.DockStyle.Bottom;
			this.listBox_Properties.HorizontalScrollbar = true;
			this.listBox_Properties.Location = new System.Drawing.Point(0, 86);
			this.listBox_Properties.Name = "listBox_Properties";
			this.listBox_Properties.SelectionMode = System.Windows.Forms.SelectionMode.MultiSimple;
			this.listBox_Properties.Size = new System.Drawing.Size(328, 303);
			this.listBox_Properties.TabIndex = 3;
			// 
			// button_ShowProperties
			// 
			this.button_ShowProperties.Location = new System.Drawing.Point(8, 56);
			this.button_ShowProperties.Name = "button_ShowProperties";
			this.button_ShowProperties.Size = new System.Drawing.Size(272, 23);
			this.button_ShowProperties.TabIndex = 4;
			this.button_ShowProperties.Text = "Show properties";
			this.button_ShowProperties.Click += new System.EventHandler(this.button_ShowProperties_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(328, 389);
			this.Controls.Add(this.button_ShowProperties);
			this.Controls.Add(this.listBox_Properties);
			this.Controls.Add(this.textBox_Filename);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.button_OpenReport);
			this.Name = "Form1";
			this.Text = "Display report logon properties";
			this.Load += new System.EventHandler(this.Form1_Load);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		private void Form1_Load(object sender, System.EventArgs e)
		{
			// configure the openfiledialog
			openFileDialog.Filter = "Crystal Reports (*.rpt)|*.rpt";

		}

		private void button_OpenReport_Click(object sender, System.EventArgs e)
		{
			openFileDialog.ShowDialog(this);
		}

		private void openFileDialog_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
		{
			textBox_Filename.Text = openFileDialog.FileName;
		}

		private void button_ShowProperties_Click(object sender, System.EventArgs e)
		{
			CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
			CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;
			CrystalDecisions.ReportAppServer.DataDefModel.Database boDatabase;
			CrystalDecisions.ReportAppServer.DataDefModel.Table boTable; 

			string listItem1, listItem2;

			CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boAttributesPB;
			
		
			// clear the listbox
			listBox_Properties.Items.Clear();

			boReportDocument.Load(textBox_Filename.Text);
			boReportClientDocument = boReportDocument.ReportClientDocument;

			boDatabase = boReportClientDocument.Database;

			// This sample will only inspect the first table
			boTable = (CrystalDecisions.ReportAppServer.DataDefModel.Table) boDatabase.Tables[0];
			boAttributesPB = boTable.ConnectionInfo.Attributes;			
			foreach(string property in boAttributesPB.PropertyIDs)
			{
				// The QE_LogonProperties contain another property bag. This section
				// inspects those values
				if(property == "QE_LogonProperties")
				{
					listItem1 = property + ":";
					listBox_Properties.Items.Add(listItem1);

					// Declare a new PropertyBag for the LogonProperties
					CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boLogonPB = (CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag) boAttributesPB[property];

					// Search through these properties and add them to the list
					foreach(string logonProperty in boLogonPB.PropertyIDs)
					{
						// If you attempt to read a password value an error will be thrown
						// Don't read the password.
						if (logonProperty == "Password")
							listItem2 = "	" + logonProperty + ":";
						else
							listItem2 = "	" + logonProperty + ":	" + boLogonPB[logonProperty];

						listBox_Properties.Items.Add(listItem2);
					}
				}
				else
				{
					listItem1 = property + ":	" + boAttributesPB[property];
					listBox_Properties.Items.Add(listItem1);
				}				
			}


			boReportDocument.Close();
			boReportDocument.Dispose();


			
		}

NB: with Oracle the DatabaseName is set to null

Cheers

Alphonse

[Former Member]

The error is probably due to some small piece of information that is missing or incorrect in the connection information. However, it also looks like you might be doing more than is necessary.

Let's start with what you want to accomplish. Are you changing from one Oracle database to another, or from another database type to Oracle? Are you trying to change the connection type (native, OLEDB, etc) at runtime?

Is the service properly configured? Ensure the destination Oracle database has the proper entry in the TNSNAMES.ora file on the same machine as the application. If you can't access that database from your SDK machine using the Oracle client tools, the application won't be able to, either.

Have you tried setting the basic connection information without bothering with the Attributes collection? That is, just set the server, database, userid and password information on the ConnectionInfo object and then call ApplyLogOnInfo.

The driver used will depend on your connection type. If you're making a native Oracle connection, crdb_oracle.dll will be used. If you use OLEDB, crdb_ado.dll will be used. Which one is "better" depends on your situation.

[Former Member]

I trying to change the conecction provider SQL to Oracle.

I don't need to change the connection type ?¿?

I haven`t problem when i launch the report from Crystal Reports designer.

It's no the solution.

I tried setting the basic connection information without bothering with the Attributes collection.KO

What attributes and LogonProperties i need it? (basic attribute,logoproperties)

I think that, i need documentaion.

Where can i find documentation about = CrystalDecisions.Shared.ConnectionInfo?

I can't find...

Sorry, but my English is not so good.

Regards

Ismael

Edited by: Ismael Ramirez Rodriguez on Sep 29, 2008 9:49 PM