Question Re: Subreport get and set an SQL query in Technology Q&A

Subreport get and set an SQL query

pierrestage — Wed, 08 Jun 2022 09:10:31 GMT

Hello,

For my project, I need to change the name of the tables of my project's data

I'm using the lastest version of CR for Visual Studio in C#

To do it, I get the SQL query of the report, I change the names I want in the string and I set the new SQL query. It work perfectly

But now, I need to do the same thing with subreports and I can't do the same simple technique.

When I get the SQL statement with "myReport.ReportClientDocument.SubreportController.GetSubreport("name").RowsetController.GetSQLStatement(groupPath, out temp)" I have an error "Missing parameter values.".

And even when I bypass this problem,
I tried to redo links by changing the name of the tables

SubreportLinks subreportLinks = new SubreportLinks();
                        SubreportLink subreportLink = new SubreportLink();
                        subreportLinks.Add(subreportLink);
                        subreportLink.LinkedParameterName = "{?Pm-DOSSIER_HISTO.DOSS_ID_DOSSIER}";
                        subreportLink.MainReportFieldName = "{DOSSIER_HISTO.DOSS_ID_DOSSIER}";
                        subreportLink.SubreportFieldName = "{JOURNAL_VIEW.JOUR_ID_DOSSIER}";
                        myReport.ReportClientDocument.SubreportController.SetSubreportLinks("name", subreportLinks);

I also tried to redo RecordSelectionFormula by changing the name of the tables and despite all this I have a mistake "Invalid field name".

I can't find the solution and I need to finish this project as soon as possible. I would be very grateful if someone could help me

Re: Subreport get and set an SQL query

former_member11696 — Wed, 08 Jun 2022 19:52:21 GMT

You can't edit the SQL, only way to change it is to base the reports on Commands. ( Actuall ynot completely true since you can change a data source from a Table to a command but for only one table)

You are should never change the Table or structure of the reports data source in code.

That info is saved in the RPT file SDK Auto-Maps the fields, if they can't be found the engine simply deletes the fields and likely breaking the report.

You do need to be logged onto the Datasource the subreport needs also, due to the SQL is dynamically generated based on the DB Server type..

Re: Subreport get and set an SQL query

pierrestage — Thu, 09 Jun 2022 07:26:22 GMT

In my project there are several different tables containing the same data with the same column names but containing older data. That's why I want to change the tables dynamically. It works very well for the main report.

here is my code for the report

// Get SQL request
               string request;
               var groupPath = new CrystalDecisions.ReportAppServer.DataDefModel.GroupPath();
               string temp = String.Empty;
               myReport.SetDatabaseLogon(/*"UserId"*/, /*"Password"*/);
               request = myReport.ReportClientDocument.RowsetController.GetSQLStatement(groupPath, out temp);

// Connection to data base
                ConnectionInfo connectionInfo = new ConnectionInfo(myReport.Database.Tables[0].LogOnInfo.ConnectionInfo);
                connectionInfo.Password = /*"PassWord"*/;
                connectionInfo.ServerName = /*"ServerName"*/;

////////////////
// I add a WHERE or I change table names in the request
////////////////

// Set the new SQL request 
               myReport.SetSQLCommandTable(connectionInfo, "newTableName", request);

Why wouldn't it work with the subreports ?

Re: Subreport get and set an SQL query

former_member11696 — Thu, 09 Jun 2022 20:15:59 GMT

Yes you must log onto the Server before changes can be made.

So is the Main report based on a Command?

As noted, you can't change the SQL directly if the report is not based on a Command originally.

Are the subreports based on a Command?

And should myReport be mySubreport?

Re: Subreport get and set an SQL query

pierrestage — Fri, 10 Jun 2022 07:15:52 GMT

I can get the SQL statement of my main report so I think it is based on a Command.

For my subreports I don't know but I can't get the SQL statement like with the main report. I don't know how it work.

And yes, in the code I gave myReport should be mySubreport.

Re: Subreport get and set an SQL query

former_member11696 — Tue, 14 Jun 2022 16:37:18 GMT

See my sample app that can get/set the log on info:

how-to-parameters-in-crystal-reports-for-visual-studio-net

See if it gets the the SQL from the subreports, you may need to modify the sample.