Solved

CR9.2 + C#. Change logon info on rpt + subrpt. Each area has >1 tables...

Posted on 2006-06-14
18
552 Views
Last Modified: 2008-02-01
(on behalf of Mik)

Hi,
I have a C# (dotNet v1.1) app that is designed to view any CR report that is dropped onto it.  It is using the CR9.2 Dev version of the viewer (COM).

For testing, I've created a report that uses DAO (OLEDB an other test rpts to follow) to connect to one of two copies made from one MDB file. The only diff between the two MDBs being some data, so that I can confirm which DB the report is connected to. Otherwise, both MDBs are the same.

The report consists of:
- 1 main report pointing to DB1, using 2 tables, linked in CR
- 2 subreports, each pointing to DB2, using (the same) 2 tables, linked in CR
(The tables are Tickets and TickTech. The MDBs are  DB1 = TicketReporting.mdb and DB2 = TicketReporting2.mdb)

Here are the test scenarios:
- When run in CR itself, the data looks correct. Main rpt reports from DB1 and both subreports show data from DB2.
GOOD

- When run in my viewer, with no db over-rides, all looks well. Main rpt reports from DB1 and both subreports show data from DB2. GOOD

- When my viewer overrides the db of the main report (ie uses SetLogOnInfo() or such), all is well.
GOOD

I get inconsistent results when I try to override the db to be used by the tables in the subreports even though the code used is the same as that of the main report.

Here are snippets of the code:


First, the following code gathers all reports and subreports contained in the one RPT.
===================================
private struct ReportSections
{
      internal string subRptName;
      internal CRAXDRT.Report subRpt;
}

private ArrayList GetAllSubreports(CRAXDRT.Report rpt, bool includeMainRpt)
{
      ArrayList al = new ArrayList();
      // If we also want the main report in the arraylist...
      if (includeMainRpt)
      {
            ReportSections rs = new ReportSections();
            rs.subRpt = rpt;
            rs.subRptName = "";
            al.Add(rs);
      }

      foreach (CRAXDRT.Section sect in rpt.Sections)
      {
            foreach (object robj in sect.ReportObjects)
            {
                  if (robj is CRAXDRT.SubreportObject )
                  {
                        CRAXDRT.SubreportObject subrobj = (CRAXDRT.SubreportObject)robj;
                        CRAXDRT.Report subrpt = subrobj.OpenSubreport();

                        ReportSections rs = new ReportSections();
                        rs.subRpt = subrpt;
                        rs.subRptName = subrobj.SubreportName;

                        al.Add(rs);
                  }
            }
      }

      return al;
}
===================================

then for each entry in the above array, I have done the following

===================================
foreach (CRAXDRT.DatabaseTable tbl in rpt.Database.Tables )
      {
            // each attempt shown was done on its own. They are shown here together for brevity only!
            // db=.\TicketReporting.mdb or .\TicketReporting2.mdb

            // attempt #1
            tbl.SetLogOnInfo("", db, "", "");

            // attempt #2
            cp = (CRAXDRT.ConnectionProperty)cps["Database Name"];
            cp.Value = db;

            // attempt #3
            tbl.Location = tbl.Location;      // elsewhere on this site, this was suggested as an option
            tbl.SetLogOnInfo("", db, "", "");
            tbl.Location = tbl.Name;      // elsewhere on this site, this was suggested as an option
      }
===================================

Cheers,
Nick

ps: This is my first ever question here, so I am not sure I have allocated points correctly. Pls let me know if they are wrong.
0
Comment
Question by:v07m9s
  • 10
  • 8
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 16925306
This is for VB.Net but it may help.. There is a subreport logon available.  The code files will open in Notepad

http://support.businessobjects.com/communityCS/FilesAndUpdates/vbnet_web_samples.exe.asp

mlmcc
0
 

Author Comment

by:v07m9s
ID: 16929607
Hi mlmcc,

Thanks for replying.
Alas, the samples you mentioned use the .NET CR libraries, whereas I am using the COM viewer + CRAXDRT.

Cheers,
Nick
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16929804
0
 

Author Comment

by:v07m9s
ID: 16929838
Having tried a few different ways of solving this, here's where I am at:
- The code below is a condensed version of what I use.

- I run it without any over-rides (ie the code does not assign any of the ConnectionPropertyValues), it works fine. I pressed REFRESH on the viewer (a number of times) and all stays well.

- I then change the external config file to override the 'Database Name' property. When I run the report nothing seems to have changed. Then, when I press REFRESH, I get inconsistent results (though the lest 3 times I ran it, it behaved itself!!!). It looks like whatever connection I set the last table in the last subreport, is what becomes set. I cannot verify this quite yet.
This whole thing is quite perplexing!


      CRAXDRT.Application crApp = new CRAXDRT.Application();
      CRAXDRT.Report rpt = crApp.OpenReport(reportFilename, CROpenReportMethod.crOpenReportByDefault);

      // Process the tables of the main report...
      SetAllTableLogInfo(rpt, "");

      // Get a list of all the subreports (just their names). Do not include main report in that list
      _rib.subReports = GetAllSubreports(rpt, false);
      foreach (ReportSections rs in _rib.subReports)
      {
            string subRptName = rs.subRptName;
            CRAXDRT.Report subrpt = rpt.OpenSubreport(subRptName);
            
            // Set logon info for all tables in subreport.
            SetAllTableLogInfo(subrpt, subRptName);
      }

      private void SetAllTableLogInfo(CRAXDRT.Report rpt, string subReportName)
      {
            foreach (CRAXDRT.DatabaseTable tbl in rpt.Database.Tables )
            {
                  SetTableLogInfo(tbl, subReportName);
            }
      }

      // 'source' is an object that references an XML config file...edited out for simplicity
      private void SetTableLogInfo(CRAXDRT.DatabaseTable tbl, string subReportName)
      {
            string tblName = tbl.Name;
            
            if (tbl.DllName == "crdb_dao.dll")
            {
                  db = GetValueFromRptConfig(source, "DB", subReportName, tblName);
                  SetConnectionPropertyValue(cps, "Database Name", db);

                  tmp = GetValueFromRptConfig(source, "DatabasePWD", subReportName, tblName);
                  SetConnectionPropertyValue(cps, "Database Password", tmp);

                  uid = GetValueFromRptConfig(source, "UID", subReportName, tblName);
                  SetConnectionPropertyValue(cps, "Session UserID", uid);

                  pwd = GetValueFromRptConfig(source, "PWD", subReportName, tblName);
                  SetConnectionPropertyValue(cps, "Session Password", pwd);

                  tmp = GetValueFromRptConfig(source, "SystemDatabase", subReportName, tblName);
                  SetConnectionPropertyValue(cps, "System Database Path", tmp);
                        
                  tbl.Location = tblName;
            }
      }

      private void SetConnectionPropertyValue(CRAXDRT.ConnectionProperties cps, string propName, string propValue)
      {
            CRAXDRT.ConnectionProperty cp;

            if (propValue.Length != 0)
            {
                  propValue = propValue.Replace("<blank>", "");
                  cp = (CRAXDRT.ConnectionProperty)cps[propName];
                  cp.Value = propValue;
            }
      }

0
 

Author Comment

by:v07m9s
ID: 16929864
Hi mlmcc,

Thanks again for replying. I seem to be doing all the right things but it still is inconsistent. (see tmp reply above. I need to run more tests to confirm above results)

I have switched to Crystal 11 Dev, also using the RDC (CRAXDRT). All the answers (to other peoples' questions)  I have seen so far on this site, seem to be pointing to pretty much what I have done.

It would *seem* that the issue comes about because I have more than one subreport.

It is getting late and I've run out of coffee and chocolate. (!)
I'll sleep over it and try again tomorrow.

Once again, thanks for taking the time. Hopefully, we'll nail this sucker down!
Cheers,
Nick
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16929970
0
 

Author Comment

by:v07m9s
ID: 16960405
Attempt #2,145 :-)

I have now created another report, which  uses 2 tables from TicketReporting.mdb in the main report.
It also has 2 subreports, both of which use the equivalent tables from TicketReporting2.mdb  (<== Note the 2 in the filename)

The report now uses ADO/OLEDB to connect to the MDB instead of DAO.
Crystal Repor 11 Dev is used, along with Visual Studion 2003.

I have now reduced the code to what is shown below.
When I execute, I do not get the expected results and, at least once in the last 10 minutes, I got differing results, every time I run the test.

I must be missing something, I just can't see it!
Help!

=============================================================
                  try
                  {
                        CRAXDRT.Application crApp = new CRAXDRT.Application();
                        _rpt = crApp.OpenReport(reportFilename, CROpenReportMethod.crOpenReportByDefault);
                        _rpt.DiscardSavedData();
                        
// Lets force main report to DB #2, subrpt1 to DB #1 and subrpt2 to DB#2
                        string db = @"S:\software development\dotNET\Kristie\tests\TicketReporting2.mdb";
                        SetDB(_rpt, "", db);

                        db = @"S:\software development\dotNET\Kristie\tests\TicketReporting.mdb";
                        CRAXDRT.Report rpt = _rpt.OpenSubreport("subrpt1");
                        SetDB(rpt, "subrpt1", db);

                        db = @"S:\software development\dotNET\Kristie\tests\TicketReporting2.mdb";
                        rpt = _rpt.OpenSubreport("subrpt2");
                        SetDB(rpt, "subrpt2", db);
                  }
                  finally
                  {
                        try
                        {
                              crViewer1.ReportSource = _rpt;
                              crViewer1.ViewReport();

                              while (crViewer1.IsBusy)
                              {
                                    System.Windows.Forms.Application.DoEvents();
                              }
                        
                              ResizeViewerControl();
                              this.Show();
                              System.Windows.Forms.Application.DoEvents();
                        }
                        catch (Exception ex)
                        {
                              new Helper.Dialogs().ShowError(ex.ToString());
                        }
                  }
            }

            private void SetDB(CRAXDRT.Report rpt, string subReportName, string db)
            {
                  try
                  {
                        foreach (CRAXDRT.DatabaseTable tbl in rpt.Database.Tables )
                        {
                              CRAXDRT.ConnectionProperties cps = tbl.ConnectionProperties;
                              SetConnectionPropertyValue(cps, "Data Source", db);
                              tbl.Location = tbl.Location;
                        }
                  }
                  catch (Exception ex)
                  {
                        new Helper.Dialogs().ShowError(ex.ToString());
                  }
            }

            private void SetConnectionPropertyValue(CRAXDRT.ConnectionProperties cps, string propName, string propValue)
            {
                  CRAXDRT.ConnectionProperty cp;

                  try
                  {
                        if (propValue.Trim().Length != 0)
                        {
                              propValue = propValue.Replace("<blank>", "");
                              cp = (CRAXDRT.ConnectionProperty)cps[propName];
                              cp.Value = propValue;
                        }
                  }
                  catch (Exception ex)
                  {
                        new Helper.Dialogs().ShowError(ex.ToString());
                  }
            }
=============================================================




0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16964927
Do you have data saved with the report?

What happens if you refresh the report when you view it.

mlmcc
0
 

Author Comment

by:v07m9s
ID: 16966204
Hi mlmcc,

I execute a  
      _rpt.DiscardSavedData();
early on in the code, in order to avoid that problem.
I have removed the Refresh button from the viewer, just to make sure I am not tripping over anything.
I have a Refresh button of my own that simply re-executes the above code. Even that has sometimes produced differing results!

I've scanned through this web site and came across a msg that talks about the need to execute
    tbl.Location = tbl.Location;
in order for all the newly-set properties to take effect. This re-enforces something I had read in one of the CR docs.

It feeld that I need something else on top of that in order to actually achieve what I need.
I've been developing software for rather a long time; I've been using Crystal since v4.5 (if my memory serves me right).
I've never had quite such difficulty with it; though, this is the first time I am trying to redirect reports with 2 or more subreports, using the dotNOT version (Developer edition - not the CR9.2 that comes bundled with VS2003)

It is quite frustrating, especially when I just know that the fix will be something very simple!  :-)

Cheers,
Nick
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
ID: 16973642
Here is another C# example and question that I found

http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21788412.html#16289413

mlmcc
0
 

Author Comment

by:v07m9s
ID: 16973915
Hi mlmcc,

I am affraid that didn't help either. :-(

I have made some progress though.
I narrowed the tests down to the DAO report (though I do have to get this working with ODBC and ADO...).
As a reminder, this report has the following: 1 main section, 2 subreports. All of those use the same 2 tables from either DB1 or DB2. DB1 and DB2 are copies of the same database, with a few records changed so that I can tell at a glance which db the data is coming from.

(As I have discovered)It is important to note that my report only had *2* connections in it. One for DB1 and one for DB2. However, it had *3* subreports.
I've just created a 3rd connection in the report, saved it and fired off my viewer and, it would appear, it is now working.

The test is using hard-coded db names, as can be seen below, but it produces the correct results. I will confirm that this is indeed working a little later.

One thing to note is that I was unable to create 3 connections in CR designer that point to the same db, so I instead created a 3rd copy of the db and set a connection to it. The aim here was to see if the issue was with not having enough distinct connections in the CR report design.

Of course, the next question is: How do I handle a report thrown to my viewer that has one connection and 3 subreports and the user wants them to each point to a different instance of the db.  I'll take care of that later on...

For now, I'll go for a walk and come back to this a bit later. Hopefully, this discovery will allow me to proceed with the design and testing of this app.



                        CRAXDRT.Application crApp = new CRAXDRT.Application();
                        _rpt = crApp.OpenReport(reportFilename, CROpenReportMethod.crOpenReportByDefault);
                        _rpt.DiscardSavedData();
                        
                        string db = @"S:\software development\dotNET\Kristie\tests\TicketReporting.mdb";
                        SetDB(_rpt, "", db);

                        db = @"S:\software development\dotNET\Kristie\tests\TicketReporting2.mdb";
                        CRAXDRT.Report rpt = _rpt.OpenSubreport("subrpt1");
                        SetDB(rpt, "subrpt1", db);

                        db = @"S:\software development\dotNET\Kristie\tests\Ticket1.mdb";
                        rpt = _rpt.OpenSubreport("subrpt2");
                        SetDB(rpt, "subrpt2", db);

============
            private void SetDB(CRAXDRT.Report rpt, string subReportName, string db)
            {
                  try
                  {
                        foreach (CRAXDRT.DatabaseTable tbl in rpt.Database.Tables )
                        {
                              CRAXDRT.ConnectionProperties cps = tbl.ConnectionProperties;
                              string tableName = tbl.Name;
                              string tableDLL = tbl.DllName;

                              switch(tableDLL.ToLower())
                              {
                                    case "crdb_dao.dll":
                                          SetConnectionPropertyValue(cps, "Database Name", db);
                                          break;

                                    case "crdb_ado.dll":
                                          SetConnectionPropertyValue(cps, "Provider", "Microsoft.Jet.OLEDB.4.0");
                                          SetConnectionPropertyValue(cps, "Data Source", db);
                                          break;

                                    default:
                                          new Helper.Dialogs().ShowError("Unknown table dllName: " + tableDLL);
                                          break;
                              }

                              tbl.Location = tbl.Location;
                              tbl.TestConnectivity();
                        }
                  }
                  catch (Exception ex)
                  {
                        new Helper.Dialogs().ShowError(ex.ToString());
                  }
            }



0
 

Author Comment

by:v07m9s
ID: 16989182
The problem

    * Need to be able to change, at runtime, the location of any table in a report and subreport.
    * Using Crystal Dev 9.2 and Visual Studio 2003 C#


TEST #1
For the purposes of this test, a report has been created that consists of:
1 main report pointing to database DB1. It has 2 tables, t1 and t2, linked in CR on a key field.
1 subreport, pointing to DB1. It has 2 tables, t1 and t2, linked in CR on a key field. They are not linked to the main report
1 subreport, pointing to DB1. It has 2 tables, t1 and t2, linked in CR on a key field. They are not linked to the main report

For the purposes of this test, the report is using DAO to connect to the database.
The report contains 2 connections to DB1. One in the main section and one in the subreports.

Running the report in Crystal, I see data from DB1 in all 3 sections, as expected.

Running the report via my viewer and specifying
DB2 for main section,
DB3 for subrpt1, and
DB1 for subrpt2
produces output that shows data from DB2 in the main section and from DB3 in both subreports. Not what I want.

TEST #2
For the purposes of this test, a report has been created that consists of:
1 main report pointing to database DB1. It has 2 tables, t1 and t2, linked in CR on a key field.
1 subreport, pointing to DB2. It has 2 tables, t1 and t2, linked in CR on a key field. They are not linked to the main report
1 subreport, pointing to DB3. It has 2 tables, t1 and t2, linked in CR on a key field. They are not linked to the main report

The report contains 3 connections, one each to DB1, DB2, DB3. This means that each section is effectively using the same connection.

Running the report in Crystal, I see data from db1, followed by db2 followed by db3...

Running the report via my viewer and specifying
DB3 for main section,
DB1 for subrpt1, and
DB2 for subrpt2
produces output that shows data from DB3 in the main section, DB1 in subrp1 and DB2 in subrpt2. Exactly what I want.

Re-running the report via my viewer and specifying
DB3 for main section,
DB1 for subrpt1.table1, and
DB3 for subrpt1.table2, and
DB2 for subrpt2
produces output that shows data from DB3 in the main section, DB1 in subrp1 and DB1 in subrpt2. Not exactly what I want.


TEST #3
Same as above, except that subreport2 has been modified to use 2 connections, one for each table. (as opposed to just one, above).

Now, re-running the report via my viewer and specifying
DB3 for main section,
DB1 for subrpt1.table1, and
DB3 for subrpt1.table2, and
DB2 for subrpt2
produces output that shows data from DB3 in the main section, DB1 in subrp1.table1, DB2 in subrpt.table2 and DB3 in subrpt2. Exactly what I want.


Conclusion
It would appear that you can only specify as many different data sources, at run time, as there were defined at design time.
If you look at test#3 above, the only reason it works is because, during design, I used a separate connection for subrpt1.table2.

The problem
The problem is that, while it is easy to do this while testing and having the luxury of creating 3 copies of a small db, just so I can create 3 different connections at design time, I am not sure how this can be done for, say, Oracle or SQLServer databases.


Any ideas?



SOURCE CODE

// This works, so long as the above comments are taken into account.

// Heavily edited to only show relevant bits!
// This code is a combination of  quite a few bits found in this forum and others. I claim no credit for it, other than the errors :-)
        private void SetTableLogInfo(CRAXDRT.DatabaseTable tbl)
        {
            string tblName = tbl.Name;
            CRAXDRT.ConnectionProperties cps = null;

            try
            {
                cps = tbl.ConnectionProperties;

                // Get name and location info for the table.
                string tableName = tbl.Name;
                string tableLoc = tbl.Location;
                string tableType = (( CRAXDRT.ConnectionProperty)cps["Database Type"]).Value.ToString();

                // switch to ADO drivers
                if (tbl.DllName.ToLower() != "crdb_ado.dll")
                {
                    tbl.DllName = "crdb_ado.dll";
                }

                // Remove all current connection properties. We will be setting our own from scratch.
                cps.DeleteAll ();

                switch (tableType.ToLower())
                {
                    case "access":
                        cps.Add("Provider", "Microsoft.Jet.OLEDB.4.0");
                        cps.Add("Database Type", "Access");

                        cps.Add("Data Source", db);
                        cps.Add("Jet Database Password", dbPwd);
                        cps.Add("User ID", uid);
                        cps.Add("Password", pwd);
                        cps.Add("Jet System Database", sysDb);

                        // !!!!!!!!!!!!!!!!! THIS IS A VERY IMPORTANT STEP! Worth shouting for!
                        // This is what causes CR to'activate' the above properties
                        // Writing a value into 'tbl.Location' is what does it...
                        tbl.Location = tableName;
                        break;

                    case "sqloledb":
                        // blah blah
                        break;

                    default:
                        // blah blah
                        break;
                }
            }
            catch (Exception ex)
            {
                  // blah blah
            }
        }
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16989637
How is this code called?

mlmcc
0
 

Author Comment

by:v07m9s
ID: 16989695
Hi mlmcc,

Here is a piece of that code:
                    // defined a little further up....
                    //                                 CRAXDRT.Report _rpt = null;

                    CRAXDRT.Application crApp = new CRAXDRT.Application();
                    _rpt = crApp.OpenReport(reportFilename, CROpenReportMethod.crOpenReportByDefault);
                    _rpt.DiscardSavedData();
                   
                    string db = @"S:\software development\dotNET\Kristie\tests\TicketReporting.mdb";
                    SetDB(_rpt, db);

                    db = @"S:\software development\dotNET\Kristie\tests\TicketReporting2.mdb";
                    CRAXDRT.Report rpt = _rpt.OpenSubreport("subrpt1");
                    SetDB(rpt, db);

                    db = @"S:\software development\dotNET\Kristie\tests\Ticket1.mdb";
                    rpt = _rpt.OpenSubreport("subrpt2");
                    SetDB(rpt, db);
                    .....
                    .....
                    .....
                   }


private void SetDB(CRAXDRT.Report rpt, string db)
{
     foreach (CRAXDRT.DatabaseTable tbl in rpt.Database.Tables )
                    {
                           SetTableLogInfo(tbl, db);
                    }
}

// Pls note that SetTableLogInfo(), is in fact declared as
                     private void SetTableLogInfo(CRAXDRT.DatabaseTable tbl, string db)
despit the example shown in the 2nd last msg, which shows it as
                     private void SetTableLogInfo(CRAXDRT.DatabaseTable tbl)    // ie, missing the 'string db'

Cheers,
Nick
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16989858
Nothing jumps out.  I am not that familiar with C# but I do know C and C++ so the code makes sense.

Are obects passed by reference? or are they read-only?

mlmcc
0
 

Accepted Solution

by:
v07m9s earned 0 total points
ID: 16990050
Hi mlmmc,

They are R/W and by ref...

The actual code shown in the last few msgs now appears to work. I think it probably always did, but I was getting messed up in testing, because of what I think is the 'connections' issue.
Because I had only one connection in the report for each section, trying to set one of the tables in that section to a different source, caused everyting else to screw up. (Don't know why)

Looking at this thread and all the answers etc, one thing did become re-enforced, namely that it is important to do a
     tbl.Location = tblName   (or tbl.Location = tblLocation)

I will keep looking to see if I can find a way of creating connections at runtime...

Cheers,
Nick


0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16998326
Nick - I can close this for you if you wish and refund the points.  The discussion is useful for others to search but if none of the comments is really the answer then it is many times better to just close it and refund the points.

mlmcc
0
 

Author Comment

by:v07m9s
ID: 16998502
Hi mlmcc,
It's probably a good idea to close it. I want to thank you for all your help, it was very much appreciated!

I will post another question describing the connections issue. This way it will be a more focused question on a specific problem.

Cheers,
Nick
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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: …

743 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

12 Experts available now in Live!

Get 1:1 Help Now