Skip to Content

A flip answer may be; “When ever your current code does not work and you can not figure out why not…”. And to some extent, this is true. However there are certain instances when using replace connection is highly recommended and perhaps required. Before diving in, a word on Crystal Reports SDK for .NET and InProc RAS ADK for .NET. The runtime for both SDKs is free. InProc RAS SDK is included in all “full” versions of Crystal Reports as well as in Crystal Reports for Visual Studio 2010 CRVS2010). The main differences between the two SDKs;

  1. The InProc RAS SDK includes report creation APIs (RCAPI) and thus the topic; ‘Replace Connection’
  2. The InProc RAS SDK is way more extensible and stable
  3. If you ever need to scale up your application to use Business Objects Enterprise, the process will be quite trivial

For more details see Choosing the Right Business Objects SDK for Your Needs. For an introductory read on InProc RAS SDK see How to Use The RAS SDK .NET With In-Process RAS Server.

Finally, this blog assumes that certain steps have been taken towards troubleshooting database connection issues. These steps include but are not limited to searching the KBase, forums and Developer Help files. As well, reading the article Troubleshooting Guide to Database Connectivity Issues with Crystal Reports in Visual Studio .NET Applications is also highly recommended.

Instances when Replace Connection method should be considered

  1. Reports created before Crystal Reports 9 – see more on this bellow
  2. Reports created on TTX files (If you don’t know what TTX is, you are probably not using them – again a pre CR 9 issue). If you are using TTX files, consider this Blog as well as the Blog How to use reports based on multiple TTX files in Visual Studio .NET.
  3. Reports where you need to change the database connection method. Say from ODBC to OLE DB
  4. Reports that were created on one table, but need to report off of a different table at runtime
  5. Reports created using SQL Native driver and needing to use SQL Native 10 driver at runtime
  6. Reports created off of one ODBC driver and needing to use another ODBC driver at runtime. An example would be ODBC DataDirect driver to ODBC Oracle driver
  7. The Logon Server Name in the database prompt screen is grayed out at runtime

Upgrading reports created in versions of CR older than version 9 may turn out to be more problematic than it possibly should be.

The whole Crystal Reports Database engine was rewritten in CR 9 which introduced new linking methods and new UI. As well as many  “hacks” introduced to work around issues caused by database client issues were removed, thus pushing responsibility of fixing the database client issues to the database vendor.

Since version 9, the recommendation when updating reports was to open each report in the report designer and set location using then new CRDB_*.dll DB drivers. Doing so causes the database engine to use the Verify Database function in the Designer and thus to clean up the connection info saved in the RPT file. While this process can also be done using code, the results are more consistent and successful if done in the designer.

The Replace Connection code

Here I must insert a word of thanks to Don Williams whose Replace Connection method code is used bellow:

private void ReplaceConnection_Click(object sender, EventArgs e)
{ CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); 
ISCDReportClientDocument rcd;  
rcd = rptClientDoc;   
rptClientDoc.DatabaseController.LogonEx("dwcb12003", "xtreme", "sb", "pw");   
//Create the logon propertybag for the connection we wish to use
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag(); 
logonDetails.Add("Auto Translate", -1); 
logonDetails.Add("Connect Timeout", 15); 
logonDetails.Add("Data Source", "dwcb12003"); 
logonDetails.Add("General Timeout", 0); 
ogonDetails.Add("Initial Catalog", "Orders"); 
logonDetails.Add("Integrated Security", "True"); 
logonDetails.Add("Locale Identifier", 1033); 
logonDetails.Add("OLE DB Services", -5);  
logonDetails.Add("Provider", "SQLOLEDB"); 
logonDetails.Add("Use Encryption for Data", 0); 
logonDetails.Add("Owner", "dbo"); 
// schema  
//Create the QE (query engine) propertybag with the provider details and logon property bag.
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag(); 
QE_Details.Add("Database DLL", "crdb_ado.dll"); 
QE_Details.Add("QE_DatabaseName", "Orders"); 
QE_Details.Add("QE_DatabaseType", "OLE DB (ADO)"); 
QE_Details.Add("QE_LogonProperties", logonDetails); 
QE_Details.Add("QE_ServerDescription", "dwcb12003"); 
QE_Details.Add("QE_SQLDB", "True"); 
QE_Details.Add("SSO Enabled", "False"); 
QE_Details.Add("Owner", "dbo");  
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;   
oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null); 
for (int I = 0; I<oldConnInfos.Count; I++) { oldConnInfo = oldConnInfos; 
newConnInfo.Attributes = QE_Details;
newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
}
}

The next question is; how do I know what connection to use when doing the above replace? Use KBase 1553921  – Is there a utility that would help in writing database logon code? . The KB contains a link to a utility that will write out the code for your report – in VB and C#.

When not to use replace connection?

Any time you need to do any table level modifications. For example, setting a Fully Qualified Table Name. Use SetTableLocation in this instance. Basic code is bellow:

Dim myDataSourceConnections As DataSourceConnections = northwindCustomersReport.DataSourceConnections     

For Each myConnectInfo As IConnectionInfo In  myDataSourceConnections  

If myConnectInfo.ServerName = “database”  

Then 

‘SQL Server        

  myConnectInfo.SetLogon(“username”, “password”) 

ElseIf myConnectInfo.ServerName = “database2” Then ‘Visual FoxPro         

  myConnectInfo.SetConnection(“database2”, “”, “”, “”)        

End If     

Next

More questions?

The SAP Crystal Reports, version for Visual Studio and the .NET SDK Application Development forums are monitored and you will get a reply there – 90% of the time within the same day and often within a few hours. For critical issues, I suggest creating a phone case here:

http://store.businessobjects.com/store/bobjamer/en_US/pd/productID.98078100?resid=S6I@hgoHAkEAAGsiyVkAAAAR&rests=1282226845369

Additional resources

Have a look at the following:
KB 1553921  – Is there a utility that would help in writing database logon code?
How to Use The RAS SDK .NET With In-Process RAS Server

Troubleshooting Guide to Database Connectivity Issues with Crystal Reports in Visual Studio .NET Applications
Report Application Server Database Connectivity
1553469 – How to enable Database logging in Crystal Reports for Visual Studio 2010

And of course, don’t forget to search the KBase, use the Developer Help files for your version of Crystal Reports and look at the NET RAS SDK Samples.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply