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 · Accepted solution]

See if it works in my test app, you will need to modify the code so run in debug and look for the commented lines to the parameters:

how-to-parameters-in-crystal-reports-for-visual-studio-net

[former_member713422 · Accepted solution]

Thank you very much Don. I will do so and let you know if it works

[former_member11696]

Try using ReplaceConnection() and see if that works.

[former_member11696]

Ah, you are connecting to a Stored Procedure, CR requires the parameter to be set first before the log on info is set.

My test app does detect what the data source type is. If it's a SP then set the param's first before logging on.

Don

[former_member713422]

Good day Don

I have moved my parameter to be set before login.

I am still experiencing problems.

Please see attached code

Thanks

Johannes

Crystal Report Code

[former_member11696]

Check your database client, if CR can't set the log on info it just fails to connect.

Does the report work in CR Designer without prompting for log on info?

[former_member713422]

Hi Don

In the Designer it does not prompt to log in.

Please see attached link

Thanks

Johannes

Crystal Report Designer

[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

[former_member11696]

Thanks, I changed the Tag to CR for VS.

When you say changed the database do you mean the structure or to a different database Server?

CR doesn't like it when you change the database structure because we save the info in the RPT file. So you need to verify the report in CR Designer if you changed the structure.

If you are pointing the data source from say a QA DB server to a Production DB make sure the permissions are configured, User has rights to access the tables etc. CR fully supports DB Security.

Are you using SSO, WinAD or ??? to log onto the DB?

If you hard code the values does it get past the error?

Do you have subreports, if so you need to log on for each subreport also.

What happens in CR Designer when you change to the new Server?

Search for this KBA and sample app - 1553921 - Is there a utility that would help in writing database logon code?

Compare the code in the before and after Reports you manually updated the connection info.

Don

[former_member967079]

When you say changed the database do you mean the structure or to a different database Server?

It can be a different database on the same server or a different server but the structures are the same between all the database, we have checkes in our application for this.

If you are pointing the data source from say a QA DB server to a Production DB make sure the permissions are configured, User has rights to access the tables etc. CR fully supports DB Security.

We tried this with dbo access and got the same results.

Are you using SSO, WinAD or ??? to log onto the DB?

We have tried both Windows Active Directory Authentication and SQL Server Authentication and both did not work.

If you hard code the values does it get past the error?

Instead of using the variable "integratedLogin" I tried to hardcoded the value in, and got the same results when it got to the "ApplyLogOnInfo" step. It keeps over riding the integrated security to false. Admittedly, I did not try to hardcode the password for the SQL Authenticated user, but I don't see how this result would be different. At the end of the day, we need to cater for both.

Do you have subreports, if so you need to log on for each subreport also.

I do have reports with subreports as well, but to get it working, I am trying to keep it simple by using a single uncompleted report for now that gets its data from a stored procedure.

Search for this KBA and sample app - 1553921 - Is there a utility that would help in writing database logon code?

I will get my guys to search this and try it, but at the end of the day, we already have our application logging onto the database, so we expect the report to run without logging on again.

Thanks

Bradley Wheeler

[former_member11696]

What SDK package you using?

[former_member967079]

we are using the Latest SDK (13.0.29) with Visual Studio 2019 on .Net Framework 4.7.2.