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):

   1: ''' <summary>
   2: ''' Applies the contents of the ConnectionString property to the report (if it's been set).
   3: ''' </summary>
   4: ''' <remarks></remarks>
   5: Private Sub ApplyNewServer(ByVal report As ReportDocument)
   6:  
   7:     If Me.ServerName = "" Then
   8:         Exit Sub
   9:     End If
  10:  
  11:     For Each subReport As ReportDocument In report.Subreports
  12:         For Each crTable As Table In subReport.Database.Tables
  13:             Dim loi As TableLogOnInfo = crTable.LogOnInfo
  14:             loi.ConnectionInfo.ServerName = Me.ServerName
  15:  
  16:             If Me.UseTrustedConnection = True Then
  17:                 loi.ConnectionInfo.IntegratedSecurity = True
  18:             Else
  19:                 loi.ConnectionInfo.UserID = Me.Username
  20:                 loi.ConnectionInfo.Password = Me.Password
  21:             End If
  22:  
  23:             crTable.ApplyLogOnInfo(loi)
  24:         Next
  25:     Next
  26:  
  27:     'Loop through each table in the report and apply the new login information (in our case, a DSN)
  28:     For Each crTable As Table In report.Database.Tables
  29:         Dim loi As TableLogOnInfo = crTable.LogOnInfo
  30:         loi.ConnectionInfo.ServerName = Me.ServerName
  31:  
  32:         If Me.UseTrustedConnection = True Then
  33:             loi.ConnectionInfo.IntegratedSecurity = True
  34:         Else
  35:             loi.ConnectionInfo.UserID = Me.Username
  36:             loi.ConnectionInfo.Password = Me.Password
  37:         End If
  38:  
  39:         crTable.ApplyLogOnInfo(loi)
  40:         'If your DatabaseName is changing at runtime, specify the table location. 
  41:         'crTable.Location = ci.DatabaseName & ".dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
  42:     Next
  43:  
  44: 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.