Error while export report with subreports to PDF (NET 4.0, C#) - Database Vendor Code: 201

[Former Member]

Here is my code

bool exportToPdf(int id)
        {
            var rdoc = new ReportDocument();
            ParameterFieldDefinitions parmFields = rdoc.DataDefinition.ParameterFields;
            int idx = 0;
            try
            {
                rdoc.Load(HostingEnvironment.MapPath(string.Format("~/App_Data/Reports/{0}", "Rep1.rpt")));
                rdoc.SetDatabaseLogon("sa", "SA PASS", "127.0.0.1", "TestTb", true); 
                foreach (ParameterFieldDefinition def in parmFields)
                {
                    if (!def.IsLinked())
                    {                        
                        switch (def.ValueType)
                        {
                            case FieldValueType.StringField:
                                rdoc.SetParameterValue(idx, "");
                                break;

                            case FieldValueType.NumberField:
                                rdoc.SetParameterValue(idx, 0);
                                break;

                            default:
                                rdoc.SetParameterValue(idx, null);
                                break;
                        }

                    }
                    idx++;
                }
                rdoc.SetParameterValue(0, id);
                rdoc.ExportToDisk(ExportFormatType.PortableDocFormat, "D:\\report_" + id.ToString() + ".pdf");
                return true;
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return false;
            }
        }

On method ExportDoDisk i get error:

Failed to retrieve data from the database. Cannot export report. Error in file Rep1.rpt. Details: Database Vendor Code: 201

(in Polish: pobranie danych z bazy danych nie powiodło się.\rNie można wyeksportować raportu.\n\rBłąd w pliku Rep1.rpt. Pobranie danych z bazy danych nie powiodło się.\nSzczegóły:  [Kod dostawcy bazy danych: 201).

If I preview report in Crystal report designer, there is no problems to generate report.

Crystal lib: 13.0.2000.0

Please help.

[former_member11696 · Accepted solution]

CR is simply passing the Database error code to you, what is Error 201  in your Database error code list?

If 32 bit app make sure 32 bit client is installed, if 64 bit app make sure 64 bit client is installed.

Crystal Lib is always 13.0.2000.0, need to know the SP you are using, actual file version?

Don

[Former Member · Accepted solution]

Hi Don,

1. It's MS SQL:

Microsoft SQL Server 2012 (64-bit)

Error 201 for MSSQL is

Procedure '<Procedure Name>' expects parameter '<Parameter Name>', which was not supplied.

But error message doesn't show which parameter was not supplied.

2. I build this app on profile "Any CPU". I have installed both 32 and 64 bit libs and my project references to MSIL path:
C:\Windows\assembly\GAC_MSIL\CrystalDecisions.CrystalReports.Engine\13.0.2000.0__692fbea5521e1304\CrystalDecisions.CrystalReports.Engine.dll

3. File version of Crystal lib: 13.0.2.469

[former_member11696 · Accepted solution]

As the error indicates, set the parameter value first.

Move this line to before you set the connection info:

rdoc.SetParameterValue(0, id);

And try using the PC Name rather than the generic IP address.

Oh, and you are using SP 2, we are now up to SP 16, download the latest and try again.

Don

[Former Member · Accepted solution]

I have moved rdoc.SetParameterValue just after rdoc.Load and before rdoc.SetDatabaseLogon. But there is still the same error.

Also after changing server name and using latest lib doesn't fix problem.

Maybe the problem is in report file? Can you verify attached files (rename rep1.rpt.txt to rep1.rpt)?

mssql_procedures.sql.txt.zip

rep1.rpt.txt.zip

[former_member11696 · Accepted solution]

Thank you, that helps...

In the Main SetLogon code in my test app you can add this to set the parameter form the main report:

// this is where you set the main report parameter values

// if the subreport is not linked by the record selection formula it returns the first value, or last value depending on the subreport link

// so in this case where the subreport is linked via the record selection formula the value needs to be passed to the subreport Link.

subreportLinkValue = 0;

SetCrystalParam(rpt, "@ID", subreportLinkValue.ToString());

crTableLogOnInfo = crTable.LogOnInfo;

crTableLogOnInfo.ConnectionInfo = crConnectioninfo;

No go down the code where I set the log on info for the subreports and add this:

// Log on and Get subreport SQl

SubreportController subreportController = rptClientDoc.SubreportController;

SubreportClientDocument subreportClientDocument = subreportController.GetSubreport(crSubreportObject.SubreportName);

// now set the subreport parameter - need to do this for each one.

SetSubCrystalParam(subreportClientDocument, "ID", "1");

That sets only the one subreport that uses the ID field. What you need to do is add code to test for the Subreport by name and then get the parameter collection and set the value accordingly.

I don't know what or how you want to set the subreport parameter values but the code to do so is in my test app, you just need to figure out where and how to...

If I don't set any of them then I get prompted for all 4:

It's the secondary subreport links that are causing it to prompt.

Don

[Former Member · Accepted solution]

I's sorry Don, but I don't know how to pass parameters to subreports. Can you attach sample code with any programming language?

In this libs I don't see methods or types called SetCrystalParam, SubreportController, GetSubreport:

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

But this works:

rdoc.SetParameterValue(0, trasaId.ToString());
rdoc.SetParameterValue("ID", id.ToString(), "PODSUMOWANIE");
rdoc.SetParameterValue("ID", id.ToString(), "Opak_ZWR");
rdoc.SetParameterValue("ID", id.ToString(), "Paczki");

I have to find out how to iterate through all subreports parameters without hard-coded setting values.

[former_member11696 · Accepted solution]

Get my sample app I used to test from this Doc:

Has the code in it...

Don