Skip to Content
0

Changing connection for report and subreports on runtime

Oct 26, 2016 at 02:14 PM

58

avatar image

Hello, I know there are plenty of topics like this, but I still can't figure out what am i doing wrong. I have found answers like this:

Changing connection

but in my case i do not have DataDefModel in ReportAppServer so i can't really use it. I'm using version 13.0.2000.0.

The problem is that when i'm trying to change the connection like this (C#):

           foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
            {
                TableLogOnInfo tableLogonInfo = table.LogOnInfo;
                tableLogonInfo.ConnectionInfo = crConnectionInfo;
                table.ApplyLogOnInfo(tableLogonInfo);
            }

it updates only username and password. I have even tried to create same ODBC connection with same password and database on my development machine but it does not work.

How can i update the report and all subreports to use specified connection? It is worth to add that main report is using stored procedure dbo.sp1 and subreport is using stored procedure dbo.sp2 and somehow i have to make it work.

Best regards,

Mateusz

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Don Williams
Oct 27, 2016 at 01:25 PM
0

Hi Mateusz,

I changed your primary Tag to CR for VS.

Try my Parameter Doc also, it has abilities to log on and set the subreport log on also:

https://blogs.sap.com/2016/02/17/how-to-parameters-in-crystal-reports-for-visual-studio-net/

Don

Share
10 |10000 characters needed characters left characters exceeded
Brian Dong Oct 26, 2016 at 06:42 PM
0

Hi Mateusz,

Because you are using LogOnInfo, you need to call this for the main report but you also have to loop through each of the subreports and call LogOnInfo and have each subreport log on as well.

The reason for this is because it is possible to have a main report and a subreport connect to different databases. So each subreport must be handled individually.

Hope this helps,

Brian

Share
10 |10000 characters needed characters left characters exceeded
Mateusz Świerkosz Oct 27, 2016 at 04:35 PM
0

Hi Brian, thanks for your time.

I know that I have to do this and currently I am trying to:

TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
            ConnectionInfo crConnectionInfo = new ConnectionInfo();


            crConnectionInfo.Type = ConnectionInfoType.SQL;
            crConnectionInfo.IntegratedSecurity = false;


            crConnectionInfo.ServerName = "SQL2012";
            crConnectionInfo.DatabaseName = "db2";
            crConnectionInfo.UserID = "sa";
            crConnectionInfo.Password = "";


            Sections ReportSections = rpt.ReportDefinition.Sections;


            ReportObjects crReportObjects;
            SubreportObject crSubreportObject;
            ReportDocument crSubreportDocument;
            Database crDatabase;
            Tables crTables;






            foreach (Section section in ReportSections)
            {
                crReportObjects = section.ReportObjects;


                foreach (ReportObject crReportObject in crReportObjects)
                {
                    if (crReportObject.Kind != ReportObjectKind.SubreportObject)
                        continue;


                    crSubreportObject = (SubreportObject)crReportObject;
                    crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);
                    crDatabase = crSubreportDocument.Database;
                    crTables = crDatabase.Tables;


                    foreach (Table crTable in crTables)
                    {
                        TableLogOnInfo crTableLogOnInfo = crTable.LogOnInfo;
                        crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
                        crTable.ApplyLogOnInfo(crTableLogOnInfo);
                    }
                }
            }




            //Main
            Tables tables = rpt.Database.Tables;
            foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
            {
                TableLogOnInfo tableLogonInfo = table.LogOnInfo;
                tableLogonInfo.ConnectionInfo = crConnectionInfo;
                table.ApplyLogOnInfo(tableLogonInfo);
            }

The problem is, that even if i have a report without subreport it does not update at all. I have two databases: DB1 and DB2. I have created a report based on stored procedure in specific schema in DB1 : schema.proc1. Then i have created the same stored procedure in DB2 (still schema.proc1 as db i just a copy). When I am trying to update connection info, so my printout will work on DB2 it just gives me an error 2812 (so he can't find the procedure). What is more if I'm working on DB1 (without changing the DatabaseName property) SQL profiler shows

exec "DB1"."schema"."proc1";

but when i change DatabaseName property to DB2 it shows

exec "proc1"

so it look like this method is not handling it in a normal way. What is interesting, if i set up a password for my "sa" login on SQL Server instance but i do NOT change the database, this method works fine if I just change crConnectionInfo.Password property.

Sorry for the long post, but it is really confusing for me and my customer is not that happy about this.

Best regards,

Mateusz

Share
10 |10000 characters needed characters left characters exceeded