Solved

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

Posted on 2004-04-07
5
795 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now