cancel
Showing results for 
Search instead for 
Did you mean: 

login required for dynamic parameter with a command data source

mark_brill91
Discoverer
0 Kudos
299

Hi,

We have a dot net application for viewing crystal reports which ships with our software to display both pre-supplied reports and end users custom reports.  It uses Table.ApplyLogOnInfo to point to an end users SQL server and database and set a SQL user and pass for each table in the report and all sub reports.

One of our end users has created a report with a dynamic parameter based on a command and this causes Crystal to display a login screen that reads:

Enter Parameter values: Server name (disabled), User Name, Password, Use Single Signon Key (set to false and disabled)

If we enter the SQL username and password - the report then works fine, displaying the list of values from the DB.  I've tried this in Crystal 13 SP33 and SP35, 32 bit in a viewer compiled in VS2019

 

How do we avoid this login screen?  Stepping through the code in debug, ApplyLogOnInfo is being applied to the command and TestConnectivity returns true.

 

This can be recreated with a pretty simple report:

Using a system DSN to SQL Server using SQL Server auth

I create a simple command of the form: 'select field from table'

I then create a new dynamic parameter and set the field in this command as the datasource

I then drop this parameter on to the report

 

When this report is sent to our viewer it then loops through all tables on the report (and all tables in any sub reports - not relevant in this example)

 

For each table:

foreach (Table tbl in rep.Database.Tables)

 

we clone the logoninfo, modify it as desired and apply it:

TableLogOnInfo info = (TableLogOnInfo)tbl.LogOnInfo.Clone();
info.ConnectionInfo = CrystalSupport.GetConnectionInfo(ServerName, database, UserID, Password, Provider);
tbl.ApplyLogOnInfo(info);

 

We then test it and it comes back fine:

if (tbl.TestConnectivity())

 

The routine to configure the connectionInfo is as follows (this was written long ago, not by me, and I don't necessarily know what all these properties are! I would imagine a lot of it came from an example?):

private static ConnectionInfo GetConnectionInfo(string serverName, string databaseName, string userId, string password, string provider)
{
ConnectionInfo connectionInfo = new ConnectionInfo();
connectionInfo.Type = ConnectionInfoType.SQL;
connectionInfo.ServerName = serverName;
connectionInfo.DatabaseName = databaseName;
connectionInfo.UserID = userId;
connectionInfo.Password = password;

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag connectionAttributes = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
connectionAttributes.EnsureCapacity(11);
connectionAttributes.Add("Connect Timeout", "15");
connectionAttributes.Add("Data Source", connectionInfo.ServerName);
connectionAttributes.Add("General Timeout", "0");
connectionAttributes.Add("Initial Catalog", connectionInfo.DatabaseName);
connectionAttributes.Add("Integrated Security", "false");
connectionAttributes.Add("Locale Identifier", "1033");
connectionAttributes.Add("OLE DB Services", "-5");
connectionAttributes.Add("Provider", provider);
connectionAttributes.Add("Tag with column collation when possible", "0");
connectionAttributes.Add("Use DSN Default Properties", false);
connectionAttributes.Add("Use Encryption for Data", "0");

DbConnectionAttributes attributes = new DbConnectionAttributes();
attributes.Collection.Add(new NameValuePair2("Database DLL", "crdb_ado.dll"));
attributes.Collection.Add(new NameValuePair2("QE_DatabaseName", connectionInfo.DatabaseName));
attributes.Collection.Add(new NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"));
attributes.Collection.Add(new NameValuePair2("QE_LogonProperties", connectionAttributes));
attributes.Collection.Add(new NameValuePair2("QE_ServerDescription", connectionInfo.ServerName));
attributes.Collection.Add(new NameValuePair2("SSO Enabled", "false"));

connectionInfo.Attributes = attributes;

return connectionInfo;
}

 

 

So... this process has worked for years on tables.  And successfully applies and tests on commands.  But when a dynamic parameter uses this command as a data source - it prompts for a log in.  Does anyone have any ideas what I can do to get this logged in via code?

Thanks!

Accepted Solutions (0)

Answers (1)

Answers (1)

DonWilliams
Active Contributor
0 Kudos

Hi Mark,

Commands are considered Stored Procedures requiring the parameter value be set before logging in, because the SP needs a value to be able to process it.

In your app you can use the below code to see if the report is using a Command and if so set the Parameter first and then set the log on info.

 

foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables)
{
    if (crDatabase.Tables.Count != 0)
    {
        CrystalDecisions.Shared.NameValuePair2 nvp2 = (NameValuePair2)rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.Attributes.Collection[1];
        btnSQLStatement.Text += "\nRPT Data Source Info: \n" + " Server Name: " + rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.ServerName.ToString() + "\n Database Name: " + nvp2.Value.ToString() + "\n Table Name: " + rpt.Database.Tables[dbx].Name.ToString();
        if (rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.UserID != null)
            btnSQLStatement.Text += "\n User ID: " + rpt.Database.Tables[dbx].LogOnInfo.ConnectionInfo.UserID.ToString() + "\r\n";
        btrSearchPath.Text = rpt.Database.Tables[dbx].Name.ToString();
        dbx++;
    }
    else
        btnSQLStatement.Text += "No Data source or not found\r\n";
}

 

 Or this way to scan the parameters:

 

if (paramfield.ParameterType.ToString() == "crParameterFieldTypeStoredProcedureParameter")
{
    if (IsCMD)
        btnReportObjects.AppendText(" (Command)");
    else
        if (IsBEX == false)
        btnReportObjects.AppendText(" (Stored Procedure)");
    else
        btnReportObjects.AppendText(" (BEX Query)");
}

 

The other thing to note is since the Server name is blank it could indicate the report is using a different set of Connection info and it failed to connect.

You'll have to do some more report debugging, make sure it matches your connection info.

The Parameter sample app I wrote has more details:

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

Don

mark_brill91
Discoverer
0 Kudos

Hi Don,

thanks for this information.  This looks great and your response is much appreciated!  I'll have a go through this later in the week and let you know how I get on.

 

Thanks,

Mark

mark_brill91
Discoverer
0 Kudos
I got dragged onto something else unfortunately so I've not had chance to look at this again yet but I do appreciate the time you put in your response and I will have a go at this again when I'm able. Thanks once again!