cancel
Showing results for 
Search instead for 
Did you mean: 

Changing Connection String dynamically at runtime

Former Member
0 Kudos

I am using the Crystal Report Viewer control in in a Visuall Studio 2008 C# project. The reports are created inside of Visual Studio 2008, and will be running against an InterSystems Cache database. I have to use an ODBC (RDO) connection and use a connection string.

I have seen many examples of how to update the connection string at runtime if it is SQL server (or some flavor thereof), but nothing for an ODBC (RDO) connection to a NON-MSSQL database.

The problem when I update the connection string for each of the tables in the report, the report will either not run and display a window -- tiled 'Datbase Login' -- that has the Server, Database, Username, and Password fields on the window, OR, it will run but not against the database that was specified at runtime (it uses the connection stored in the report).

-


Code Sample #1 (This is using the connection string, and will cause a the "Database Login" window to be displayed - the first scenario from above)


rd = new ReportDocument();

// Load the report
rd.Load(fileName, OpenReportMethod.OpenReportByDefault);
rd.Refresh();

// Create the connection object
ConnectionInfo connectionInfo = CreateConnection(Server, Port, ServerNamespace, Username, Password);

// Set the connection info on each table in the report
SetDBLogonForReport(connectionInfo, rd);
SetDBLogonForSubreports(connectionInfo, rd);



private static ConnectionInfo CreateConnection(string Server,
                                               int Port,
                                               string ServerNamespace,
                                               string Username,
                                               string Password)

{
        ConnectionInfo connectionInfo = new ConnectionInfo();

        string connString = "DRIVER=InterSystems ODBC;SERVER=" + Server
                                                  + ";PORT=" + Port
                                                  + ";DATABASE=" + ServerNamespace
                                                  + ";UID=" + Username
                                                  + ";PWD=" + Password;

        connectionInfo.IntegratedSecurity = false;
        connectionInfo.UserID = Username;
        connectionInfo.Password = Password;        //In examples that I have seen, this is the actual connection string, not just the server name
        connectionInfo.ServerName = connString;          connectionInfo.DatabaseName = ServerNamespace;
        connectionInfo.Type = ConnectionInfoType.CRQE;

        return connectionInfo;

}

private static void SetDBLogonForReport(ConnectionInfo connectionInfo, ReportDocument reportDocument)
{
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in reportDocument.Database.Tables)

        {
            TableLogOnInfo tableLogonInfo = table.LogOnInfo;
            tableLogonInfo.ConnectionInfo = connectionInfo;
            table.ApplyLogOnInfo(tableLogonInfo);
        }
}

private static void SetDBLogonForSubreports(ConnectionInfo connectionInfo, ReportDocument reportDocument)
{
        foreach (Section section in reportDocument.ReportDefinition.Sections)
        {
            foreach (ReportObject reportObject in section.ReportObjects)
            {
                if (reportObject.Kind == ReportObjectKind.SubreportObject)
                {
                    SubreportObject subreportObject = (SubreportObject)reportObject;
                    ReportDocument subReportDocument = subreportObject.OpenSubreport(subreportObject.SubreportName);
                    SetDBLogonForReport(connectionInfo, subReportDocument);
                }
            }
        }
}

-


Sample #2 (the same as Sample #1 above, only the ConnectionInfo method is different - which is all that I included here)


private static ConnectionInfo CreateConnection(string Server,
                                                int Port,
                                               string ServerNamespace,
                                               string Username,
                                               string Password)
{
        ConnectionInfo connectionInfo = new ConnectionInfo();



        string connString = "DRIVER=InterSystems ODBC;SERVER=" + Server
                                                  + ";PORT=" + Port
                                                  + ";DATABASE=" + ServerNamespace
                                                  + ";UID=" + Username
                                                  + ";PWD=" + Password;

            

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.QE_DATABASE_NAME, ServerNamespace));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          "QE_DatabaseType", "ODBC (RDO)"));



        DbConnectionAttributes attributes = new DbConnectionAttributes();
        attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.CONNINFO_CONNECTION_STRING, connString));

        attributes.Collection.Add(new NameValuePair2(
                          "Server", Server));
        attributes.Collection.Add(new NameValuePair2(
                          "UseDSNProperties", false));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2
(                          DbConnectionAttributes.QE_LOGON_PROPERTIES, attributes));
        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.QE_SERVER_DESCRIPTION, Server));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          "QE_SQLDB", true));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.CONNINFO_SSO_ENABLED, false));


        connectionInfo.IntegratedSecurity = false;
        connectionInfo.UserID = Username;
        connectionInfo.Password = Password;
        connectionInfo.ServerName = connString;
        connectionInfo.DatabaseName = ServerNamespace;
        connectionInfo.Type = ConnectionInfoType.CRQE;

        return connectionInfo;

}

-


I have also tried setting connectionInfo.ServerName equal to just the real server name, but that didn't work either.

After the above operations are performed, I simply assign the returned ReportDocument object to the Crystal Viewer control. If I don't call the above functions, and just set the ReportDocument source (using whatever connection string was embedded in the report originally), the report runs as expected. Only when I attempt to update the connection string, the report will not execute. Please advise. Thanks!

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

No I just had to download the redistributable runtime package that was for the full Crystal 2008 and install it.

Former Member
0 Kudos

No I just had to download the redistributable runtime package that was for the full Crystal 2008 and install it.

Former Member
0 Kudos

Even though these reports were created with Crystal Reports for Visual Studio 2008, it required me to use the Crystal Reports 2008 runtime (the full version of 2008, not the runtime for the version of Crystal bundled with Visual Studio).

Once I got past that little issue, it worked very well.

Former Member
0 Kudos

Does this mean you had to purchase the full version of Crystal 2008 instead of the "free" one in Visual Studio?

Former Member
0 Kudos

Hello Brice,

I know this is a pretty old thread but I didn't know how else to contact you.

I am having exactly same issue.

I am using InterSystems CACHE 2014 and Microsoft Visual Studio 2010.  My Crystal reports are developed using Crystal Reports 2008.

When I try to run and display the Crystal Report in a web application using crystal report viewer ,  I can't change reports Datasource at run time.  If report was developed using datasource named  "ABC"  and if I try to run it with datasource "XYZ"  it doesn't work.  It doesn't display any data.

I have the issue on my development machine.

I have all the following softwares installed on my machine:

1. Microsoft Visual Studio 2010

2. SAP Crystal reports, version for Microsoft Visual Studio (version: 13.0.11)

3. SAP crystal reports runtime engine for .Net Framework (64-bit)

4. Crystal Reports 2008 (Full version)

and I am still unable to change reports Datasource at runtime.

Would you please advise if I am missing something?  Really appreciate your help.

Thanks.

DellSC
Active Contributor
0 Kudos

SP 11 of the SDK has some significant issues and has been withdrawn.  I would uninstall it and get SP 10.  If that doesn't solve the issue, then post a new discussion here and we'll see what we can do to help.

-Dell

Former Member
0 Kudos

Sorry for the formatting issues, I can't seem to fix them. The 'Plain text' view looks ok, but the post looks horrible! It appears to be an issue with how SAP is rendering the posts. There is some combination of characters that their renderer can't handle! Sorry!

Edited by: BriceOliver on Dec 21, 2009 9:14 PM

Former Member
0 Kudos

I am using the Crystal Report Viewer control in in a Visuall Studio 2008 C# project. The reports are created inside of Visual Studio 2008, and will be running against an InterSystems Cache database. I have to use an ODBC (RDO) connection and use a connection string.

I have seen many examples of how to update the connection string at runtime if it is SQL server (or some flavor thereof), but nothing for an ODBC (RDO) connection to a NON-MSSQL database.

The problem when I update the connection string for each of the tables in the report, the report will either not run and display a window -- titled 'Datbase Login' -- that has the Server, Database, Username, and Password fields on the window, OR, it will run but not against the database that was specified at runtime (it uses the connection stored in the report).

-


Code Sample #1 (This is using the connection string, and will cause a the "Database Login" window to be displayed - the first scenario from above) -- The code samples appear to be too long to post, so I will add them as seperate replies.


rd = new ReportDocument();

// Load the report
rd.Load(fileName, OpenReportMethod.OpenReportByDefault);
rd.Refresh();

// Create the connection object
ConnectionInfo connectionInfo = CreateConnection(Server, Port, ServerNamespace, Username, Password);

// Set the connection info on each table in the report
SetDBLogonForReport(connectionInfo, rd);
SetDBLogonForSubreports(connectionInfo, rd);

private static ConnectionInfo CreateConnection(string Server, int Port, string ServerNamespace, string Username, string Password)

{
        ConnectionInfo connectionInfo = new ConnectionInfo();

        string connString = "DRIVER=InterSystems ODBC;SERVER=" + Server + ";PORT=" + Port + ";DATABASE=" + ServerNamespace + ";UID=" + Username + ";PWD=" + Password;

        connectionInfo.IntegratedSecurity = false;
        connectionInfo.UserID = Username;
        connectionInfo.Password = Password;        //In examples that I have seen, this is the actual connection string, not just the server name
        connectionInfo.ServerName = connString;          connectionInfo.DatabaseName = ServerNamespace;
        connectionInfo.Type = ConnectionInfoType.CRQE;

        return connectionInfo;

}

// Code smple continued in the next reply

Former Member
0 Kudos

Code Sample #1 (continued)


private static void SetDBLogonForReport(ConnectionInfo connectionInfo, ReportDocument reportDocument)
{
        foreach (CrystalDecisions.CrystalReports.Engine.Table table in reportDocument.Database.Tables)

        {
            TableLogOnInfo tableLogonInfo = table.LogOnInfo;
            tableLogonInfo.ConnectionInfo = connectionInfo;
            table.ApplyLogOnInfo(tableLogonInfo);
        }
}

private static void SetDBLogonForSubreports(ConnectionInfo connectionInfo, ReportDocument reportDocument)
{
        foreach (Section section in reportDocument.ReportDefinition.Sections)
        {
            foreach (ReportObject reportObject in section.ReportObjects)
            {
                if (reportObject.Kind == ReportObjectKind.SubreportObject)
                {
                    SubreportObject subreportObject = (SubreportObject)reportObject;
                    ReportDocument subReportDocument = subreportObject.OpenSubreport(subreportObject.SubreportName);
                    SetDBLogonForReport(connectionInfo, subReportDocument);
                }
            }
        }
}

Former Member
0 Kudos

Sample #2 (the same as Sample #1 above, only the ConnectionInfo method is different - which is all that I included here)


private static ConnectionInfo CreateConnection(string Server, int Port, string ServerNamespace, string Username, string Password)
{
        ConnectionInfo connectionInfo = new ConnectionInfo();

        string connString = "DRIVER=InterSystems ODBC;SERVER=" + Server + ";PORT=" + Port + ";DATABASE=" + ServerNamespace + ";UID=" + Username + ";PWD=" + Password;

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(
                          DbConnectionAttributes.QE_DATABASE_NAME, ServerNamespace));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2("QE_DatabaseType", "ODBC (RDO)"));

        DbConnectionAttributes attributes = new DbConnectionAttributes();
        attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, connString));

        attributes.Collection.Add(new NameValuePair2("Server", Server));
        attributes.Collection.Add(new NameValuePair2("UseDSNProperties", false));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.QE_LOGON_PROPERTIES, attributes));
        connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.QE_SERVER_DESCRIPTION, Server));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2("QE_SQLDB", true));

        connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.CONNINFO_SSO_ENABLED, false));

        connectionInfo.IntegratedSecurity = false;
        connectionInfo.UserID = Username;
        connectionInfo.Password = Password;
        connectionInfo.ServerName = connString;
        connectionInfo.DatabaseName = ServerNamespace;
        connectionInfo.Type = ConnectionInfoType.CRQE;

        return connectionInfo;

}

Former Member
0 Kudos

I have also tried setting connectionInfo.ServerName equal to just the real server name, but that didn't work either.

After the above operations are performed, I simply assign the returned ReportDocument object to the Crystal Viewer control. If I don't call the above functions, and just set the ReportDocument source (using whatever connection string was embedded in the report originally), the report runs as expected. Only when I attempt to update the connection string, the report will not execute. Please advise. Thanks!