Solved

# 'Failed to open a rowset' error when opening report containing subreports on different database

Posted on 2004-04-07
Medium Priority
820 Views
I use CR 9 for MS Visual Studio .NET. I have a report that has several nested subreports, and I use Oracle DB with default ODBC driver as a data source.

I wrote a thin client application using Crystal Reports Viewer component, so I create a report object, set database location for it, and then pass some parameters to the viewer and display the report. It looks like that:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

class MainForm : Form {

private CrystalDecisions.Windows.Forms.CrystalReportViewer crvViewer;
private CrystalDecisions.CrystalReports.Engine.ReportDocument rptOptions;

void btnOK_Click() {
rptOptions = new ReportDocument();

ParameterFields paramFields = new ParameterFields();
// setting the parameters - skipped
crvViewer.ParameterFieldInfo = paramFields;

ConnectionInfo connInfo = new ConnectionInfo();
connInfo.ServerName = "ODBC source name";
connInfo.DatabaseName = "";
connInfo.UserID = "user";

// cycle through report tables -- what's important is that
// subreport tables are NOT enlisted here
foreach(Table tbl in rptOptions.Database.Tables) {
TableLogOnInfo logOnInfo = (TableLogOnInfo)tbl.LogOnInfo.Clone();
logOnInfo.ConnectionInfo = connInfo;
tbl.ApplyLogOnInfo(logOnInfo);

// this trick is needed to change database location
// of ORACLE table from user1.table to user2.table
if(tbl.Location.IndexOf('.') > 0) {
string str = frmLogon.User.ToUpper() + tbl.Location.Substring(tbl.Location.IndexOf('.'));
tbl.Location = str;
}
}
rptOptions.Refresh();

crvViewer.ReportSource = rptOptions;
}
}

Should work, right? It does, of course, if I use a report which contains no subreports. Otherwise, I get this ugly error message 'Failed to open a rowset'. I managed to diagnose the problem, with the help of Tracing option of ODBC data source administrator. t shows SQL statement that was sent to Oracle server, and there it thies to SELECT from tables from schema for which it was originally created, say:

- if I login as user 'MK'
- if I created subreport logged into DB as 'MST'
- SQL statement shows 'SELECT ... FROM "MST"."TABLE"' and NOT '... FROM "MK"."TABLE"', generating 'Table or view does not exist' Oracle error.

So I'm wondering what the hell else must I do to make this damn think work? I wasted too much time on that, and I'm pretty much frustrated with this behaviour of CR, so please help me out!!
0
Question by:bobbisson
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 10780231
For reports with subreports you have to log each subreport in.  Crystal treats each subreport as a sepearate database access so you need to set them up exactly as you do the main report.

The code is something like this

foreach (Subreport S in Rpt.Options.Subreport){
foreach(Table tbl in S.rptOptions.Database.Tables) {
TableLogOnInfo logOnInfo = (TableLogOnInfo)tbl.LogOnInfo.Clone();
logOnInfo.ConnectionInfo = connInfo;
tbl.ApplyLogOnInfo(logOnInfo);

That isn't correct but you get the idea.  I don't use .Net and we use trusted logins for the reports so I don't have to code it.

mlmcc
0

LVL 1

Author Comment

ID: 10781019
I didn't mention that I tried to log into separate subreports as well, I did it like this:

foreach(ReportObject obj in rptOptions.ReportDefinition.ReportObjects) {
if(!(obj is SubreportObject)) continue;
SubreportObject subReport = (SubreportObject)obj;
ReportDocument doc = subReport.OpenSubreport(subReport.SubreportName);

// do the same thing with this subreport
foreach(Table tbl in doc.Database.Tables) {
// ...
}
}

but this doesn't work since it returns _exactly_ the same Tables collection (and same Database object) and I basically scroll through it as many times as many subreports I have...

I already downloaded the 1st service pack for CR for VS .NET 2002, heard there is a magical function 'SetDatabaseLogon', but it seems to me that the installation of this service pack has screwed up CR on my computer and I'll have to reinstall it (one more reason for frustration).

You said 'trusted connections', can I use this mechanism (and how) to connect to Oracle? Could you tell me more?
0

LVL 101

Expert Comment

ID: 10789075
I don't know if they can be used with Oracle or not.

When I create the database connection in CR one of the options on the screen where I provide the server name, database name and logon information is trusted connection.  Since I use MS SQL this may be available only with MS SQL.  We have ALL USERS set as a group in MS SQL with READ ONLY permission on the tables and execute permission on the views and sstored procedures.

mlmcc
0

LVL 1

Author Comment

ID: 11150267
the answer was exactly what mlmcc suggested - there was just a typo in my code so it didn't work as expected. this method works like this:

private CrystalDecisions.CrystalReports.Engine.ReportDocument rptOptions = null;

rptOptions = new ReportDocument();

rptOptions.SetDatabaseLogon("User","Pass","ODBCSource","");

// construct array of subreports
ArrayList rpts = new ArrayList();
foreach(ReportObject obj in rptOptions.ReportDefinition.ReportObjects) {
if(!(obj is SubreportObject)) continue;
SubreportObject subReport = (SubreportObject)obj;
ReportDocument doc = rptOptions.OpenSubreport(subReport.SubreportName);
doc.SetDatabaseLogon("User","Pass","ODBCSource","");
Debug.WriteLine("    Subreport found: {0}",subReport.Name);
}

ConnectionInfo connInfo = new ConnectionInfo();
connInfo.ServerName = "Name";
connInfo.DatabaseName = "";
connInfo.UserID = "User";

// npw cycle through all reports in list, including main report and its subreports
foreach(ReportDocument doc in rpts) {
Debug.WriteLine("Setting params for " + doc.Name);
// the next line is where I had error - I went through rptOptions.Database.Tables rather than doc.Database.Tables each time
foreach(Table tbl in doc.Database.Tables) {
TableLogOnInfo logOnInfo = (TableLogOnInfo)tbl.LogOnInfo.Clone();
logOnInfo.ConnectionInfo = connInfo;
tbl.ApplyLogOnInfo(logOnInfo);
string str = String.Empty;
if(tbl.Location.IndexOf('.') > 0) str = tbl.Location.Substring(tbl.Location.IndexOf('.') + 1);
else str = tbl.Location;
// set table location - got to do it to make things work
tbl.Location = str;
Debug.WriteLine("    {0}@{1}: {2}::{3}",
tbl.LogOnInfo.ConnectionInfo.UserID,
tbl.LogOnInfo.ConnectionInfo.ServerName,
tbl.LogOnInfo.ConnectionInfo.DatabaseName,
tbl.Location);
}
}

rptOptions.Refresh();
}

then select this report in viewer (CrystalDecisions.Windows.Forms.CrystalReportViewer), for instance

crvViewer.ReportSource = rptOptions;

enjoy.
0

LVL 101

Expert Comment

ID: 11158556

mlmcc
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
###### Suggested Courses
Course of the Month9 days, 4 hours left to enroll