cancel
Showing results for 
Search instead for 
Did you mean: 

Re: Database logon failed when exporting Crystal Report to PDF

Former Member
0 Kudos
1,078

I've been chasing a CR error for a while and finally FOUND the answer and I'd thought I'd relay the information to you.

A windows application built and deployed using VB in Visual Studio 2010, .net framework 4.

originally using CR for VS 13_0_1.  I was getting intermittent "null reference" exceptions on the EXPORT or getting the infamous "database login" error too ( on export).

I inherited the application and the CR reports that were developed for it but eventually I found that despite using the same exact method to load, set the database connection, set the export options and set the report parameters the cause of the Exceptions was...

...

...individually each report was using different underlying connections type.

one report used OLEDB (ADO) and the one that failed was SQLNCLI10.

..

it would be nice to know why when the Connection types were different it would cause the subsequent report to crash.

my code is below. I've removed any code not related to Crystal Rpt processing.

    Friend Sub CreatePDF_Orig(ByVal zReportName As String, ByVal zInspType As String, ByVal zApplNmbr As String, ByVal zInspNmbr As String, ByVal zCreateDateTime As String, ByVal zIsCOrpt As Boolean)

        Dim crzReport As New ReportDocument

        Dim sTempPath As String = Nothing, sDocOutPath As String = Nothing

        Dim sCreatePDFDebug As String = Nothing

        Dim CrzPdfOptions As New PdfFormatOptions

        Dim CrzExportOptions As New ExportOptions

        Dim CrzDiskFileDestinationOptions As New DiskFileDestinationOptions()

        Dim CrzFormatTypeOptions As New PdfRtfWordFormatOptions()

        Dim sReportOutName As String = Nothing, sReportOutSuffix As String = Nothing

        Dim sImageDocFiles As String() = Nothing

        Dim zFileInfo As IO.FileInfo = Nothing

        Dim bAlreadyExists As Boolean = False

        Dim bAExistsOverwrite As Boolean = False

        Dim bImagesMoved As Boolean = False

        Dim sDbServer As String = "(local)\SQLEXPRESS", sDbName As String = "DatabaseNameHere"

        Dim cnxInfo As ConnectionInfo = Nothing

        Try

           ' create directory structure for output documents and path.

            sTempPath = gsMyInspectionsRootPath & "\" & zInspType & "\" & zApplNmbr

            sTempPath = gsMyInspectionsRootPath & "\" & zInspType & "\" & zApplNmbr & "\" & zInspNmbr

            sReportOutName = zApplNmbr & "-" & zInspNmbr & "-" & gsUserName & "-" & zCreateDateTime

           If zIsCOrpt Then

                sReportOutSuffix = "-CO"

                sDocOutPath = gsMyInspectionsRootPath & "\" & zInspType & "\" & zApplNmbr & "\" & zInspNmbr & "\" & sReportOutName & sReportOutSuffix & ".pdf"

            Else

                sDocOutPath = gsMyInspectionsRootPath & "\" & zInspType & "\" & zApplNmbr & "\" & zInspNmbr & "\" & sReportOutName & ".pdf"

            End If

            crzReport.Load(zReportName)

            ' Removes saved data. This causes fresh data to be loaded when the report is subsequently viewed.-- probably not needed.

            ' crzReport.Refresh() '(zReportName)

            ' version 68... 6/12/2015

            If gsUserName = "ADPJA" Or gsUserName = "PDLDW" Then

                '---------------------------------------------------------------------------------------------------------------------------

                ' Explicit connection to each table in report(s) source from internet.

                '---------------------------------------------------------------------------------------------------------------------------

                Dim crTable As Engine.Table

                Dim crTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo

                Dim ConnInfo As New CrystalDecisions.Shared.ConnectionInfo

                ConnInfo.ServerName = sDbServer

                ConnInfo.DatabaseName = sDbName

                ConnInfo.UserID = gCnxDBUser

                ConnInfo.Password = gCnxUserPassword

                For Each crTable In crzReport.Database.Tables

                    crTableLogonInfo = crTable.LogOnInfo

                    crTableLogonInfo.ConnectionInfo = ConnInfo

                    crTable.ApplyLogOnInfo(crTableLogonInfo)

                Next

                '---------------------------------------------------------------------------------------------------------------------------

            Else

                'crzReport.SetDatabaseLogon(gCnxDBUser, gCnxUserPassword)

                crzReport.SetDatabaseLogon(gCnxDBUser, gCnxUserPassword, "(local)\SQLEXPRESS", "DatabaseNameHere")

            End If

            '' Checks whether the database connection is active for the report that is loaded by the ReportDocument object.

            CrzDiskFileDestinationOptions.DiskFileName = sDocOutPath                    'Set the destination path and file name

            CrzExportOptions = crzReport.ExportOptions                                  'Set export options

            With CrzExportOptions

                .ExportDestinationType = ExportDestinationType.DiskFile ' DiskFile, ExchangeFolder, MicrosoftMail, NoDestination

                .ExportFormatType = ExportFormatType.PortableDocFormat  'ExcelWorkBook, HTML32, HTML40, NoFormat, PDF, RichText, RTPR, TabSeperatedText, Text

                .DestinationOptions = CrzDiskFileDestinationOptions

                .FormatOptions = CrzFormatTypeOptions

            End With

            If IsNothing(crzReport) Then sCreatePDFDebug &= ", crzReport is Null b4 CRSetParms."

            CRSetParameters(zReportName, crzReport)

            If IsNothing(crzReport) Then sCreatePDFDebug &= ", crzReport is Null after CRSetParms."

            crzReport.Export()

        Catch exc As Exception

                MsgBox("Error in Module1.CreatePDF:" & vbCrLf & exc.ToString)

        Finally

            crzReport.Close()

            crzReport.Dispose()

            CrzPdfOptions = Nothing

            CrzExportOptions = Nothing

            CrzDiskFileDestinationOptions = Nothing

            CrzFormatTypeOptions = Nothing

            Application.DoEvents()        ' 7/8/2015

        End Try

    End Sub

Message was edited by: Ludek Uher

Accepted Solutions (1)

Accepted Solutions (1)

former_member183750
Active Contributor
0 Kudos

Hi Paul

I branched the discussion.

I have never heard of this behavior, however there are a few ideas I could throw out;

The code is missing .close and .dispose on the report objects, thus persisting previous objects with who knows what consequences.

Subreport and main report not using consistent database connections.

Multi-threaded apps may have interesting behaviors.

The OLE DB layer is not up to date.

And probably more. I'll ask Don tomorrow, see if he has other ideas or if he has seen something like this with win app,

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

Answers (1)

Answers (1)

0 Kudos

Not sure if you are looking for an answer since you state you FOUND it...

For others who read this there is a basic misunderstanding when it comes to Reports so let me explain...

When in CR Designer creating reports often you may reference fields and then change the DB structure and alter the field type or even delete it or add to the DB. Due to the way CR keeps track of the DB structure in the RPT file you MUST Verify the database in CR Designer to bring the info up to date and then save the report.

Another typical scenario is when creating formula, you may get into it or plan to modify the formula at runtime but the MUST DO is the formula MUST be valid. In CR Designer you can simply close the Formula Editor and ignore any errors or save an invalid formula. CR assumes you will go back and "fix" the formula later.

Here's why bad reports work in CR Designer but not when using the SDK....

CRD simply ignores the errors unless they are required when previewing, the SDK does not, It REQUIRES all Objects references to Database info and formula fields actually exist and are valid. The engine when loading a report builds the various collections for all objects and partially evaluates them on load. This is why sometimes you get an error when simply trying to load a report.

Result of not validating the report before opening in SDK is you can get misleading messages like invalid path or an exception may be thrown with an unknown error or even Error 0.

So when Taking over someone elses project PLEASE validate the reports in CRD first if you find an error and fix all of them.

Also be aware when exporting to different formats some of them are page based and some of them are row based. Page base exports like PDF and Word/RTF do not require all objects to be evaluated because suppression formula and other "hide" feature does not require that part of the report to be evaluate on an object level.

When exporting to CSV or other row based formats then EVERY object in the report is evaluated and the formatting engine determines if it needs to be in the output or not BUT it MUST evaluate everything to determine this.

So to validate the database in CRD Click on Database, Set Datasource Location and look at the properties, folder paths or DB client dll used etc. and make sure those folders and Clients are installed and working on the runtime PC.

In the Formula Editor click the X-2 button to verify each and EVERY formula.

Now save the report and update the Project to use the updated report(s).

Once the report has been Verified not test again, 99.9% of the time I find after fixing the reports the application works as designed.

For history this goes back to Crystal Reports Enterprise days... now know as SAP Business Objects Enterprise.

When publishing reports to BOE it also REQUIRED the reports to be valid and no errors generated because the interface did not have the ability to "fix" the report on the fly or ignore errors because BOE is using the SDK to run the reports.

Hope that helps

Don