C# – Using an ODBC connection to load a Crystal Report in Code-Behind

asp.netccrystal-reports

I have a current set of code working to load a Crystal Report in c# with a server connection.

Here's the code for loading the report:

CrystalReportViewer1.ParameterFieldInfo.Clear();
string reportName = Request.QueryString["rpt"];
if (reportName != null)
{
    crystalReport = new ReportDocument();
    crystalReport.Load(@"\\esco-iis\crystalreports\purchasing\po.rpt");
    //crystalReport.Load(@"\\esco\crystalreports\purchasing\po.rpt");

    var connectionInfo = new ConnectionInfo();
    connectionInfo.ServerName = "server";
    connectionInfo.DatabaseName = "database";
    connectionInfo.Password = "pass";
    connectionInfo.UserID = "user";
    connectionInfo.IntegratedSecurity = false;
    crystalReport.SetDatabaseLogon("user", "pass", "server", "database");
    Tables CrTables = crystalReport.Database.Tables;
    TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
    {
        crtableLogoninfo = CrTable.LogOnInfo;
        crtableLogoninfo.ConnectionInfo = connectionInfo;
        CrTable.ApplyLogOnInfo(crtableLogoninfo);
        CrystalReportViewer1.LogOnInfo.Add(crtableLogoninfo);

    }

    foreach (ReportDocument report in crystalReport.Subreports)
    {
        report.SetDatabaseLogon("user", "pass", "server", "database");
        foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in report.Database.Tables)
        {
            crtableLogoninfo = CrTable.LogOnInfo;
            crtableLogoninfo.ConnectionInfo = connectionInfo;
            CrTable.ApplyLogOnInfo(crtableLogoninfo);
            CrystalReportViewer1.LogOnInfo.Add(crtableLogoninfo);
        }
    }

    string PO = Request.QueryString["PO"];
    if (PO != null)
    {
        ParameterField parameter = new ParameterField();
        ParameterDiscreteValue parameterValue = new ParameterDiscreteValue();
        parameterValue.Value = PO;
        parameter.Name = "@PO";
        parameter.CurrentValues.Add(parameterValue);
        CrystalReportViewer1.ParameterFieldInfo.Add(parameter);
    }
}

Current state: This works when the report is set up to connect to the database direct, but about half of them are set up to use an ODBC connection.

Attempt: I attempted to switch the server name to the name of the ODBC connection, but this still came back with the same Failed to open the connection. Failed to open the connection. error.

Problem: I can't seem to figure out what I can change in my ConnectionInfo to be able to make it use an ODBC connection successfully. I keep getting a Failed to open the connection. Failed to open the connection.

I'm aware of the solution to do a connection separately and set the reportsource, but this isn't feasible since there are many reports.

Thanks in advance!

Best Answer

Setting an ODBC connection at runtime is rather tricky, mostly because there are all these additional "logon properties" and "connection attributes" that need to be set:

// DbConnectionAttributes contains some, but not all, consts.
var logonProperties = new DbConnectionAttributes();
logonProperties.Collection.Set("Connection String", @"Driver={SQL Server};Server=TODD-PC\SQLEXPRESS2;Trusted_Connection=Yes;");
logonProperties.Collection.Set("UseDSNProperties", false);

var connectionAttributes = new DbConnectionAttributes();
connectionAttributes.Collection.Set("Database DLL", "crdb_odbc.dll");
connectionAttributes.Collection.Set("QE_DatabaseName", String.Empty);
connectionAttributes.Collection.Set("QE_DatabaseType", "ODBC (RDO)");
connectionAttributes.Collection.Set("QE_LogonProperties", logonProperties);
connectionAttributes.Collection.Set("QE_ServerDescription", @"TODD-PC\SQLEXPRESS2");
connectionAttributes.Collection.Set("QE_SQLDB", true);
connectionAttributes.Collection.Set("SSO Enabled", false);

return new ConnectionInfo
           {
               Attributes = connectionAttributes,
               // These don't seem necessary, but we'll include them anyway: ReportDocument.Load does
               ServerName = @"TODD-PC\SQLEXPRESS2",
               Type = ConnectionInfoType.CRQE
           };

On the other hand, rather than creating a ConnectionInfo object from scratch, you might be able to use the existing one, especially if you already designed your report using a similar connection:

foreach (Table crTable in crTables)
{
    TableLogOnInfo tableLogOnInfo = crTable.LogOnInfo;
    var connectionInfo = tableLogOnInfo.ConnectionInfo;

    // set connection info stuff here

    crTable.ApplyLogOnInfo(tableLogOnInfo);
}
Related Topic