<p>
I would like to use the <b>same report for several data base providers</b> (ODBC, OLEDB SqlServer and OLEDB Oracle).<br />
My original report is designed with ODBC by another department. When I try to change the data base provider to Oracle or SqlServer I have an error at line:<br />
<br /></p>
<code>
//this line throw an exception if the report was not designed with the
same database provider.<br />
switch (provider)<br />
{<br />
case Provider.SqlServer:<br />
crTable.Location =
String.Format("{0}.dbo.", crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename<br /> break;<br /> case Provider.Oracle:<br /> crTable.Location = String.Format(".", crConnectionInfo.DatabaseName, crTable.Name);
//schema.tablename<br />
break;<br />
default:<br />
break;<br />
}</code>
<p>Even setting the same location, like this crTable.Location = crTable.Location; the code throws an exception.
I have to change the report schema location at runtime because we have several databases (SqlServer) or schemas (Oracle) and we have to use the same report.
I use Crystal Reports Version=12.0.2000.0</p>
<br />
Here is my complete code:<br />
<code>
private CrystalDecisions.Shared.ConnectionInfo FixDatabaseSqlServer()<br />
{<br />
CrystalDecisions.Shared.DbConnectionAttributes dbAttributes = new
DbConnectionAttributes();<br />
dbAttributes.Collection.Set("Server", this.ServerName.Text);<br />
dbAttributes.Collection.Set("Trusted_Connection", "false");<br />
dbAttributes.Collection.Set("Data Source", this.ServerName.Text);<br />
dbAttributes.Collection.Set("Initial Catalog", this.DatabaseName.Text);<br />
dbAttributes.Collection.Set("Integrated Security", "false");<br />
dbAttributes.Collection.Set("Provider", "SQLOLEDB");<br />
<br />
//setup the connection <br />
CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();<br />
crConnectionInfo.DatabaseName = this.DatabaseName.Text;<br />
crConnectionInfo.ServerName = this.ServerName.Text;<br />
crConnectionInfo.UserID = this.UserID.Text;<br />
crConnectionInfo.Password = this.Password.Text;<br />
crConnectionInfo.IntegratedSecurity = false;<br />
crConnectionInfo.Attributes.Collection.Set("Database DLL", "crdb_ado.dll");<br />
crConnectionInfo.Attributes.Collection.Set("QE_DatabaseName",
this.DatabaseName.Text);<br />
crConnectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");<br />
crConnectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);<br />
crConnectionInfo.Attributes.Collection.Set("QE_ServerDescription",
this.ServerName.Text);<br />
crConnectionInfo.Attributes.Collection.Set("QE_SQLDB", "True");<br />
crConnectionInfo.Attributes.Collection.Set("SSO Enabled", "False");<br />
crConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE;<br />
crConnectionInfo.LogonProperties = dbAttributes.Collection;<br />
<br />
return (crConnectionInfo);<br />
}<br />
<br />
private CrystalDecisions.Shared.ConnectionInfo FixDatabaseOracle()<br />
{<br />
CrystalDecisions.Shared.DbConnectionAttributes dbAttributes = new
DbConnectionAttributes();<br />
dbAttributes.Collection.Set("Server", this.ServerName.Text);<br />
dbAttributes.Collection.Set("Trusted_Connection", "False");<br />
dbAttributes.Collection.Set("Data Source", this.ServerName.Text);<br />
dbAttributes.Collection.Set("Provider", "MSDAORA");<br />
<br />
//setup the connection <br />
CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();<br />
crConnectionInfo.DatabaseName = "";<br />
crConnectionInfo.ServerName = this.ServerName.Text;<br />
crConnectionInfo.UserID = this.UserID.Text;<br />
crConnectionInfo.Password = this.Password.Text;<br />
crConnectionInfo.IntegratedSecurity = false;<br />
crConnectionInfo.Attributes.Collection.Set("Database DLL", "crdb_oracle.dll");<br />
crConnectionInfo.Attributes.Collection.Set("QE_DatabaseName", "");<br />
crConnectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");<br />
crConnectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);<br />
crConnectionInfo.Attributes.Collection.Set("QE_ServerDescription",
this.ServerName.Text);<br />
crConnectionInfo.Attributes.Collection.Set("QE_SQLDB", "True");<br />
crConnectionInfo.Attributes.Collection.Set("SSO Enabled", "False");<br />
crConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE;<br />
crConnectionInfo.LogonProperties = dbAttributes.Collection;<br />
<br />
return (crConnectionInfo);<br />
}<br />
<br />
private void FixDatabase(ReportDocument report, Provider provider)<br />
{<br />
bool setLocationError = false;<br />
string tableLocationPattern = String.Empty;<br />
CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = null;<br />
<br />
switch (provider)<br />
{<br />
case Provider.SqlServer:<br />
crConnectionInfo = FixDatabaseSqlServer();<br />
tableLocationPattern = String.Format("{0}..", crConnectionInfo.DatabaseName, "");<br /> break;<br /> case Provider.Oracle:<br /> crConnectionInfo = FixDatabaseOracle();<br /> tableLocationPattern = String.Format(".", crConnectionInfo.UserID, "");<br /> break;<br /> <br /> case Provider.Undefinded:<br /> default:<br /> break;<br /> }<br /> <br /> //set database login information for the entire report object<br /> report.SetDatabaseLogon(crConnectionInfo.UserID, crConnectionInfo.Password, crConnectionInfo.ServerName, crConnectionInfo.DatabaseName);<br /> <br /> <br /> CrystalDecisions.CrystalReports.Engine.Database crDatabase = report.Database; //Set the CrDatabase Object to the Report's Database <br /> CrystalDecisions.CrystalReports.Engine.Tables crTables = crDatabase.Tables; //Set the CrTables object to the Tables collection of the Report's dDtabase<br /> <br /> foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)<br /> {<br /> TableLogOnInfo crTableLogOnInfo = null;<br /> crTableLogOnInfo = crTable.LogOnInfo;<br /> if (crTableLogOnInfo != null)<br /> {<br /> crTableLogOnInfo.ConnectionInfo = crConnectionInfo;<br /> crTable.ApplyLogOnInfo(crTableLogOnInfo);<br /> <br /> //<br /> //Set location.<br /> //<br /> try<br /> {<br /> //this line throw an exception if the report was not designed with the same database provider.<br /> switch (provider)<br /> {<br /> case Provider.SqlServer:<br /> crTable.Location = String.Format(".dbo.", crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename<br /> break;<br /> case Provider.Oracle:<br /> crTable.Location = String.Format(".", crConnectionInfo.DatabaseName,
crTable.Name); //schema.tablename<br />
break;<br />
default:<br />
break;<br />
}<br />
crTable.Location = crTable.Location;<br />
<br />
}<br />
catch (Exception ex)<br />
{<br />
setLocationError = true;<br />
//throw;<br />
}<br />
crTable.TestConnectivity();<br />
}<br />
}<br />
<br />
//call this method recursively for each subreport<br />
foreach (ReportObject reportObject in report.ReportDefinition.ReportObjects)<br />
{<br />
if (reportObject.Kind == ReportObjectKind.SubreportObject)<br />
{<br />
FixDatabase(report.OpenSubreport(((SubreportObject)reportObject).SubreportName),
provider);<br />
}<br />
}<br />
}
</code>
<p>
I tried everything I found at forums with no success.<br />
Please, Is there anyone with the same problem who had solved it?
Is it possible to have the same report and several database providers?
<br />
What have do I have to do?
Thank you very much.</p>
Edited by: jporcar on Feb 9, 2010 9:41 AM