How to export crystal report to pdf using oracle

Question

I get an InvalidComObjectException error when trying to export to pdf. I think it might have something to do with connecting to oracle, but not sure. The SetDatabaseLogon seem to favor Sql Server. The report calls a stored proc for the data, and is working using the Business Objects XIR2 service. I can view the report and connect inside the designer also.

'CrystalDecisions.CrystalReports.Engine 13.0.2000.0 'Crystal Reports for .NET Framework 4.0

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportAppServer.DataDefModel
Imports CrystalDecisions.Shared
Imports CrystalDecisions.ReportSource

   Public Overloads Function CreateReport(ByVal reportFileName As String, ByVal reportTitle As String, ByVal outputDirectory As String, _
ByVal isFullPath As Boolean, ByVal outputFormat As OutputFormat, ByVal crystalParameter As CrystalParameter) As String


      Dim baseReportsSourcePath As String = "C:\ADSTAX\SUITES\RFL\Letters\"
      Dim baseReportsOutputPath As String = "C:\PDF_Exports\"

      Dim fullFilepath As String = baseReportsSourcePath + reportFileName

      'check report exists
      If File.Exists(fullFilepath) = False Then Throw New FileNotFoundException("File:" + fullFilepath + " does not exist", fullFilepath)

      Dim doc As ReportDocument = New ReportDocument()
      doc.Load(fullFilepath)

      'set parameters
      If crystalParameter.Name = CrystalParameterName.DistributionKey Then

         doc.SetParameterValue("DISTRIBUTIONKEY", crystalParameter.Value)

      ElseIf crystalParameter.Name = CrystalParameterName.RequestKey Then

         doc.SetParameterValue("REQUESTKEY", crystalParameter.Value)

      End If

      'user, pass, server, database
      doc.SetDatabaseLogon("user", "pass")


      'build full output filename
      Dim exportFileName As String = Path.GetFileNameWithoutExtension(reportFileName)
      exportFileName += Guid.NewGuid.ToString + ".pdf"
      Dim fullOuputFilePath As String = baseReportsOutputPath + exportFileName

      'export to pdf
      doc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, fullOuputFilePath)

      'check report created
      If File.Exists(fullFilepath) = False Then
         Return String.Empty
      End If

      Return fullOuputFilePath

   End Function

Error Below:

System.Runtime.InteropServices.InvalidComObjectException was unhandled
  Message=COM object that has been separated from its underlying RCW cannot be used.
  Source=mscorlib
  StackTrace:
       at System.StubHelpers.StubHelpers.StubRegisterRCW(Object pThis, IntPtr pThread)
       at System.Runtime.InteropServices.ComTypes.IConnectionPoint.Unadvise(Int32 dwCookie)
       at CrystalDecisions.ReportAppServer.ISCDClientDocumentEvents_EventProvider.RemoveOnClosed(_ISCDClientDocumentEvents_OnClosedEventHandler handler)
       at CrystalDecisions.ReportAppServer.ISCDClientDocumentEvents_EventProvider.remove_OnClosed(_ISCDClientDocumentEvents_OnClosedEventHandler value)
       at CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.DisconnectEventRelay()
       at CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.InternalClose(Boolean bSetupForNextReport, Boolean bAutoClose)
       at CrystalDecisions.ReportAppServer.ReportClientDocumentWrapper.Dispose(Boolean bDisposeManaged)
       at System.ComponentModel.Component.Dispose()
       at CrystalDecisions.CrystalReports.Engine.ReportDocument.ClearCache(Boolean clearDocument)
       at CrystalDecisions.CrystalReports.Engine.ReportDocument.InternalClose(Boolean bSetupForNextReport)
       at CrystalDecisions.CrystalReports.Engine.ReportDocument.Close()
       at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExitHandler(Object sender, EventArgs e)
  InnerException: 

Answer 1

After many crystal issues this is what ends up working. Credit goes out to many others. I mostly discovered what was needed and pieced together other peoples work to find a solution.

you also need to add a reference to: CrystalDecisions.ReportAppServer.DataDefModel.dll

Imports CrystalDecisions.ReportAppServer.DataDefModel

   Public Overloads Function CreateReport(ByVal reportFileName As String, ByVal reportTitle As String, ByVal outputDirectory As String, _
ByVal isFullPath As Boolean, ByVal outputFormat As OutputFormat, ByVal crystalParameter As CrystalParameter) As String


      Dim baseReportsSourcePath As String = "C:\data\ReportTemplates\Correspondence\" '"C:\ADSTAX\SUITES\RFL\Letters\"
      Dim baseReportsOutputPath As String = "C:\PDF_Exports\"

      Dim fullFilepath As String = baseReportsSourcePath + reportFileName

      'check report exists
      If File.Exists(fullFilepath) = False Then Throw New FileNotFoundException("File:" + fullFilepath + " does not exist", fullFilepath)

      'build crystal
      Dim startCreateDoc As Long = DateTime.Now.Ticks
      Dim doc As ReportDocument = New ReportDocument()

      Dim tsc As New TimeSpan(startCreateDoc - DateTime.Now.Ticks)
      Trace.WriteLine(fullFilepath + " Report create time:" + tsc.ToString)

      Dim startLoad As Long = DateTime.Now.Ticks
      doc.Load(fullFilepath)

      Dim ts As New TimeSpan(startLoad - DateTime.Now.Ticks)
      Trace.WriteLine(fullFilepath + " Report Load time:" + ts.ToString)


      CrystalLogin(doc, "service", "user", "password")

      'set parameters
      If crystalParameter.Name = CrystalParameterName.DistributionKey Then
         doc.ApplyParameters("DISTRIBUTIONKEY=" + crystalParameter.Value)
      ElseIf crystalParameter.Name = CrystalParameterName.RequestKey Then
         doc.ApplyParameters("REQUESTKEY=" + crystalParameter.Value)
      End If

      'build full output filename
      Dim exportFileName As String = Path.GetFileNameWithoutExtension(reportFileName)
      exportFileName += "_"
      exportFileName += Guid.NewGuid.ToString + ".pdf"
      Dim fullOuputFilePath As String = baseReportsOutputPath + exportFileName

      'export to pdf
      'doc.ExportToDisk(ExportFormatType.PortableDocFormat, fullOuputFilePath)
      Dim pdfOps As CrystalDecisions.Shared.PdfFormatOptions = CrystalDecisions.Shared.ExportOptions.CreatePdfFormatOptions

      Dim eo As New CrystalDecisions.Shared.ExportOptions
      eo.ExportFormatType = ExportFormatType.PortableDocFormat
      eo.ExportDestinationType = ExportDestinationType.DiskFile
      eo.ExportFormatOptions = pdfOps

      'Dim htmlOps As CrystalDecisions.Shared.HTMLFormatOptions = CrystalDecisions.Shared.ExportOptions.CreateHTMLFormatOptions
      'htmlOps.HTMLFileName = fullOuputFilePath
      'eo.ExportFormatOptions = htmlOps

      Dim dop As DiskFileDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions
      dop.DiskFileName = fullOuputFilePath

      eo.ExportDestinationOptions = dop

      doc.Export(eo)
      doc.Close()

      'check report created
      If File.Exists(fullFilepath) = False Then
         Return String.Empty
      End If

      Return fullOuputFilePath

   End Function

   Public Shared Sub CrystalLogin(mainInRD As ReportDocument, dataSource As String, userId As String, pwd As String)
      Try
         'now update logon info for all sub-reports
         If Not mainInRD.IsSubreport AndAlso mainInRD.Subreports IsNot Nothing AndAlso mainInRD.Subreports.Count > 0 Then
            For Each rd As ReportDocument In mainInRD.Subreports
               CrystalLogin(rd, dataSource, userId, pwd)
            Next
         End If
      Catch
      End Try
      'do the main reports database
      Dim logonInfo As TableLogOnInfo = Nothing
      For Each table As CrystalDecisions.CrystalReports.Engine.Table In mainInRD.Database.Tables
         logonInfo = table.LogOnInfo
         logonInfo.ConnectionInfo.ServerName = dataSource
         logonInfo.ConnectionInfo.DatabaseName = ""
         logonInfo.ConnectionInfo.UserID = userId
         logonInfo.ConnectionInfo.Password = pwd
         table.ApplyLogOnInfo(logonInfo)

   'This part was needed to support the oracle store procs we use to obtain the data
         Dim prop As PropertyInfo = Nothing
         prop = table.[GetType]().GetProperty("RasTable", BindingFlags.NonPublic Or BindingFlags.Instance)
         Dim rasTable As ISCRTable = Nothing
         rasTable = DirectCast(prop.GetValue(table, Nothing), ISCRTable)
         table.Location = rasTable.QualifiedName
         'Console.Out.WriteLine(table.Name)
         'Console.Out.WriteLine(rasTable.QualifiedName)
      Next

   End Sub

Answer 2

I would recommend looking at this code:

Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
        Dim cryRpt As New ReportDocument
        Dim crtableLogoninfos As New TableLogOnInfos
        Dim crtableLogoninfo As New TableLogOnInfo
        Dim crConnectionInfo As New ConnectionInfo
        Dim CrTables As Tables
        Dim CrTable As Table

        cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt")

        With crConnectionInfo
            .ServerName = "YOUR SERVER NAME"
            .DatabaseName = "YOUR DATABASE NAME"
            .UserID = "YOUR DATABASE USERNAME"
            .Password = "YOUR DATABASE PASSWORD"
        End With

        CrTables = cryRpt.Database.Tables
        For Each CrTable In CrTables
            crtableLogoninfo = CrTable.LogOnInfo
            crtableLogoninfo.ConnectionInfo = crConnectionInfo
            CrTable.ApplyLogOnInfo(crtableLogoninfo)
        Next

        CrystalReportViewer1.ReportSource = cryRpt
        CrystalReportViewer1.Refresh()
    End Sub
End Class

From http://vb.net-informations.com/crystal-report/vb.net_crystal_report_load_dynamically.htm

I would probably put a breakpoint in where the asterisk is:

        For Each CrTable In CrTables
            *crtableLogoninfo = CrTable.LogOnInfo
            crtableLogoninfo.ConnectionInfo = crConnectionInfo
            CrTable.ApplyLogOnInfo(crtableLogoninfo)
        Next

Then analyse the existing CrTable.TableLogonInfo

Hopefully you can then use this going forward.

Answer 3

ESchneider,

I suspect the issue not due to your setting of connection details or parameters. To confirm my suspicions you could place your code into a simple web or windows form/page and instead of exporting just try to view the report by dropping a CrystalReportViewer object onto that page. (Note: There are some prerequisites to this working but if you are on a development machine that had the full Crystal installed it should easily meet those requirements.)

Assuming that you don't see complaints (i.e. it prompts for a parameter or database credentials) then I would lean towards an entirely different theory.

The clue is in your error message: "COM object that has been separated from its underlying RCW cannot be used." This error is not unique to Crystal Reports.

Where I've seen this before is related to either garbage collection (be it automated or due to dispose/deconstruction occuring due to your code) OR multi-threading related challenges.

I noticed you are calling this as a function that returns the PDF path as a string. Perhaps you've nested the call into a thread that is independent of the calling thread? Perhaps you have other code that triggers other events that could play into object disposal or resource/lock related issues like batching?

One way to avoid speculating is to place all this into a brand new, dead simple, basic windows application. Do nothing but the absolute minimum to trigger the export. Make sure that the Windows application is just writing to a local folder and reading the report from a local directory. We wouldn't want file permissions or network issues (reading/writing over a network) to interfere.

Best of luck.

Answer 4

1. Execute a Dataset to fill data from the oracle storedprocedure. Make sure The table names in the dataset are same as the name of the database object based on which the report was designed.(Stored Procedure name in this case)

2. Do not provide any logoninfo. This is important.

3. Set all report parameters but dont provide any data filters as the dataset contains already filtered data.

4. do the data binding manually.

   doc.SetDataSource=yourdatasource;

now proceed with report export as usual.

This is Push Model way of populating report. Generally i use oledb dataadapter for oracle for designing reports. But if you are dealing with stored procedures then Push model is the only way. The designer can access the oracle SP and populate the report but In the application you have to take care of it otherwise you will be presented with annoying popup asking the Stored Procedure Parameter value.

Here is some code

OracleConnection cn = new OracleConnection("Data Source=yourdbname;User ID=someid;password=somepw;Pooling=true;Connection Lifetime=30;Min Pool Size=5;Max Pool Size=100");

OracleParameter DETAILS = new OracleParameter();
DETAILS.ParameterName = "DETAILS";
DETAILS.Direction = ParameterDirection.Output;

OracleParameter NN = new OracleParameter();
NN.ParameterName = "PRODUCT";
NN.Direction = ParameterDirection.Input;
NN.Value = 1000; // Some product id

OracleParameter DD = new OracleParameter();
DD.ParameterName = "TRDATE";
DD.Direction = ParameterDirection.Input;
DD.Value = “09-DEC-2008”; // Some Date


// for Oracle.DataAccess.Client use the following
DETAILS.OracleDbType = OracleDbType.RefCursor;
NN.OracleDbType = OracleDbType.Varchar2;
DD.OracleDbType = OracleDbType.Date;


// for System.Data.OracleClient use the following
//DETAILS.OracleType = OracleType.Cursor;
//NN.OracleType = OracleType.VarChar;
//DD.OracleType = OracleType.DateTime;



OracleCommand cmd = new OracleCommand("Myschemaname.GETSTOCK", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(NN);
cmd.Parameters.Add(DD);
cmd.Parameters.Add(DETAILS);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds,"GETSTOCK"); //Name must be same as Procedure
ReportDocument rptDoc = new ReportDocument();
rptDoc.Load(Server.MapPath("FINC//abc.rpt"));
rptDoc.SetDataSource(ds);
CRT.ReportSource = rptDoc; // CRT is the name of Crystal report viewer control
    // your export routine goes here