Skip to Content

Unable to change database at runtime

We are using Crystal Reports for Visual Studio CRRuntime_32bit_13_0_22 with Visual Studio 2017 and DB2 Windows Database.

We need to change the database connection at run time (server, database, user password). Our application has sample reports that we distribute to our customers with our application.

When trying to preview the report (we are using the WPF viewer, if that makes any difference) an exception is thrown with the following message:

Unable to connect: incorrect log on parameters.

The exception is occurring at table.ApplyLogOnInfo(table.LogOnInfo);

When we load the report, before the preview, the following code is executed to change the connection information. I based it on samples from here: https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports+for+.NET+SDK+Samples?original_fqdn=wiki.sdn.sap.com#CrystalReportsfor.NETSDKSamples-Database

I am not using subreports, so I am not looping through the list of subreports.

Here is my code:

 private void ChangeConnectionInfo(ReportDocument boReportDocument)
 {


	boReportDocument.SetDatabaseLogon(databaseUser, databasePassword, databaseServer, databaseName);


	TableLogOnInfo logOnInfo;
	foreach (CrystalDecisions.CrystalReports.Engine.Table table in boReportDocument.Database.Tables)
	{
		logOnInfo = table.LogOnInfo;
		logOnInfo.ReportName = boReportDocument.Name;
		logOnInfo.ConnectionInfo.ServerName = databaseServer;
		logOnInfo.ConnectionInfo.DatabaseName = databaseName;
		logOnInfo.ConnectionInfo.UserID = databaseUser;
		logOnInfo.ConnectionInfo.Password = databasePassword;
		logOnInfo.TableName = table.Name;
		if (schemaName.IsNotNullOrEmpty())
			table.Location = $"{schemaName}.{table.Name}";
		else
			table.Location = table.Name;


		table.ApplyLogOnInfo(table.LogOnInfo);
	}
}


The one odd thing I noticed when in debug is that table is null, even though tables.count is 6 and I can look at the entire list in the debugger. I don't know if that is indicative of the problem or just a debugger anomaly.

Any help would be appreciated.

Eric

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on Apr 23 at 10:40 PM

    Using the Engine is only for changing Servers, assuming the DB has not changed.

    The Download WIKI page an app I wrote for setting logon info using ReplaceConnection(). See if that works for you.

    https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

    It the Parameter test app, half way down the page.

    Issue may be you need to convert the database name to a String().

    Don

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 30 at 09:55 PM

    Make it a loop through the tables.

    Add a comment
    10|10000 characters needed characters exceeded

    • If I loop through the Tables collection and set the logon information using Table.SetTableLocation I get an exception with the message

      Error in File ListBillTemplate_MODL 15980_3776_{0164F666-AD2D-4195-946C-08D5FDA64D7B}.rpt:

      Unable to connect: incorrect log on parameters.

      That is the only methods I could find to do this are the SetTableLocation or SetTableLocationEx methods.

      My current code is below. The table code follows the comment "// Set the new table location".

      I am obviously missing something.

      Thank you,

      Eric.

      private void ChangeConnectionInfo(ReportDocument boReportDocument)
      {
      	{
      		CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rptClientDoc = boReportDocument.ReportClientDocument;
      
      
      		//Create a new Database Table to replace the reports current table.
      		CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
      		CrystalDecisions.ReportAppServer.DataDefModel.Table subboTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
      		CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
      		CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
      		CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;
      		//                    CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
      
      
      		////Get the Database Tables Collection for your report
      		//CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
      		//boTables = rptClientDoc.DatabaseController.Database.Tables;
      
      
      		// Get the old connection info
      		oldConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);
      		
      		//boTable.ConnectionInfo = boConnectionInfo;
      
      
      		oldConnInfo = oldConnInfos[0];
      		CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonProperties =
      			new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
      		CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag oldlogonProperties =
      			new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
      
      
      		// loop through connection info and copy to new connection info, changing parameters where appropriate
      
      
      		// test test //
      		string tmpProperty;
      		dynamic tmpValue;
      		for (int i = 0; i < oldConnInfo.Attributes.Count; i++)
      		{
      			if (oldConnInfo.Attributes.PropertyIDs[i] == "QE_LogonProperties")
      			{
      				// Copy current logon properties with these changes:
      				//   Update Datasource to databaseServer
      				//   Add extendedProperties
      				oldlogonProperties = (PropertyBag)oldConnInfo.Attributes["QE_LogonProperties"];
      				for (int j = 0; j < oldlogonProperties.Count; j++)
      				{
      					if (oldlogonProperties.PropertyIDs[j] == "Data Source")
      					{
      						logonProperties.Add("Data Source", databaseServer);
      					}
      					else if (oldlogonProperties.PropertyIDs[j] == "Extended Properties")
      					{
      						// Drop this if exists. Add in below.
      					}
      					else
      					{
      						tmpProperty = oldlogonProperties.PropertyIDs[j];
      						tmpValue = oldlogonProperties[oldlogonProperties.PropertyIDs[j]];
      						logonProperties.Add(tmpProperty, tmpValue);
      					}
      				}
      				logonProperties.Add("Extended Properties", extendedProperties); // add this unconditionally
      				newConnInfo.Attributes.Add("QE_LogonProperties", logonProperties);
      			}
      			else if (oldConnInfo.Attributes.PropertyIDs[i] == "QE_ServerDescription")
      			{
      				newConnInfo.Attributes.Add("QE_ServerDescription", databaseServer);
      			}
      			else if (oldConnInfo.Attributes.PropertyIDs[i] == "QE_DatabaseName")
      			{
      				newConnInfo.Attributes.Add("QE_DatabaseName", databaseName);
      			}
      			else
      			{
      				newConnInfo.Attributes.Add(oldConnInfo.Attributes.PropertyIDs[i], oldConnInfo.Attributes[oldConnInfo.Attributes.PropertyIDs[i]]);
      			}
      
      
      		}
      
      
      		//**EDIT** Set the User Name and Password if required.
      		newConnInfo.UserName = databaseUser;
      		newConnInfo.Password = databasePassword;
      
      
      		newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindSQL;
      
      
      		rptClientDoc.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionUseDefault);
      
      
      
      
      		// Set the new table location
      		
      		CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable oldTable;
      		CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable newTable;
      		CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
      		boTables = boReportDocument.ReportClientDocument.DatabaseController.Database.Tables;
      		for (int i =0 ; i < boTables.Count; i++ )
      		{
      			oldTable = (CrystalDecisions.ReportAppServer.DataDefModel.Table)boTables[i];
      			newTable = (CrystalDecisions.ReportAppServer.DataDefModel.Table)oldTable.Clone();
      
      
      			newTable.Name = oldTable.Name;
      			newTable.Alias = oldTable.Alias;
      			newTable.ConnectionInfo = newConnInfo;
      
      
      			if (schemaName.IsNullOrEmpty())
      				newTable.QualifiedName = oldTable.Name;
      			else
      				newTable.QualifiedName = $"{schemaName}.{oldTable.Name}";
      
      
      			rptClientDoc.DatabaseController.SetTableLocation(oldTable, newTable);
      
      
      			rptClientDoc.VerifyDatabase();
      		}
      	}
      }
  • Posted on May 01 at 09:22 PM

    Preview the report without setting log on info, it should prompt for missing info.

    If that doesn't work then manually update the report and then test again and compare what it's showing with what you are setting.

    Don't Verify in code, not required.

    How are you connecting, ODBC or OLE DB?

    Server name property should either be the DSN name or the DB Server name.

    Don

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 06 at 05:45 PM

    It's OLEDB.

    I will try the other items and see what the results are.

    Thank you for your assistance.

    Eric.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.