Crystal Reports Reportviewer - Set Datasource Dynamically Not Working :argh:

I'm running CR XI, and accessing .RPT files through a ReportViewer in my ASP.NET pages.

I've already got the following code, which is supposed to set the Report Datasource dynamically.

            rptSP = New ReportDocument
            Dim rptPath As String = Request.QueryString("report")
            rptSP.Load(rptPath.ToString, 0)
            Dim SConn As New System.Data.SqlClient.SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
            rptSP.DataSourceConnections(SConn.DataSource, SConn.InitialCatalog).SetConnection(SConn.DataSource, SConn.InitialCatalog, SConn.UserID, SConn.Password)
            Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo
            myConnectionInfo.ServerName = SConn.DataSource
            myConnectionInfo.DatabaseName = SConn.InitialCatalog
            myConnectionInfo.UserID = SConn.UserID
            myConnectionInfo.Password = SConn.Password

            'Two new methods to loop through all objects and tables contained in the requested report and set
            'login credentials for each object and table.  
            SetDBLogonForReport(myConnectionInfo, rptSP)
            SetDBLogonForSubreports(myConnectionInfo, rptSP)


            Me.CrystalReportViewer1.ReportSource = rptSP

But when I go into each .RPT file, and open up the Database Expert section, there is obviously still servernames hardcoded in there, and the code listed above doesn't seem to be able to change the servernames that are hardcoded there.

I say this because I have training 开发者_运维百科and production environments. When the .RPT file is hardcoded with my production server, and I open it on my training server with the code above (and the web.config has the training server in the connection string), I get the ol:

Object reference not set to an instance of an object.

And then if I go into the .RPT file, and change over the datasource to the training server, and try to open it again, it works fine. Why doesn't the code above overwrite the .RPT files datasource?

How can I avoid having to open up each .RPT and change the datasource when migrating reports from server to server? Is there a setting in the .RPT file I'm missing or something?

Are you calling the Crystal method ApplyLogOnInfo? This is a code snippet that I use that works fine.

        //Set database details
        TableLogOnInfo oLogOn;
        foreach (CrystalDecisions.CrystalReports.Engine.Table tbCurrent in report.Database.Tables)
        {
            oLogOn = tbCurrent.LogOnInfo;
            oLogOn.ConnectionInfo.DatabaseName = databaseName;
            oLogOn.ConnectionInfo.UserID = userName;
            oLogOn.ConnectionInfo.Password = pwd;
            oLogOn.ConnectionInfo.ServerName = serverName;
            oLogOn.ConnectionInfo.Type = ConnectionInfoType.SQL;
            tbCurrent.ApplyLogOnInfo(oLogOn);
        }

You would have to change report to rptSP

Thanks

I know it may sound weird, but I experienced such problems using parameters with reports. I had to set the parameter object before setting the value to them.

This could mean that you perhaps need to set your report object to your source before you configure it. Have you ever tried that?

I have the exact same issue with a C# code . I have both a DEV and PROD environment and the datasource is hardcoded to DEV. The interesting part is I have 5 reports that work using the exact same code and 3 that do not. I thought maybe verify database and update database would do the trick as it is keeping DB out of synch and so it does not update datasource but I have done that and still my three reports do not work. Not so sure if it is the code now because my 5 reports using the exact code work.

            addParametersToFields("inst", inst, fields);
            addParametersToFields("reportSubTitle", reportSubTitle, fields);

            CrystalReportViewer1.ParameterFieldInfo = fields;
            rptDoc.Load(Server.MapPath(report));

            ConnectionInfo connectionInfo = Reports.GetConnectionInfo(server, database, "userID", "password");
            connectionInfo.IntegratedSecurity = true;

            connectionInfo.Type = ConnectionInfoType.SQL;
            SetDBLogonForReport(connectionInfo, env);
            CrystalReportViewer1.ReportSource = rptDoc;
        }
        catch (Exception e)
        {
        }
        finally
        {
        }

    }

  private void SetDBLogonForReport(ConnectionInfo oConnectionInfo, string env)
    {
        try
        {
            TableLogOnInfos oTableLogOnInfos = CrystalReportViewer1.LogOnInfo;
            string[] sparams = new string[]{
            };

            foreach (CrystalDecisions.CrystalReports.Engine.Table oTable in rptDoc.Database.Tables)
            {
                if (oTable.LogOnInfo.ConnectionInfo.ServerName == oConnectionInfo.ServerName)
                {
                    TableLogOnInfo oTableLogOnInfo = oTable.LogOnInfo;

                    oTableLogOnInfo.ConnectionInfo = oConnectionInfo;

                    oTable.ApplyLogOnInfo(oTableLogOnInfo);


                    bool b = oTable.TestConnectivity();

                    if (!b)
                    {
                        invokeErrorLogger(sparams, env);
                    }
                }
            }

        }
        catch
        {
            throw;
        }
    }