Changing the Database Connection Programmatically

[former_member967079]

Hi Guys

We have written a program that uses SAP Crystal Reports. The machine and database that we wrote the report against, the system works perfectly, but when we change the database, we are having a few issues where the system says "Error in File Report_###.rpt: Failed to load database information."

We then proceeded to debug this and picked up something strange.

Here is our code:

TableLogOnInfo tableLogOnInfo = GetSQLTableLogOnInfo(server, DataUtility.DatabaseName, integratedLogin, userID, password);
for (int i = 0; i < report.Database.Tables.Count; i++)
{
    Table table = report.Database.Tables[i];
    table.ApplyLogOnInfo(tableLogOnInfo);
}

        private static TableLogOnInfo GetSQLTableLogOnInfo(string serverName, string databaseName, bool integratedLogin, string userID, string password)
        {
            CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag connectionAttributes = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
            connectionAttributes.Add("Initial Catalog", databaseName);
            connectionAttributes.Add("Data Source", serverName);
            connectionAttributes.Add("Initial Catalog", databaseName);
            connectionAttributes.Add("User ID", userID);
            connectionAttributes.Add("Integrated Security", integratedLogin);


            DbConnectionAttributes attributes = new DbConnectionAttributes();
            attributes.Collection.Add(new NameValuePair2("QE_DatabaseName", databaseName));
            attributes.Collection.Add(new NameValuePair2("QE_LogonProperties", connectionAttributes));
            attributes.Collection.Add(new NameValuePair2("QE_ServerDescription", serverName));
            attributes.Collection.Add(new NameValuePair2("SSO Enabled", integratedLogin));


            ConnectionInfo connectionInfo = GetConnectionInfo(serverName, databaseName, integratedLogin, userID, password);
            connectionInfo.Attributes = attributes;
            connectionInfo.Type = ConnectionInfoType.SQL;


            TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
            tableLogOnInfo.ConnectionInfo = connectionInfo;
            return tableLogOnInfo;
        }

        public static ConnectionInfo GetConnectionInfo(string serverName, string databasename, bool integratedLogin, string UserID, string passWord)
        {
            ConnectionInfo connectionInfo = new ConnectionInfo();
            connectionInfo.AllowCustomConnection = true;
            connectionInfo.ServerName = serverName;
            connectionInfo.DatabaseName = databasename;
            connectionInfo.IntegratedSecurity = integratedLogin;
            if (!integratedLogin)
            {
                connectionInfo.UserID = UserID;
                connectionInfo.Password = passWord;
            }


            return connectionInfo;
        }

Now, when this code runs, it shows that the integrated login on "tableLogOnInfo" = true but when "ApplyLogOnInfo" to the table, the reportdocument shows the integrated login to false. We also tried to use a SQL user, but using the SA user, and again, the tableLogOnInfo shows a password for the SA user, but the ApplyLogOnInfo shows no password, so we can never change the database on the report.

Please help, as we don't know what more to try.

Thanks in advance

Bradley Wheeler

[former_member11696]

Thanks for the info.

It's something we changed when logging onto the DB for each report and subreport.

We used to propagate the connection info to the subreports, that obviously is a work flow error because the whole point of using subreports is to allow different data sources to be used.

So we no longer propagate thus you need to look for each subreport and set the log on info per...

Don

[former_member967079]

Hi Don,

I would understand that, if our report had sub reports. But as per the info I gave you above, we are keeping it simply for now to get it working, so there is no subreports, it is a single main report that gets its info via a stored proceedure.

Thanks

Bradley Wheeler

[former_member713422]

Hi Don I am sending this comment on Bradley Wheeler behalf. We have found out that it gives us a different problem when we run the code on another machine. The database name does not change to the required database and the integrated login stays true as required.

Please see attached video.

Thanks

Johannes

Link to Video Taken Of Code