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

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

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

These are Windows examples
http://support.businessobjects.com/communityCS/FilesAndUpdates/vbnet_win_samples.exe.asp

http://support.businessobjects.com/library/kbase/articles/c2010371.asp

Found this C#
https://www.experts-exchange.com/questions/21788412/c-database-login-window-popup-with-crystal-report.html#16289413

ASP examples with the RDC (CRAXDRT)
http://support.businessobjects.com/communityCS/FilesAndUpdates/aspxmps9.exe.asp

mlmcc

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;
            }
      }

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

This may also help

https://www.experts-exchange.com/questions/21813385/change-Crystal-Report-database-at-Runtime.html

mlmcc

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());
                  }
            }
=============================================================

Do you have data saved with the report?

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

mlmcc

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

Here is another C# example and question that I found

https://www.experts-exchange.com/questions/21788412/c-database-login-window-popup-with-crystal-report.html#16289413

mlmcc

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());
                  }
            }

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

How is this code called?

mlmcc

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

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

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

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