Crystal Reports, Changing the Database Connection from .Net, SubReport links and the case of the "Missing parameter values"

Posted by Blake on 9/17/2010
)

In my last post, I talked about updating the connection information of a Crystal Report at runtime, and some of the quirky behavior that Crystal Reports exhibits (and by quirky I mean, just doesn't work in certain scenarios). Well, after patting myself on the back at figuring out that I could not use RDO connections and should instead use ADO if I wanted to update them from .Net… I ran into another problem. All of a sudden, when I updated my LoginInfo object in Crystal I would get the following error:

    Missing parameter values.
 at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)
 at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)
 at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)
 at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportOptions options)
 at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToHttpResponse(ExportOptions options, HttpResponse response, Boolean asAttachment, String attachmentName)
 at Iuf.Reporting.CrystalReport.GenerateCrystalReport(HttpResponse& response)
 Missing parameter values.
 at CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext)
 at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

What I found was, if I did not change the login information, the report worked fine. If I did change the login information, Crystal stopped passing down parameters to sub-reports. The top level parameter would be set, but any linked subreports no longer were populated. I quickly learned, that when setting a new DataSource, you had to do so BEFORE you set your parameter values or Crystal would clear all of them. This was a great find (for most people). My problem was, I WAS setting the new connection before the parameter values there that didn't fix my issue. After much frustration, I blindly made a change that fixed my problem. When applying new connection information to a Crystal Report from .Net, you MUST set the subreports connection information before the main report. I don't know why, I can't explain it and frankly, I don't care at this point at 5:17 on a Friday. It works. Here is the updated method I use from the CrystalReports class (you should not that it's references a few properties from the class that you would need to change, but the names should make them self explanitory):

        ''' <summary>
        ''' Applies the contents of the ConnectionString property to the report (if it's been set).
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub ApplyNewServer(ByVal report As ReportDocument)
            If Me.ServerName = "" Then
                Exit Sub
            End If
            For Each subReport As ReportDocument In report.Subreports
                For Each crTable As Table In subReport.Database.Tables
                    Dim loi As TableLogOnInfo = crTable.LogOnInfo
                    loi.ConnectionInfo.ServerName = Me.ServerName
                    If Me.UseTrustedConnection = True Then
                        loi.ConnectionInfo.IntegratedSecurity = True
                    Else
                        loi.ConnectionInfo.UserID = Me.Username
                        loi.ConnectionInfo.Password = Me.Password
                    End If
                    crTable.ApplyLogOnInfo(loi)
                Next
            Next
            'Loop through each table in the report and apply the new login information (in our case, a DSN)
            For Each crTable As Table In report.Database.Tables
                Dim loi As TableLogOnInfo = crTable.LogOnInfo
                loi.ConnectionInfo.ServerName = Me.ServerName
                If Me.UseTrustedConnection = True Then
                    loi.ConnectionInfo.IntegratedSecurity = True
                Else
                    loi.ConnectionInfo.UserID = Me.Username
                    loi.ConnectionInfo.Password = Me.Password
                End If
                crTable.ApplyLogOnInfo(loi)
                'If your DatabaseName is changing at runtime, specify the table location. 
                'crTable.Location = ci.DatabaseName & ".dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
            Next
        End Sub

Now a small rant, the piece that I find most frustrating is that individuals have been having this problem with Crystal for over a half decade (as well as flat out not being able to change RDO connections real time from code). When you look at snippits of code provided with Visual Studio and from SAP/Business Objects, they do not tell you to swap out your subreports connections first (in fact, their snippits all tell you to do it the other way). Who tests this stuff and do they use real world scenarios? Sure, there are work arounds, you could use DataTables and pass those to reports... but shouldn't there be a standard way to update connections that consistently works. For everything I like about Crystal (like a decent programming API), they took it like 90% of the way there and then floundered on a few key pieces that have frustrated and continue to frustrate their users.