Crystal Report runtime Missing parameter values error after updating SQL provider programmatically

[former_member714747]

Due to TLS 1.2 support we have to upgrade SQL driver MSOLEDBSQL, crystal runtime version 13.0.30 SP 30 and crystal report developer 2016.

When I manually update the driver for report and subreport to MSOLEDBSQL using crystal report developer 2016 then it executes without any error on crystal report runtime 13.0.30 SP 30 (latest version)

But when I change the driver programmatically then it gives error Missing parameter values:

Stack trace : at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)

at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.FormatEngine.Export(ExportRequestContext reqContext)

at CrystalDecisions.CrystalReports.Engine.ReportDocument.Export(ExportOptions options)

Assembly version - 13.0.4000.0

Dll version -13.0.30.3805

Store proc used to fetch the data from SQL server and bind to report.

Note: Most of the repot is working with below code but some reports are failing. My assumption is that those report having formula and record selection linking between main report and subreport having problem. Please guide to resolve the issue.

Code to update the driver.

privatevoid SetTbl(ReportDocument rpt)

{

SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["ABC"].ConnectionString);

foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in rpt.ReportClientDocument.DatabaseController.Database.Tables)

{

CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable newTable = table.Clone(true);

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo cnnRpt = newTable.ConnectionInfo;

PropertyBag PropertyBagRpt = cnnRpt.Attributes["QE_LogonProperties"];

PropertyBagRpt["Provider"] = "MSOLEDBSQL";

//PropertyBagRpt["Provider"] = "SQLNCLI11";

PropertyBagRpt["Data Source"] = scsb.DataSource;

PropertyBagRpt["Initial Catalog"] = scsb.InitialCatalog;

if (scsb.IntegratedSecurity)

PropertyBagRpt["Integrated Security"] = true;

else

{

PropertyBagRpt["Integrated Security"] = false;

PropertyBagRpt["User ID"] = scsb.UserID;

PropertyBagRpt["Password"] = scsb.UserID;

}

cnnRpt.Attributes["QE_LogonProperties"] = PropertyBagRpt;

cnnRpt.Attributes["Database DLL"] = "crdb_ado.dll";

cnnRpt.Attributes["QE_DatabaseName"] = scsb.InitialCatalog;

cnnRpt.Attributes["QE_ServerDescription"] = scsb.DataSource;

cnnRpt.Attributes["QE_SQLDB"] = true;

rpt.ReportClientDocument.DatabaseController.SetTableLocation(table, newTable);

}

foreach (ReportDocument subreport in rpt.Subreports)

{

foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in rpt.ReportClientDocument.SubreportController.GetSubreportDatabase(subreport.Name).Tables)

{

CrystalDecisions.ReportAppServer.DataDefModel.ISCRTable newTable = table.Clone(true); ;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo cnnRpt = newTable.ConnectionInfo;

PropertyBag PropertyBagRpt = cnnRpt.Attributes["QE_LogonProperties"];

PropertyBagRpt["Provider"] = "MSOLEDBSQL";

//PropertyBagRpt["Provider"] = "SQLNCLI11";

PropertyBagRpt["Data Source"] = scsb.DataSource;

PropertyBagRpt["Initial Catalog"] = scsb.InitialCatalog;

if (scsb.IntegratedSecurity)

PropertyBagRpt["Integrated Security"] = true;

else

{

PropertyBagRpt["Integrated Security"] = false;

PropertyBagRpt["User ID"] = scsb.UserID;

PropertyBagRpt["Password"] = scsb.UserID;

}

cnnRpt.Attributes["QE_LogonProperties"] = PropertyBagRpt;

cnnRpt.Attributes["Database DLL"] = "crdb_ado.dll";

cnnRpt.Attributes["QE_DatabaseName"] = scsb.InitialCatalog;

cnnRpt.Attributes["QE_ServerDescription"] = scsb.DataSource;

cnnRpt.Attributes["QE_SQLDB"] = true;

newTable.ConnectionInfo = cnnRpt;

rpt.ReportClientDocument.SubreportController.SetTableLocation(subreport.Name, table, newTable);

}

}

}

[former_member714747 · Accepted solution]

BTW I have changed the code differently and it has worked and continue testing for all other reports. code as below. Please check if there is any improvement required or any statement may result abnormal behavior by crystal report runtime.

publicstatic TableLogOnInfo GetTableLogOnInfo()

{

SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(ConfigurationManager.ConnectionStrings["ABC"].ConnectionString);

CrystalDecisions.Shared.ConnectionInfo connectionInfo = new ConnectionInfo();

connectionInfo.ServerName = scsb.DataSource;

connectionInfo.DatabaseName = scsb.InitialCatalog;

if (scsb.IntegratedSecurity)

{

connectionInfo.IntegratedSecurity = true;

connectionInfo.UserID = "";

connectionInfo.Password = "";

}

else

{

connectionInfo.IntegratedSecurity = false;

connectionInfo.UserID = scsb.UserID;

connectionInfo.Password = scsb.Password;

}

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag connectionAttributes = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

connectionAttributes.EnsureCapacity(12);

connectionAttributes.Add("Connect Timeout", "15");

connectionAttributes.Add("Data Source", scsb.DataSource);

connectionAttributes.Add("General Timeout", "0");

connectionAttributes.Add("Initial Catalog", scsb.InitialCatalog);

if (scsb.IntegratedSecurity)

connectionAttributes.Add("Integrated Security", true);

else

{

connectionAttributes.Add("Integrated Security", false);

connectionAttributes.Add("User ID",scsb.UserID);

connectionAttributes.Add("Password",scsb.UserID);

}

if(scsb.MultiSubnetFailover)

connectionAttributes.Add("MultiSubnetFailover", 1);

else

connectionAttributes.Add("MultiSubnetFailover", 0);

connectionAttributes.Add("Locale Identifier", "1033");

connectionAttributes.Add("OLE DB Services", "-5");

connectionAttributes.Add("Provider", "MSOLEDBSQL");

//connectionAttributes.Add("Provider", "SQLNCLI11");

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", scsb.InitialCatalog));

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", scsb.DataSource));

attributes.Collection.Add(new NameValuePair2("SSO Enabled", false));

//CrystalDecisions.Shared.ConnectionInfo connectionInfo = GetConnectionInfo();

connectionInfo.Attributes = attributes;

connectionInfo.Type = ConnectionInfoType.CRQE;

TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();

tableLogOnInfo.ConnectionInfo = connectionInfo;

return tableLogOnInfo;

}

call the GetTableLogOnInfo method and iterate tables to update ConnectionInfo.

privatevoid SetTbl1(ReportDocument rpt)

{

TableLogOnInfo t1 = GetTableLogOnInfo();

using (CrystalDecisions.CrystalReports.Engine.Tables tbls1 = rpt.Database.Tables)

{

foreach (Table tbl in tbls1)

{

tbl.ApplyLogOnInfo(t1);

}

}

}

Calling mechanism for Main and sub report.

SetTbl1(rpt);

foreach (ReportDocument srpt in rpt.Subreports)

SetTbl1(srpt);

[former_member11696]

Add this after you set logon on info:

try
{
    if (crTable.TestConnectivity())
        ConWorks = true;
    else
        ConWorks = false;
}
catch (Exception ex)
{
    btnSQLStatement.Text += "Test Connectivity failed: " + ex.ToString();
}

what's the difference between working and non-working?

You'll have to debug to see what the differences are, SP's require the Parameters to be set first before logging on is set in code. I don't see that in your code.

[former_member714747]

Working means I am able to export report in pdf using RPT file. Not working means not able to export the report in pdf and getting missing parameter error.

I have tested with set the parameter values first for SP before setting the log on info but however code not pasted above.

[former_member11696]

Hi Nikesh,

I found that to be true also and the only way to fix it is to manually update the report in CR Designer.

Because the rpt file keeps the client info in the file it generates the based on that driver dynamically thus it fails to connect internally so it can update the report info. Since it fails to connect it won't save the new info.

One scenario I did not test was to connect without TLS 1.2 enabled and then export the report to RPT format. Once saved then enable TLS 1.2 and see if that works.

Also, because you are using a SP set the parameter values first before setting the log on info.

Search for this KBA - 2269180 - Disabling TLS 1.0 may cause Crystal Reports Designer and .NET application to fail to connect to your MS SQL Server Database 2012/2014

Don

[former_member714747]

Hi Don,

Thanks for response.

36 of 50 reports are working with above code change but remaining 14 are failing.

these 14 report having multiple sub report and also some report has sub report link.

but what could be the reason for working with some of reports?

I have also test with set the parameter values first for SP before setting the log on info but didn't work.