[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2004-04-07
5
Medium Priority
?
825 Views
Last Modified: 2007-12-19
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
    paramFields.AddRange(new ParameterField[] { year,month,prodUnit,exchRate,exchRateFact });
    crvViewer.ParameterFieldInfo = paramFields;

    rptOptions.Load("full\path\to\rpt\file");

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

    // 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
Comment
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
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 101

Accepted Solution

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

by:bobbisson
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

by:mlmcc
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

by:bobbisson
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;

    private void Load() {
      rptOptions = new ReportDocument();
      rptOptions.Load("path\\to\\report");

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

      // construct array of subreports
      ArrayList rpts = new ArrayList();
      rpts.Add(rptOptions);
      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","");
        rpts.Add(doc);
        Debug.WriteLine("    Subreport found: {0}",subReport.Name);
      }

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

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

by:mlmcc
ID: 11158556
Glad I could help

mlmcc
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question