Solved

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

Posted on 2004-04-07
5
809 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
  • 3
  • 2
5 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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 100

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 100

Expert Comment

by:mlmcc
ID: 11158556
Glad I could help

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

838 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