How to change a Crystal Reports datasource location programmatically in a report using SQL command from Database Expert?

Question

I'm creating a report (Crystal Reports 11 ) using SQL command from Database Expert in Visual Studio. So, the query responsible for retrieving information from database is inside the rpt file.

I want to open it in my web application (C#) as a PDF document. It works when the database connection string of my application is the same as used in the report designer. When a different database is configured in Web.config, I get the following error:

[COMException (0x80004005): The system cannot find the file specified.]

CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext) +0
CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext) +1994
CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext) +802
CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportOptions options) +231
CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToHttpResponse(ExportOptions options, HttpResponse response, Boolean asAttachment, String attachmentName) +403
CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToHttpResponse(ExportFormatType formatType, HttpResponse response, Boolean asAttachment, String attachmentName) +307
RelProducaoCDS.PreencherDados(Int64 codUsuario, Int64 codUnidade, DateTime datInicio, DateTime datFim) in d:\Projetos-NET\Projetos Net\Governa.Saude.AtencaoBasica\Governa.Saude.AtencaoBasica.Web\Relatorios\Producao\RelProducaoCDS.aspx.cs:110
RelProducaoCDS.Page_Load(Object sender, EventArgs e) in d:\Projetos-NET\Projetos Net\Governa.Saude.AtencaoBasica\Governa.Saude.AtencaoBasica.Web\Relatorios\Producao\RelProducaoCDS.aspx.cs:59
System.Web.UI.Control.LoadRecursive() +71
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3178

Code used to export report to HttpResponse as a PDF document. The connection string is retrieved from Web.config through ConfigurationManager class.

        Hashtable parameters = new Hashtable();
        parameters.Add("COD_USER", codUsuario);
        parameters.Add("DAT_START", datInicio);
        parameters.Add("DAT_END", datFim);

        ExportPDFToHttpResponse("myDatabaseConnection", "~\\Reports\\Production\\RelProductionCDS.rpt", parameters, new Hashtable());
    }

     public void ExportPDFToHttpResponse(string connectionName, string rptPath, Hashtable parameters, Hashtable parametersFormula)
    {
        ReportDocument rpt = CreateReportDocument(connectionName, rptPath);
        foreach (string key in parameters.Keys)
        {
            rpt.SetParameterValue(key, parameters[key]);
        }
        foreach (string key in parametersFormula.Keys)
        {
            rpt.DataDefinition.FormulaFields[key].Text = string.Concat("\"", parametersFormula[key] ,"\"");
        }
        string reportName = rptPath.Substring(0, rptPath.LastIndexOf('.'));
        reportName = reportName.Substring(reportName.LastIndexOf('\\'));
        rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, false, reportName);
        rpt.Close();
        rpt.Dispose();
    }

    private ReportDocument CreateReportDocument(string connectionString, string rptPath)
    {
        ReportDocument rpt = new ReportDocument();
        rpt.Load(Server.MapPath(rptPath));
        ConnectionInfo connInfo = new ConnectionInfo();

        string connString = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connString);
        connInfo.ServerName = builder.DataSource;
        connInfo.DatabaseName = builder.InitialCatalog;
        connInfo.IntegratedSecurity = builder.IntegratedSecurity;
        if (!builder.IntegratedSecurity)
        {
            connInfo.Password = builder.Password;
            connInfo.UserID = builder.UserID;
        }

        Tables tables = rpt.Database.Tables;
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
        {
            TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
            tableLogOnInfo.ConnectionInfo = connInfo;
            table.ApplyLogOnInfo(tableLogOnInfo);
        }
        return rpt;
    }
}

samithagun samithagun · Accepted Answer · 2017-04-04T09:51:10

Once you created a crystal report and run it, the SQL query will be hard coded inside the report. You can check it via "Show SQL Query" in the Database menu.

After that, you can only change the server name through your C# code. Your Database name & table has to be the same or else you will get errors. So I suggest you create 2 or more reports if you have different databases.

However, you can point your existing report database to a new one using below method. Point Crystal Reports at a new database

Hope this will help.

How to change a Crystal Reports datasource location programmatically in a report using SQL command from Database Expert?

1 Answers