Stored Procedure Called through Crystal Report, Doesn't insert records

[Former Member]

Dear Tech Team,

I have a RPT file that has few sub reports. The Main Stored procedure that I'm using for the crystal reports inserts few records in one of the table and that table is referenced in the sub reports. This works fine with the old version of crystal reports (Version 9) with the C++ Code. We recently migrated it to .Net Version: SP16, VS2013, MS SQL Standard 2014. It appears that when the main Stored Procedure is called, which has the insert statement to insert records in another table not at all works. We use ODBC Connection (SQL Native Client) to connect to the SQL server. Please advise.

Thanks,

Arun

[former_member11696 · Accepted solution]

Try using the SQL Native 10/11 client.

If you turn on SQL Profiler are you see the SP run and inserting data?

Could b e SQL Or CR is caching the data, make sure you are committing the Data also.

You may also need to enable Dirty Reads so it captures the data in the buffer.

Do more debugging, lots of DB security and other changes since CR 9.

Don

[Former Member · Accepted solution]

The main issue happening was, the report has 20 sub reports and the old engine CR Version 9 was executing the sub reports in the order it was created. Main Report was inserting records to one of the table when it called the stored proc. Later sub report 12 had a select and it was showing the results properly. Sub report 16 was calling another stored proc that deleted all these records. But finally when the report with all subreports executed, the results were coming fine with CR9 engine. When we migrated to CR13 with SP16, the order in which sub reports were run seems changed. so this was calling the delete sub report first before executing the select sub report. Anyways, I changed the logic of select and here inserting the records into a temp table and deleting the main records in the other table and took care of the issue. But I'm curious to know if we can preserve/specify the order of execution of sub reports in any way.

[former_member11696 · Accepted solution]

Hi Arum,

Ah ha... nice detective work....

they have updated the Subreport Object collection, previously you were limited to 40 subreports, now it's 120. So this change may have altered the way we read in the subbreport collection.

Once way to get them is:

//set the crSections object to the current report's sections

CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections;

int flcnt = 0;

bool SecureDB;

//loop through all the sections to find all the subreport objects

foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)

{

    crReportObjects = crSection.ReportObjects;

    //loop through all the report objects to find all the subreports

    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)

    {

        if (crReportObject.Kind == ReportObjectKind.SubreportObject)

        {

            try

            {

The other way is to open the subreport by name

So I don't believe we will alter the order again, DEV said 120 is max the SDK will be able to access.

It did update the the info in the RPT file but nothing you can access either.

That Parameter App in Doc-70646 has a drop down list to get subreports using above code, I believe the order that it produces is the order of insertion. As far as I recall I don't believe this ever changed but may be different for subreport objects. You can see this in the various object labels, depends on when the object was added to the report.

Don

[Former Member · Accepted solution]

Thank you Don for the Helpful information.