How do I change a Crystal Report's ODBC database connection at runtime?

Question

I have a report made with Crystal Reports 2008 that I need to deploy a production system which means that I need to be able to change the database connection at runtime. The database is PostgreSQL 8.3.0 and the connection I use for creating the initial report is an ODBC connection.

I have found various ways to change the database connection including the following:

reportDoc.Load(report);
reportDoc.DataSourceConnections[0].SetConnection("server", "database", "user", "pwd");
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

However, this always fails with the following error message.

Failed to open the connection.

I have validated the connection parameters by successfully connecting to the database with pgAdmin III so I know the connection parameters are correct. In addition, if I remove the SetConnection(...) line so the code looks like this:

reportDoc.Load(report);
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

then the report runs fine using the connection parameters that are stored in the report. Is it possible that this method does not work for ODBC connections?

How do I change a Crystal Report's ODBC database connection at runtime?

Solution 1

After even more research I found that there was a two part answer.

PART 1

If you are connecting to PostgreSQL via ODBC (the only way Crystal Reports can pull data from PostgreSQL as of the time of this writing) using the data owner you then you can use the following code:

reportDoc.Load(report);
reportDoc.DataSourceConnections[0].SetConnection("Driver={PostgreSQL ANSI};Server=myServer;Port=5432;", "myDatabase", "myUser", "myPassword");
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);
// Depending on your application you may have more than one data source connection that needs to be changed.

This method only works if you are connecting as a user that owns the data that you are reporting on because the schema name does not need to be supplied.

PART 2

If you are connecting to PostgreSQL via ODBC with a user other than the data owner then you need to manually supply the schema name. This is accomplished with the following code.

reportDoc.Load(report);

ConnectionInfo connInfo = new ConnectionInfo();
connInfo.ServerName = "Driver={PostgreSQL ANSI};Server=myServer;Port=5432;";
connInfo.DatabaseName = "myDatabase";
connInfo.UserID = "myUser";
connInfo.Password = "myPassword";

TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
tableLogOnInfo.ConnectionInfo = connInfo;

foreach (Table table in reportDoc.Database.Tables)
{
    table.ApplyLogOnInfo(tableLogOnInfo);
    table.LogOnInfo.ConnectionInfo.ServerName = connInfo.ServerName;
    table.LogOnInfo.ConnectionInfo.DatabaseName = connInfo.DatabaseName;
    table.LogOnInfo.ConnectionInfo.UserID = connInfo.UserID;
    table.LogOnInfo.ConnectionInfo.Password = connInfo.Password;

    // Apply the schema name to the table's location
    table.Location = "mySchema." + table.Location;
}

reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

Summary

There are two critical pieces of information here when trying to connect to a PostgreSQL database from Crystal Reports.

1. The driver, server, and port number must all be specified in the server name property.
2. If connecting as a user other than the data owner you must specify the schema name for each table you are pulling data from.

Sources

There were several sources used that did not have an answer that worked in my specific scenario but that led me in the right direction. These sources are listed below.

• Nathan Koop's Answer
• https://www.sdn.sap.com/irj/scn/thread?messageID=6913827#6913827
• Best Practices for Changing Databases at Runtime

Solution 2

First of all thank you for this information!!!

I'm using MySQL/C#/Crystal Reports. After setting up the ODBC/DSN something as simple as this worked for me.

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using MySql.Data.MySqlClient;

.
.
.

ConnectionInfo connInfo = new ConnectionInfo();

connInfo.ServerName = "Driver={MySQL ODBC 3.51 Driver};DSN=MyODBCDatasourceName";

TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();

tableLogOnInfo.ConnectionInfo = connInfo;

// rpt is my Crystal Reports ReportDocument

// Apply the schema name to the table's location

foreach (Table table in rpt.Database.Tables)

{

    table.ApplyLogOnInfo(tableLogOnInfo);

    table.Location = table.Location;

}

Solution 3

I just went through this same ordeal.

I set my connections like this (where sDataSource etc... are string with the information)

    Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
    myConnectionInfo.ServerName = sDataSource
    myConnectionInfo.DatabaseName = sInitialCatalog
    myConnectionInfo.UserID = sUserID
    myConnectionInfo.Password = sPassword

    Dim myTables As Tables = report.Database.Tables
    Dim myTableLogonInfo As TableLogOnInfo = New TableLogOnInfo()
    myTableLogonInfo.ConnectionInfo = myConnectionInfo
    For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
        myTable.ApplyLogOnInfo(myTableLogonInfo)

        myTable.LogOnInfo.ConnectionInfo.DatabaseName = myTableLogonInfo.ConnectionInfo.DatabaseName
        myTable.LogOnInfo.ConnectionInfo.ServerName = myTableLogonInfo.ConnectionInfo.ServerName
        myTable.LogOnInfo.ConnectionInfo.UserID = myTableLogonInfo.ConnectionInfo.UserID
        myTable.LogOnInfo.ConnectionInfo.Password = myTableLogonInfo.ConnectionInfo.Password

    Next

Solution 4

Updating the ODBC within a crystal report file.

We are using ODBC with MSSQL, we couldn't find how to update the ODBC within the crystal files within a C sharp project.

With the example provided here we were able to find the way to update the ODBC within MSSQL, and it is as simple as this:

       Cursor.Current = Cursors.WaitCursor;

        CrystalDecisions.Windows.Forms.CrystalReportViewer CR_Viewer;
        CR_Viewer = new CrystalDecisions.Windows.Forms.CrystalReportViewer();
        this.Controls.Add(CR_Viewer);

        ConnectionInfo connInfo = new ConnectionInfo();
        connInfo.ServerName = "YOUR ODBC NAME"; 

        TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
        tableLogOnInfo.ConnectionInfo = connInfo;

         //THIS IS A CRYSTAL RPT FILE DIFINE AS A CLASS
        Facturation_Nord_Ouest.Reports.Facture_Français CrystalReportFr;


       CrystalReportFr = new Facturation_Nord_Ouest.Reports.Facture_Français();

            for (int i = 0; i < CrystalReportFr.Database.Tables.Count; i++)
            {
                CrystalReportFr.Database.Tables[i].ApplyLogOnInfo(tableLogOnInfo);
            }
            CR_Viewer.ReportSource = CrystalReportFr;

        CR_Viewer.ActiveViewIndex = 0;
        CR_Viewer.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
        CR_Viewer.Dock = System.Windows.Forms.DockStyle.Fill;
        CR_Viewer.Location = new System.Drawing.Point(0, 0);
        CR_Viewer.Size = new System.Drawing.Size(545, 379);
        CR_Viewer.TabIndex = 0;
        CR_Viewer.Name = "Invoice";
        CR_Viewer.Zoom(100);
        CR_Viewer.Show();

        Cursor.Current = Cursors.Default;

With this the ODBC in the crystal file is automaticaly updated.

View more solutions

Comments

[Ryan Taylor]

This still fails for me with the same "Failed to open the connection" error message. What database are you using?

[Nathan Koop]

also ensure that you have the appropriate driver installed on your test machine (if different from your dev machine)

[Ryan Taylor]

I am not using integrated security. Additionally, my testing is on my dev machine and I do have the appropriate drivers as the report will run correctly on my dev machine when I do not modify the connection at runtime.

[Ryan Taylor]

Thanks for all the info and the quick responses. I ended up taking a different approach (less code). Ultimately I needed to specify the driver and port in the server name because I was using an ODBC connection where Crytsal Reports doesn't have native support for PostgreSQL.

[DeveloperChris]

This worked for me the most important line was connInfo.ServerName = "YOUR ODBC NAME"; thanks patm

[Dan Diplo]

Thanks, this helped me pull myself out of the quagmire that is Crystal Reports.

[Nathan Koop]

@Dan +1 for quagmire of Crystal reports

[Ryan Taylor]

@amateurprogrammer, I do not see why this wouldn't work if you supply the appropriate mysql driver/connection string.