2420739 - SqlQueryString Property cannot be modified when using SQLExpressions and DataSets in a .NET application

Background: We create SQL conditions to support following features in Crystal Reports and we use SQLExpressions in some of the Reports.

• Upgrading from using the RDC to .NET
• To add security to the SQL Statement
• Many of our report templates are executed from our code via the SetDataSource approach. But the problem with the updated SDKs is that reports so created cannot utilize SQLExpressions in order to augment the report. When we add a SQLExpression as a report field and run.

Problem is we see multiple data rows, combination of both the SQLExpression connection and the Dataset connection.   

Crystal Reports for Visual Studio

//An Example code demonstrating the problem:   System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(string.Format("{0} WHERE ORDERS.ORDERID > 1", sql), connectionString);                                  da.Fill(ds);                 ds.WriteXml(@"C:\SetSQLQueryString.xml");                 boReportDocument.SetDataSource(ds.Tables[0]);                   //Set table login info                 foreach (CREngine.Table CrTable in boReportDocument.Database.Tables)                 {                     CRShared.TableLogOnInfo crtablelogoninfo = CrTable.LogOnInfo;                     CRShared.ConnectionInfo crConnectionInfo = GetConnectionInfoCREngine("XE", "CRYSTALTEST", "CRYSTALTEST");                     //crConnectionInfo.                     crtablelogoninfo.ConnectionInfo = crConnectionInfo;                     CrTable.ApplyLogOnInfo(crtablelogoninfo);                 }                 bocrystalReportViewer.ReportSource = boReportDocument;             CreateReportFile(boReportClientDocument);

The problem is setting the Report location directly to the DB Server for the SQLExpression and then using a Dataset based on the same SQL from the reports merges the data from both connections.

There are no specific SQL Expression Connection properties so the report will use it as a data source.

And because you cannot disconnect the SQL Expresssion connection the data will be merged.

The work around is use the RecordSelection Formula to add filtering and do not use a Dataset.

 

SQLExpression, DataSets, cr for vs, multiple data sources , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

SAP Crystal Reports, developer version for Microsoft Visual Studio