Table Location Error Oracle and SqlServer

[Former Member]

<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>

&nbsp;//this line throw an exception if the report was not designed with the

same database provider.<br />

switch (provider)<br />

{<br />

&nbsp;&nbsp;&nbsp; case Provider.SqlServer:<br />

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; crTable.Location =

String.Format(&quot;{0}.dbo.&quot;, crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename<br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; break;<br /> &nbsp;&nbsp;&nbsp; case Provider.Oracle:<br /> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; crTable.Location = String.Format(&quot;.&quot;, crConnectionInfo.DatabaseName, crTable.Name);

//schema.tablename<br />

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; break;<br />

&nbsp;&nbsp;&nbsp; default:<br />

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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(&quot;Server&quot;, this.ServerName.Text);<br />

dbAttributes.Collection.Set(&quot;Trusted_Connection&quot;, &quot;false&quot;);<br />

dbAttributes.Collection.Set(&quot;Data Source&quot;, this.ServerName.Text);<br />

dbAttributes.Collection.Set(&quot;Initial Catalog&quot;, this.DatabaseName.Text);<br />

dbAttributes.Collection.Set(&quot;Integrated Security&quot;, &quot;false&quot;);<br />

dbAttributes.Collection.Set(&quot;Provider&quot;, &quot;SQLOLEDB&quot;);<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(&quot;Database DLL&quot;, &quot;crdb_ado.dll&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_DatabaseName&quot;,

this.DatabaseName.Text);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_DatabaseType&quot;, &quot;OLE DB (ADO)&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_LogonProperties&quot;, dbAttributes);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_ServerDescription&quot;,

this.ServerName.Text);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_SQLDB&quot;, &quot;True&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;SSO Enabled&quot;, &quot;False&quot;);<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(&quot;Server&quot;, this.ServerName.Text);<br />

dbAttributes.Collection.Set(&quot;Trusted_Connection&quot;, &quot;False&quot;);<br />

dbAttributes.Collection.Set(&quot;Data Source&quot;, this.ServerName.Text);<br />

dbAttributes.Collection.Set(&quot;Provider&quot;, &quot;MSDAORA&quot;);<br />

<br />

//setup the connection <br />

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();<br />

crConnectionInfo.DatabaseName = &quot;&quot;;<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(&quot;Database DLL&quot;, &quot;crdb_oracle.dll&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_DatabaseName&quot;, &quot;&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_DatabaseType&quot;, &quot;OLE DB (ADO)&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_LogonProperties&quot;, dbAttributes);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_ServerDescription&quot;,

this.ServerName.Text);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;QE_SQLDB&quot;, &quot;True&quot;);<br />

crConnectionInfo.Attributes.Collection.Set(&quot;SSO Enabled&quot;, &quot;False&quot;);<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(&quot;{0}..&quot;, crConnectionInfo.DatabaseName, &quot;&quot;);<br /> break;<br /> case Provider.Oracle:<br /> crConnectionInfo = FixDatabaseOracle();<br /> tableLocationPattern = String.Format(&quot;.&quot;, crConnectionInfo.UserID, &quot;&quot;);<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(&quot;.dbo.&quot;, crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename<br /> break;<br /> case Provider.Oracle:<br /> crTable.Location = String.Format(&quot;.&quot;, 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

[Former Member]

<p>I thought it was solved but I have another issue with the subreports.<br />

Within a subreport the following line throws an exception:<br />

</p>

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables<i>, boTable);

Message = "Not supported within subreports."

at CrystalDecisions.ReportAppServer.SubreportWrapper.get_IsOpen()

at CrystalDecisions.CrystalReports.Engine.ReportDocument.get_IsLoaded()

at CrystalDecisions.CrystalReports.Engine.ReportDocument.get_ReportClientDocument()

<p>

Thank you very much.</p>

Edited by: jporcar on Feb 15, 2010 10:46 AM

[Former Member]

Maybe to approach this is to save out the subreport and run it through the utility. See what the database logon should be for the subreport, then incorporate that into your project (?).

Ludek