Server Error in '/CrystalReportsWebSite3' Application.

Former Member · ‎2010 Feb 09

I would like to use the same report for several data base providers (ODBC, OLEDB SqlServer and OLEDB Oracle).

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:

<code>

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

same database provider.

switch (provider)

{

case Provider.SqlServer:

        crTable.Location =

String.Format("{0}.dbo.", crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename         break;     case Provider.Oracle:         crTable.Location = String.Format(".", crConnectionInfo.DatabaseName, crTable.Name);

//schema.tablename

break;

default:

break;

}</code>

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

Here is my complete code:

<code>

private CrystalDecisions.Shared.ConnectionInfo FixDatabaseSqlServer()

{

CrystalDecisions.Shared.DbConnectionAttributes dbAttributes = new

DbConnectionAttributes();

dbAttributes.Collection.Set("Server", this.ServerName.Text);

dbAttributes.Collection.Set("Trusted_Connection", "false");

dbAttributes.Collection.Set("Data Source", this.ServerName.Text);

dbAttributes.Collection.Set("Initial Catalog", this.DatabaseName.Text);

dbAttributes.Collection.Set("Integrated Security", "false");

dbAttributes.Collection.Set("Provider", "SQLOLEDB");

//setup the connection

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();

crConnectionInfo.DatabaseName = this.DatabaseName.Text;

crConnectionInfo.ServerName = this.ServerName.Text;

crConnectionInfo.UserID = this.UserID.Text;

crConnectionInfo.Password = this.Password.Text;

crConnectionInfo.IntegratedSecurity = false;

crConnectionInfo.Attributes.Collection.Set("Database DLL", "crdb_ado.dll");

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseName",

this.DatabaseName.Text);

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");

crConnectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);

crConnectionInfo.Attributes.Collection.Set("QE_ServerDescription",

this.ServerName.Text);

crConnectionInfo.Attributes.Collection.Set("QE_SQLDB", "True");

crConnectionInfo.Attributes.Collection.Set("SSO Enabled", "False");

crConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE;

crConnectionInfo.LogonProperties = dbAttributes.Collection;

return (crConnectionInfo);

}

private CrystalDecisions.Shared.ConnectionInfo FixDatabaseOracle()

{

CrystalDecisions.Shared.DbConnectionAttributes dbAttributes = new

DbConnectionAttributes();

dbAttributes.Collection.Set("Server", this.ServerName.Text);

dbAttributes.Collection.Set("Trusted_Connection", "False");

dbAttributes.Collection.Set("Data Source", this.ServerName.Text);

dbAttributes.Collection.Set("Provider", "MSDAORA");

//setup the connection

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();

crConnectionInfo.DatabaseName = "";

crConnectionInfo.ServerName = this.ServerName.Text;

crConnectionInfo.UserID = this.UserID.Text;

crConnectionInfo.Password = this.Password.Text;

crConnectionInfo.IntegratedSecurity = false;

crConnectionInfo.Attributes.Collection.Set("Database DLL", "crdb_oracle.dll");

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseName", "");

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");

crConnectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);

crConnectionInfo.Attributes.Collection.Set("QE_ServerDescription",

this.ServerName.Text);

crConnectionInfo.Attributes.Collection.Set("QE_SQLDB", "True");

crConnectionInfo.Attributes.Collection.Set("SSO Enabled", "False");

crConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE;

crConnectionInfo.LogonProperties = dbAttributes.Collection;

return (crConnectionInfo);

}

private void FixDatabase(ReportDocument report, Provider provider)

{

bool setLocationError = false;

string tableLocationPattern = String.Empty;

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = null;

switch (provider)

{

case Provider.SqlServer:

crConnectionInfo = FixDatabaseSqlServer();

tableLocationPattern = String.Format("{0}..", crConnectionInfo.DatabaseName, ""); break; case Provider.Oracle: crConnectionInfo = FixDatabaseOracle(); tableLocationPattern = String.Format(".", crConnectionInfo.UserID, ""); break; case Provider.Undefinded: default: break; } //set database login information for the entire report object report.SetDatabaseLogon(crConnectionInfo.UserID, crConnectionInfo.Password, crConnectionInfo.ServerName, crConnectionInfo.DatabaseName); CrystalDecisions.CrystalReports.Engine.Database crDatabase = report.Database; //Set the CrDatabase Object to the Report's Database CrystalDecisions.CrystalReports.Engine.Tables crTables = crDatabase.Tables; //Set the CrTables object to the Tables collection of the Report's dDtabase foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables) { TableLogOnInfo crTableLogOnInfo = null; crTableLogOnInfo = crTable.LogOnInfo; if (crTableLogOnInfo != null) { crTableLogOnInfo.ConnectionInfo = crConnectionInfo; crTable.ApplyLogOnInfo(crTableLogOnInfo); // //Set location. // try { //this line throw an exception if the report was not designed with the same database provider. switch (provider) { case Provider.SqlServer: crTable.Location = String.Format(".dbo.", crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename break; case Provider.Oracle: crTable.Location = String.Format(".", crConnectionInfo.DatabaseName,

crTable.Name); //schema.tablename

break;

default:

break;

}

crTable.Location = crTable.Location;

}

catch (Exception ex)

{

setLocationError = true;

//throw;

}

crTable.TestConnectivity();

}

//call this method recursively for each subreport

foreach (ReportObject reportObject in report.ReportDefinition.ReportObjects)

{

if (reportObject.Kind == ReportObjectKind.SubreportObject)

{

FixDatabase(report.OpenSubreport(((SubreportObject)reportObject).SubreportName),

provider);

}

}

</code>

I tried everything I found at forums with no success.

Please, Is there anyone with the same problem who had solved it?

Is it possible to have the same report and several database providers?

What have do I have to do?

Thank you very much.

Edited by: jporcar on Feb 9, 2010 9:41 AM

Former Member · ‎2010 Feb 09

I'd simplify things significantly.

1) do not use any subreports

2) create a report on MS SQL, make sure the code you use connects there

3) create a report off of Oracle, make sure the code you use connects there

4) the reports above should be one table, one field

5) the reports should be using the same type of connectivity (ODBC, or OLEDB)

6) reports using ODBC can not be made to go to an OLEDB datasource with the CR APIs for .NET. to change from ODBC to OLEDB and vice versa, you'd need to use inprocRas

7) once you have both of the reports working, try to switch them - first in the designer, then at runtime (e.g.; if these can not be changed in the designer, runtime will do the same)

Your Oracle connectivity code would look something like this:

Dim connection As ConnectionInfo 
connection = New ConnectionInfo() 
With connection 
.ServerName = Our Server Name Here 
.UserID = Our User ID Here 
.Password = Our Password Here 
.DatabaseName = Our Database Name Here 
.Type = ConnectionInfoType.SQL 
End With 

Dim CRTableLogOnInfo As TableLogOnInfo 
For Each Tbl As Table In Report.Database.Tables 
CRTableLogOnInfo =Tbl.LogOnInfo 
CRTableLogOnInfo.ConnectionInfo = connection 
Tbl.ApplyLogOnInfo(CRTableLogOnInfo) 
Tbl.Location = Our Schema Here & "." & Tbl.Location 
Next

Note that this is different than MS SQL... Also, note that Oracle is case sensitive.

Ludek

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Table Location Error Oracle and SqlServer

Know the answer?

Need more details?

Server Error in '/CrystalReportsWebSite3' Application.

+No se pudo abrir la conexión.<br />

Table Location Error Oracle and SqlServer

Know the answer?

Need more details?

Server Error in &#39;/CrystalReportsWebSite3&#39; Application.

+No se pudo abrir la conexión.<br />

Server Error in '/CrystalReportsWebSite3' Application.