login required for dynamic parameter with a command data source

[mark_brill91]

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!

[DonWilliams · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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!

[mark_brill91 · Accepted solution]

OK, it's been a long time but I finally got a chance to play with this again. I can see why you suggested what you did and it was helpful to my understanding but it doesn't quite solve the issue in the way I need. I can only see how to get to a full editor via the answer box. So I'm going to clarify my problem via an answer!

[mark_brill91]

OK, this post isn't an answer.  It's to give a more detailed reply to Don's answer above since I can't see how to do that outside of submitting an answer!

So following Don's suggestions and downloading and studying the RAS2010_CsharpParameters from the article he linked. I was able to make the parameters popup screen go away.  SO in the demo app, my using SetParam and then Replace Conn, it made the parameter popup go away.  'Cool', I thought.  As per that app, I set our missing parameter to empty string and then it applied our existing connection code and we got the same results.  Yay!  Only, it then occurred to me this isn't quite what I want!

I want the parameter popup. I just don't want the logon details on it.  So I want the command to logon to SQL server, retrieve the data, present the results to the user in a combo box as the source for a dynamic parameter prompt.  But if I've set the parameter to empty string, we don't get any prompt - because the parameter is already set.

So attached are screen show of it working this way in the v11 COM based viewer.  Whereas the screenshot called logon in the original post shows how the parameters popup looks in 13 dot net viewer.  But if i set the parameter in code, it does avoid the login prompt. But it also doesn't bring up the prompt I do want. Because we set it in code.

Does this make sense?  Is this possible in 13?

Thanks!

[DonWilliams]

Hi Mark, See if this helps, change the default value. It's a property of the viewer, you can set it in code also:

crystalReportViewer1.ReuseParameterValuesOnRefresh = false; // or True

Try adding a Report.refresh also, you may need to test this in various places, because it can discard any values you may have previously set.

[mark_brill91]

Success! Setting a default parameter, then the DB connection, showing it, then a refresh works in your example app! I'm sure I can work something from this into our app now. Thanks for all your help!