Change reports Datasource (sproc) at runtime

[john_noble3]

Hi folks,

I was advised to move this thread to this forum....

My report is designed to get the data from a stored procedure (sproc1) via the database fields from the field explorer. This has obviously been done at design time where the fields on the report match those that the sproc1 returns.

My question is can I change the report at runtime so that its gets its data from sproc2 instead of sproc1 ?

Both sprocs return exactly the same fieldnames / datatypes etc

I am using Visual Studio C# Win Forms with e mySQL back end.

J

[former_member11696 · Accepted solution]

Still need to know what Dev language you are using and CR SDK package?

[john_noble3 · Accepted solution]

Hi Don,

Using C# with Crystal  v13.0.15

J

[former_member11696 · Accepted solution]

Click the Overview Tab on this forum and you'll find samples on how to...

ReplaceConnection may be the one to use.

Don

[john_noble3 · Accepted solution]

Hi Don,

When I click and then view the Overview tab, I cannot see a samples section ??

Can you provide a link ?

J

[former_member11696 · Accepted solution]

Unfortunately they broke the search engine and it only goes back to 2015. Here's the content of the KBA:

1535347 - How to replace database connection information used by a Crystal Reports in a VS .NET proj...

Symptom

• SQL Database Vendor Code of 2812 (other Database Vendor Code errors possible - depending on the database and connection type)
• After calling method ApplyLogOnInfo, the logon information in each table of ReportDocument does not change
• Database login prompts when changing database connection type (e.g.; crdb_dao.dll to crdb_adoplus.dll)
• Slow running reports

Resolution

• Use the InProc RAS SDK for VS .NET
• Example code:

private void ReplaceConnection_Click(object sender, EventArgs e)

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rcd;

rcd = rptClientDoc;

rptClientDoc.DatabaseController.LogonEx("dwcb12003", "xtreme", "sb", "pw");

//Create the logon propertybag for the connection we wish to use
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
logonDetails.Add("Auto Translate", -1);
logonDetails.Add("Connect Timeout", 15);
logonDetails.Add("Data Source", "dwcb12003");
logonDetails.Add("General Timeout", 0);
logonDetails.Add("Initial Catalog", "Orders");
logonDetails.Add("Integrated Security", "True");
logonDetails.Add("Locale Identifier", 1033);
logonDetails.Add("OLE DB Services", -5);
logonDetails.Add("Provider", "SQLOLEDB");
logonDetails.Add("Use Encryption for Data", 0);
logonDetails.Add("Owner", "dbo"); // schema

//Create the QE (query engine) propertybag with the provider details and logon property bag.
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
QE_Details.Add("Database DLL", "crdb_ado.dll");
QE_Details.Add("QE_DatabaseName", "Orders");
QE_Details.Add("QE_DatabaseType", "OLE DB (ADO)");
QE_Details.Add("QE_LogonProperties", logonDetails);
QE_Details.Add("QE_ServerDescription", "dwcb12003");
QE_Details.Add("QE_SQLDB", "True");
QE_Details.Add("SSO Enabled", "False");
QE_Details.Add("Owner", "dbo");

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);
for (int I = 0; I < oldConnInfos.Count; I++)
{
oldConnInfo = oldConnInfos;
newConnInfo.Attributes = QE_Details;
newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
}
}

See Also

• Attached CodeBuilder-RasConnectionInfo application
• InProc RAS Samples

[john_noble3 · Accepted solution]

Hi Don,

I cant see one for replace connection as you stated but there is one for 'change datsource location' but that shows how to change location at design time. But I need to be able to change datasource location at runtime.

J

[former_member11696 · Accepted solution]

Did you not see the code I pasted in from the KBA?

[john_noble3 · Accepted solution]

Hi Don,

I see it now,

Before it appeared as if you had posted a screen shot of the webpage and not the actual code ???

Thanks for the code.

J