How do I change report DSN at runtime?

[former_member1013264]

Hello,

I have some reports (some 2011 but most 9) that were created with a DSN named Dev for example.  When the reports are deployed to production, they are viewed via an ASP.NET application written in VB.NET (VS2012).  The production server has a DSN named Prod for example. The code I'm using to change the connection information "seems" to be working because if I write out the connection info before the code, it shows the Dev DSN connection information and if I write out the connection inf after the code, it shows the Prod DSN connection information.

The problem is that even though the connection information appears to be changing at runtime, when the report renders it shows data from the Dev database instead of production.

Here is the code I'm using to change the main report connection and the subreports, which seems to be working:

        Dim strPassword As String = System.Configuration.ConfigurationManager.AppSettings("ReportPassword")

        Dim strUserID As String = System.Configuration.ConfigurationManager.AppSettings("ReportUser")

        Dim strServer As String = System.Configuration.ConfigurationManager.AppSettings("ReportServer")

        Dim strDBName As String = System.Configuration.ConfigurationManager.AppSettings("ReportDatabase")

        Dim strDSName As String = System.Configuration.ConfigurationManager.AppSettings("ReportDSN")

        Dim myTable As CrystalDecisions.CrystalReports.Engine.Table

        Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo

        Dim blDebugFlag As Boolean = False

        'This test is needed because the flag may/should not exist in the production web.config

        If Not System.Configuration.ConfigurationManager.AppSettings("debugFlag") Is Nothing Then

            blDebugFlag = System.Configuration.ConfigurationManager.AppSettings("debugFlag")

        End If

        'Modify the data source location for each table in the report (does not include subreports)

        strReturnData += Now.ToLongTimeString & "<br>"

        For Each myTable In Session("crpt").Database.Tables

            myLogin = myTable.LogOnInfo

            strReturnData += "<br><br>Table = [<b>" & myTable.Name.ToString & "</b>] - (Main report)" & _

              "<blockquote>" & _

              "<br>Original ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

              "<br>Original DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

              "<br>Original UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]"

            'check if a DSN or non-DSN connection

            If myTable.LogOnInfo.ConnectionInfo.DatabaseName = "" Or myTable.LogOnInfo.ConnectionInfo.ServerName = "Dev" Then

                myLogin.ConnectionInfo.ServerName = strDSName

                myLogin.ConnectionInfo.DatabaseName = ""

                strReturnData += "<br><br> ### DSN Connection ###"

            Else

                myLogin.ConnectionInfo.ServerName = strServer

                myLogin.ConnectionInfo.DatabaseName = strDBName

                strReturnData += "<br><br> *** Non-DSN Connection ***"

            End If

            myLogin.ConnectionInfo.Password = strPassword

            myLogin.ConnectionInfo.UserID = strUserID

            myTable.ApplyLogOnInfo(myLogin)

            ' Collect the login information after-the-fact

            strReturnData += "<br><br>Final ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

            "<br>Final DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

            "<br>Final UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]" & _

            "</blockquote>"

        Next

        'Iterate through subreports and make sure ConnectionInfo matches the main report

        'set the crSections object to the current report's sections

        Dim crSections As CrystalDecisions.CrystalReports.Engine.Sections = Session("crpt").ReportDefinition.Sections 'rpt.ReportDefinition.Sections

        Dim crReportobjects As ReportObjects, crSubReportobject As SubreportObject, crSubReportDocument As ReportDocument

        Dim crDatabase As Database, crTables As Tables

        Dim crtableLogoninfo As New TableLogOnInfo

        Dim crConnectioninfo As New ConnectionInfo

        'loop through all the sections to find all the report objects

        For Each crSection As CrystalDecisions.CrystalReports.Engine.Section In crSections

            crReportObjects = crSection.ReportObjects

            'loop through all the report objects to find all the subreports

            For Each crReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In crReportObjects

                If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                    'you will need to typecast the reportobject to a subreport object once you find it

                    crSubreportObject = DirectCast(crReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)

                    Dim mysubname As String = crSubreportObject.SubreportName.ToString()

                    'open the subreport object

                    crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

                    'set the database and tables objects to work with the subreport

                    crDatabase = crSubreportDocument.Database

                    crTables = crDatabase.Tables

                    'loop through all the tables in the subreport and set up the connection info and apply it to the tables

                    For Each crTable As CrystalDecisions.CrystalReports.Engine.Table In crTables

                        'With crConnectioninfo

                        '    .ServerName = strDSName

                        '    .DatabaseName = ""

                        '    .UserID = strUserID

                        '    .Password = strPassword

                        'End With

                        'crtableLogoninfo = crTable.LogOnInfo

                        'crtableLogoninfo.ConnectionInfo = crConnectioninfo

                        'crTable.ApplyLogOnInfo(crtableLogoninfo)

                        myLogin = crTable.LogOnInfo

                        strReturnData += "<br><br>Table = [<b>" & crTable.Name.ToString & "</b>] - (Subreport - [" & mysubname & "])" & _

                          "<blockquote>" & _

                          "<br>Original ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

                          "<br>Original DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

                          "<br>Original UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]"

                        'check if a DSN or non-DSN connection

                        If crTable.LogOnInfo.ConnectionInfo.DatabaseName = "" Or crTable.LogOnInfo.ConnectionInfo.ServerName = "Dev" Then

                            myLogin.ConnectionInfo.ServerName = strDSName

                            myLogin.ConnectionInfo.DatabaseName = ""

                            strReturnData += "<br><br> ### DSN Connection ###"

                        Else

                            myLogin.ConnectionInfo.ServerName = strServer

                            myLogin.ConnectionInfo.DatabaseName = strDBName

                            strReturnData += "<br><br> *** Non-DSN Connection ***"

                        End If

                        myLogin.ConnectionInfo.Password = strPassword

                        myLogin.ConnectionInfo.UserID = strUserID

                        crtableLogoninfo = crTable.LogOnInfo

                        crtableLogoninfo.ConnectionInfo = myLogin.ConnectionInfo

                        crTable.ApplyLogOnInfo(crtableLogoninfo)

                        ' Collect the login information after-the-fact

                        strReturnData += "<br><br>Final ServerName = [" & myLogin.ConnectionInfo.ServerName.ToString & "]" & _

                        "<br>Final DatabaseName = [" & myLogin.ConnectionInfo.DatabaseName.ToString & "]" & _

                        "<br>Final UserId = [" & myLogin.ConnectionInfo.UserID.ToString & "]" & _

                        "</blockquote>"

                    Next 'crTable

                End If

            Next 'crReportObject

        Next 'crSection

Here is what gets printed after this code when the debug flag is True:

   Table = [usp_MainReportProcedureName] - (Main report)

      Original ServerName = [Dev]

      Original DatabaseName = [Dev]

      Original UserId = [UserName]

      ### DSN Connection ###

      Final ServerName = [Prod]

      Final DatabaseName = []

      Final UserId = [UserName]

   Table = [usp_SubreportProcedureName] - (Subreport - [subrptName.rpt])

      Original ServerName = [Dev]

      Original DatabaseName = [Dev]

      Original UserId = [UserName]

      ### DSN Connection ###

      Final ServerName = [Prod]

      Final DatabaseName = []

      Final UserId = [UserName]

Here is the export code that renders the report:

        'Export to new format

        Dim crExportOptions As New ExportOptions

        Dim crDiskFileDestinationOptios As New DiskFileDestinationOptions

        Dim fname As String = ""

        Dim gFileId As New Guid

        Try

            'Filename for temporary report

            fname = System.Configuration.ConfigurationManager.AppSettings("DocumentsLocation") + gFileId.ToString + ".pdf"

            crDiskFileDestinationOptios.DiskFileName = fname

            crExportOptions = Session("crpt").ExportOptions

            Session("crpt").ExportOptions.DestinationOptions = crDiskFileDestinationOptios

            Session("crpt").ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile

            Session("crpt").ExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat

            Session("crpt").Export()

            'Display and Cleanup

            Response.ClearContent()

            Response.ClearHeaders()

            If blDebugFlag = False Then

                'Display the report

                Response.ContentType = "application/pdf"

                Response.WriteFile(fname)    'writes out PDF if created

            Else

                'Display connection information for debugging

                '*** DEBUG CODE *** for use in TEST ENVIRONMENTS ONLY

                Response.Write(strReturnData)

            End If

        Catch ex As Exception

            Session("error") = ex.ToString

            'Add the location of the error

            Session("error") = "[Address: " & Request.Url.ToString & "] " & Session("error")

            Response.Redirect("ErrorEvents.aspx")

        Finally

            Response.Flush()

            'Response.Close()  'Prevents pdf's from rendering in Chrome

            System.IO.File.Delete(fname)

            GC.Collect()

        End Try

I'm wondering if there is some additional code needed after .ApplyLogOnInfo or if there are some other parameters needed when exporting after the connection info has been changed or if there is something else I'm missing.

Thanks for your help.

[former_member183750 · Accepted solution]

It will be good to know the version of CR you are using (including any updates).

The database.

The database client.

You may also want to add code to test the connection:

If myTable.TestConnectivity = True Then

.

.

.

Also add:

myReportDocument.Refresh()

myReportDocument.VerifyDatabase()

As a test, what happens if you rename the dev DSN so the report cannot find it?

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[former_member1013264 · Accepted solution]

Thank you for the troubleshooting ideas.

The version of the Crystal Runtime on the web server is:  CRRuntime_32bit_13_0_6.msi

The database is on an SQL Server 2008 separate server

I started with adding this code just before the export code right after all the looping to change the connection info:

   Session("crpt").Refresh()

   Session("crpt").VerifyDatabase()

I tried running a report and an error was raised a few lines later at:

   Session("crpt").Export()

Here is the error message:

CrystalDecisions.CrystalReports.Engine.ParameterFieldCurrentValueException: Missing parameter values. ---> System.Runtime.InteropServices.COMException: Missing parameter values.

  at CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext)

  at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext) --- End of inner exception stack trace ---

  at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)

  at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)

  at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)

  at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)

Next, I commented out those added lines (refresh and verify) and tried renaming the Dev DSN to Devxxx on the web application server.

I'm at a loss but somehow the report rendered still showing data from the Dev database. I checked the Prod DSN just to be sure it was pointed at the Prod database and it is. I rebooted the server and still got the same results.  I opened the report directly in CR 2011 on the web server and tried to preview it. The ODBC dialog came up and the Devxxx DSN was there.

I changed some data in Dev ran the report through the web application in Prod and the data on the report showed the changed data in Dev.

The web application says the report connection info is for Dev and then it is changed for Prod and then even though the Dev DSN no longer exits the report still renders from Dev data???

I'm totally confused as to how this can be but maybe if we can solve the refresh and verify error the DSN mystery will become mute.

[former_member11696 · Accepted solution]

Hi Dave,

It could be as your logging shows:

      Original ServerName = [Dev]

      Original DatabaseName = [Dev]

      Original UserId = [UserName]

      ### DSN Connection ###

      Final ServerName = [Prod]

      Final DatabaseName = []

      Final UserId = [UserName]

After you set it to the Production DB the database name is empty. Should this not have the same name [Dev] or renamed to [Prod]? In either case it should not be empty.

If this is MS SQL Server that must have a name

And I would not Use .Refresh at all, this has all sorts of timing issues, it could be you are resetting the DB back to the DEV DB.

Rather than .Verify use TestDatabase as Ludek first suggested.

What happens if you stop the DEV DB completely? CR has the DSN info embedded in the RPT file so it has enough info to connect, renaming the DSN is not enough to validate which DB it is connecting to.

And of course the usual, install the MS SQL Server Native 10 client on both your DEV and App/IIS Server and update the reports to use the updated client.

You can do it in code but requires RAS and ReplaceConnection API to do this.

And Crlogger may give you more info, search for it, there is a Kbase article on how to enable.

Don

[former_member1013264 · Accepted solution]

Thanks for your help!  I didn't know a database name must still be included in the VB code when using a DSN.

[former_member11696 · Accepted solution]

Hi Dave,

Ah yes, CR doesn't care how you connect we just need the same logon connection info you use for any client. CR is going through the Client to get connected. So if you don't select a DB in a DSN you can't get past it either...

Thanks for the update

Don

[DellSC]

Do the reports have "Save Data with Report" turned on?  (This is on by default...)  If so, turn it off and resave the reports.  This can cause this issue.

-Dell

[former_member1013264]

I opened a few of the reports that are giving me this problem and none of them have that option (Save Data with Report) checked in the File menu.  If I change data in the Dev database, I can see the changed data on the reports in production.  So the data in the reports is being refreshed but it's from the Dev DSN instead of the Prod DSN even though the connection information appears to be changing correctly at runtime.