How to Update the SetdataSource dynamically on the Crystal Report

[former_member1319955]

We are using Visual Studio 2015 & SAP crystal reports v13.

Step 1: I’m fetching the Query from the crystal report by using the GetCommandText() function.

Following the code is GetCommandText() function.

Dim boReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument

Dim boReportClientDocument As CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument = boReportDocument.ReportClientDocument

Dim ISCRGroupPath As CrystalDecisions.ReportAppServer.DataDefModel.ISCRGroupPath

Dim SqlQuery As String = ""

boReportClientDocument.RowsetController.GetSQLStatement(ISCRGroupPath, SqlQuery)

Step 2: I’m trying to concatenate the where condition into the SQL query.

Step 3: Then the Query is Execute and assigned to a dataset.

Step 4: And Set the Dataset value into the CrystalReport.setdatasource.

If I am trying to use the Specific field in the Select query for binding the crystal report, I am getting the attached error.

In the same condition if I am trying to change the Specific filed in to “Select * From” the crystal report is loading correctly. But the “where” condition is not getting considered. The crystal report is loading all the data’s

so here is how to update the query into the crystal report. The query is supporting in VB6 & not supporting in vb.net

VB.net

Dim ReportDoc As New ReportDocument

Dim ConnectionString = "Data Source=test;Initial Catalog=testDB;User ID=sa;Password=123;"

ReportDoc.Load("~\CrystalReportTesting\CrystalReport1")

Dim con As SqlConnection = New SqlConnection(ConnectionString)

Dim cmd As SqlCommand = New SqlCommand(SqlQuery, con)

Dim adapter As New SqlDataAdapter(cmd)

Dim dtset As New DataSet

adapter.Fill(dtset, "Dataset1")

ReportDoc.SetDataSource(dtset.Tables.Item(0))

CrystalReportViewer1.ReportSource = ReportDoc

CrystalReportViewer1.Refresh()

VB6

fReport.Report.SQLQueryString = fReport.Report.SQLQueryString & _

" where ""Invoice"".""Ref"" in (" & 1,2,3 ") " & sqlOrderby