Skip to Content
0
Former Member
Feb 09, 2010 at 08:40 AM

Table Location Error Oracle and SqlServer

644 Views

<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