ReportDocument.SetParameterValue problem

[Former Member]

Hello !

I need some advice for this problem:

I have a very simple CR2008 report used from my program (C#, VS2008, SQL2005, Northwind demo database). The report is used to generate a PDF document using Export method of ReportDocument object. It works OK.

Now I added a discrete parameter (a country) in the report to filter the result. In Crystal Report designer I can change the parameter value and see the report (preview) filtered according to the parameter. Now I need to set the parameter in my program at runtime before calling Export().

Calling

reportDoc.SetParameterValue("Country", "USA");

I get an exception at runtime which is totally unrelated to the parameter: "Code from database provider: 17. Unable to start connexion" (Translated from french so the exact english text may be a little bit different). Without that code line, the program is working OK and report is filtered with country selected in report designer.

Any help is appreciated.

Regards,

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello,

The error suggests that you're having an issue logging onto the database.

As a test you should be able to comment out your parameter code, database logon code, etc. Run the application and you should receive prompts for any missing information - like logon credentials or parameters.

If this works, then start adding back code until the application breaks again. Then you can concentrate on that area of the code.

Also - before starting, make sure you're running a report that does not have Saved Data. Open the report in Crystal Reports. Go to the File menu. Is there a check mark next to Save Data with Report? If so, clear it and resave the report. If not, you should be all set for the test.

Sincerely,

Dan Kelleher

[Former Member · Accepted solution]

Hello Dan,

No it is not a connection problem. The program is working perfectly when I comment out the single line with SetParameterValue function call.

If it makes a difference, the program is actually a COM object accessed by a Win32 application.

Regards,

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello,

Did you confirm that the "save data with report" option is unchecked on your report? Unless you're using integrated security you need to pass database logon credentials at some point when running the report.

Note that Crystal will try to connect to the database to check the parameters, so seeing this error isn't completely unexpected if there's a database logon issue.

If you're passing logon credentials in code, comment them out and run the application again.

You also haven't described the steps that you follow without the parameter code. If you leave the parameter code out do you get prompted to log in? Do you get prompted to fill in the parameter?

Sincerely,

Dan Kelleher

[Former Member · Accepted solution]

Hello,

1) Yes, I confirm "save data with report" is unchecked.

2) When commenting out the login code, I'm not prompetd for the logon info. I get an exception "Failure to connect to database" (translated from french). Same error if calling SetParameterValue or not. The code for creadential is:

foreach (Table table in reportDoc.Database.Tables)
{
  logonInfo = table.LogOnInfo;
  logonInfo.ConnectionInfo.DatabaseName = DatabaseName;
  logonInfo.ConnectionInfo.ServerName = ServerName;
  logonInfo.ConnectionInfo.UserID = loginName;
  logonInfo.ConnectionInfo.Password = loginPassword;

  table.ApplyLogOnInfo(logonInfo);
}

3) I wrote a loop to iterate thru all parameters and I can see the parameter as expected with all attributes defined in the report:

foreach (ParameterField Param in reportDoc.ParameterFields)
{
    ... code removed ...
}

4) I'm never prompted for the parameter.

btw: I moved SetParameterValue before or after credential code with same result.

Regards,

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello Francois,

The "Failure to connect to database" error is the real issue here. This means your credentials don't match what the report or the database is expecting.

If you comment out your database code you should be prompted to log into the database through a prompt. If you put in the credentials here does it fail?

You might also try hardcoding the database credential variables to see if it works. If so, then there may be a difference in what the variables are passing.

Sincerely,

Dan Kelleher

[Former Member · Accepted solution]

Hello Dan,

Can you tell me why the credential code is working perfectly when there is no call to SetParameterValue (The PDF file is correctly generated with data from the database) and suddently fails when SetParameterValue is called ?

I don't use MSSQL integrated security.

Maybe the login prompt is not created when the code is called within a COM object ? Remember that the .NET assembly I produce is a COM object accessed from a win32 application.

Regards,

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello Dan,

I wrote a WinForms application with same code. I get same result.

When credential is not provided by code, I'm not promted to enter. Just get a failure connecting to database.

When credential is OK by code, without SetParameterValue, I'm not prompted for parameter and I get error missing parameter (that is expected).

When credential is OK by code and SetParameterValue is called after having set credential, I get the same error about database connection:

Failure: Impossible de démarrer la connexion.

Détails : Code du fournisseur de la base de données : 17 Impossible de démarrer la connexion.

Northwind2 {1CBC784F-DAD3-465F-90C7-575AFA7FA116}.rpt

Détails : Code du fournisseur de la base de données : 17

Note that this is not the same error at all when removing credentials.

Regards,

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello Dan,

I have reduced as much my code in the WinFoms application. Here is full source:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public string ExecuteReport(string reportPath,
                                    string dbName,
                                    string loginName,
                                    string loginPassword,
                                    string exportPath)
        {
             ReportDocument reportDoc;
             DiskFileDestinationOptions diskFileDestinationOptions;
             ExportOptions exportOptions;

            string errMessage = "Unknown error";
            try
            {
                reportDoc = new ReportDocument();
                reportDoc.Load(reportPath);

                TableLogOnInfo logonInfo = new TableLogOnInfo();
                string ServerName;
                string DatabaseName;
                int I = dbName.IndexOf('.');  // Northwind.XPSQL1
                if (I == -1)
                {
                    ServerName = "";
                    DatabaseName = dbName;
                }
                else
                {
                    ServerName = dbName.Substring(0, I);
                    DatabaseName = dbName.Substring(I + 1, dbName.Length - I - 1);
                }
                
                foreach (Table table in reportDoc.Database.Tables)
                {
                    logonInfo = table.LogOnInfo;
                    logonInfo.ConnectionInfo.DatabaseName = DatabaseName;
                    logonInfo.ConnectionInfo.ServerName = ServerName;
                    logonInfo.ConnectionInfo.UserID = loginName;
                    logonInfo.ConnectionInfo.Password = loginPassword;

                    table.ApplyLogOnInfo(logonInfo);
                }

                reportDoc.SetParameterValue("Country", "USA");

                if (!System.IO.Directory.Exists(exportPath))
                {
                    System.IO.Directory.CreateDirectory(exportPath);
                }
                diskFileDestinationOptions = new DiskFileDestinationOptions();
                exportOptions = reportDoc.ExportOptions;
                exportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                exportOptions.ExportFormatOptions = null;
                exportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
                diskFileDestinationOptions.DiskFileName = exportPath + "PortableDoc.pdf";
                exportOptions.ExportDestinationOptions = diskFileDestinationOptions;
                reportDoc.Export();
                errMessage = "";
            }
            catch (Exception ex)
            {
                errMessage = "Failure: " + ex.Message;
            }
            return errMessage;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string ErrMessage;
            textBox1.Text = "Started";
            ErrMessage = ExecuteReport("C:\\Northwind2.rpt",
                          "Northwind.MySqlServer",
                          "sa",
                          "*****",
                          "C:\\Exported1\\");
            if (ErrMessage == "")
                textBox1.Text = "Done";
            else
                textBox1.Text = ErrMessage;

        }
    }
}

The report is the most basic possible. I uses Microsoft Northwind demo database and list the customer table with a parameter to filter on the country.

Regards,

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello Francios,

I did not understand that your application is not using a viewer. That's why you're not being prompted for logon when you comment out your code. Let's make it an even "simpler" sample application that makes use of the CrystalReportViewer to view the report. Once we understand what's going on and get the code working you can move it back into your actual application.

First - for confirmation. You are using VS2008, correct? Are you using CR2008 SP0 or SP1? Open Crystal Reports, go to Help > About Crystal Reports. What is the version number? For VS2008 integration SP0 is the minimum requirement. The version number should be 12.0.0.683 or higher.

Create a simple windows test application. Add a CrystalReportViewer to the form. Add this code to Page_Load:

	//Crystal Reports variables
	ReportDocument crReportDocument = new ReportDocument(); 
	Database crDatabase; 
	Tables crTables; 
	Table crTable; 
	TableLogOnInfo crTableLogOnInfo; 
	ConnectionInfo crConnectionInfo = new ConnectionInfo ();

            ReportDocument crReportDocument = new ReportDocument();
            Database crDatabase;
		    Tables crTables;
            Table crTable;
       	    TableLogOnInfo crTableLogOnInfo;
		    ConnectionInfo crConnectionInfo = new ConnectionInfo ();

            crReportDocument.Load("<path_to_your\\Report.rpt>"); // Hard code the path for testing.

            ////Setup the connection information structure to be used
            ////to log onto the datasource for the report.
            //crConnectionInfo.ServerName = "<serverName>";   //physical server name
            //crConnectionInfo.DatabaseName = "Northwind";
            //crConnectionInfo.UserID = "<userID>";
            //crConnectionInfo.Password = "<password>";

            ////Get the table information from the report
            ////crDatabase = crReportDocument.Database;
            //crTables = crDatabase.Tables;

            ////Loop through all tables in the report and apply the connection
            ////information for each table.
            //for (int i = 0; i < crTables.Count; i++)
            //{
              //crTable = crTables<i>;
              //crTableLogOnInfo = crTable.LogOnInfo;
              //crTableLogOnInfo.ConnectionInfo = crConnectionInfo;
              //crTable.ApplyLogOnInfo(crTableLogOnInfo);
            //}

            ////Set single discrete parameter
            //crReportDocument.SetParameterValue("Country", "USA");

            //Set the viewer to the report object to be previewed.
            crystalReportViewer1.ReportSource = crReportDocument;

When running this code you should be prompted to log onto your database. Then you should be prompted to pass the single parameter. If this works then uncomment the database logon code. Does it work? If this works, uncomment the .SetParameterValue code. Does this work.

After going through these steps let me know what you find out.

Sincerely,

Dan Kelleher

NOTE: Code sections are displaying incorrectly. No ETA on when this will be corrected.

[Former Member · Accepted solution]

Hello Dan,

> First - for confirmation. You are using VS2008, correct?

I'm using VS2008 9.0.21022.8 RTM

> Are you using CR2008 SP0 or SP1?

> The version number should be 12.0.0.683 or higher.

It is 12.0.0.683

> Create a simple windows test application. Add a CrystalReportViewer to the form.

Done.

> After going through these steps let me know what you find out.

Everything works as expected.

I get prompted for database logon, I'm prompted for parameter value.

Uncommenting logon code supress the logon prompt and the report still OK.

Uncommenting SetParameterValue supress the parameter prompt and the report is still OK with correct selection.

So everything is like I would expect. No problem. Well, this is not what I needed. I need to produce a PDF report without showing anything on screen. And this must be in a COM object.

Thanks for your help.

--

Francois PIETTE

http://www.overbyte.be

[Former Member · Accepted solution]

Hello Francios,

What I asked you to do was a test. As you know, it is often helpful to start at the beginning and work your way up.

I used your code and put it into my simple application. I commented out the things that are not needed on my end - like the Try/Catch, and the test for the database name.

Your code works fine for me in my simple windows application. You should test this yourself if you haven't done so already. If this works then it may be somehow related to how you are using it in the COM object.

Sincerely,

Dan Kelleher

[Former Member · Accepted solution]

Hello,

I added the call to Export() and the options at the end of your code and it works.

Looking carefully at the differences between your code and my code, probably the problem is solved by using - as you do - a separate variable crConnectionInfo to set all tables ConnectionInfo instead of assigning the existing connectioninfo.

In short it works !

Thank you so much for your help. I granted you 10 points.

Regards,

--

Francois PIETTE

http://www.overbyte.be