How to set RPX Subreport's connection/datasource at runtime in vb.net

How to set RPX Subreport's connection/datasource at runtime in vb.net

Posted by: softcomlimited on 30 June 2019, 7:57 am EST

• 

  softcomlimited

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 30 June 2019, 7:57 am EST

  Hello,
  
  I am able to load a normal .rpx report and set its DataSource at runtime in vb.net.
  
  I am now adding a subreport to the first report but I can’t figure out how to either set the datasource of the subreport or
  
  maybe make it use the same connection as the MainReport while I set the SQL for a datasource in the Detail_Format of the MainReport
  
  I want the ability to use the Standalone Report Designer to change the Main/Sub.rpx files and let the application set the db connection.
  
  I am using AR12Pro and VS2017
  
  Thanks
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 30 June 2019, 8:23 am EST

  I forgot to say that I tried the following script in the main rpx but me.Datasource is not valid.

  Dim rpt As New GrapeCity.ActiveReports.SectionReport

  Sub ActiveReport_ReportStart
  
  Dim xtr As New System.Xml.XmlTextReader(“D:\Test\RPXReports\subrptCurrentLoans.rpx”)
  
  rpt.LoadLayout(xtr)
  
  xtr.Close()
  
  End Sub

  Sub Detail_Format
  
  Dim subDS As New GrapeCity.ActiveReports.Data.SqlDBDataSource
  
  subDS.ConnectionString = CType(me.DataSource, GrapeCity.ActiveReports.Data.SqlDBDataSource).ConnectionString
  
  subDS.SQL ="Select * From vwCurrentLoans WHERE LoanNo = " + me.LoanAppNo
  
  rpt.DataSource = subDS
  
  me.subrptCurrentLoans.Report = rpt
  
  End Sub

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 30 June 2019, 4:59 pm EST

  Hello,

  The script that you have used to assign the DataSource to the SubReport requires a small change. To pass Main Report’s DataSource to SubReport, use rpt.DataSource instead of Me.DataSource. Further, use some other name in place of “rpt” for referring the SubReport to avoid any exceptions.

  Sub Detail_Format	
  	childDataSource.ConnectionString = CType(rpt.DataSource, GrapeCity.ActiveReports.Data.OleDBDataSource).ConnectionString
  	childDataSource.SQL = "SELECT * FROM Products WHERE CategoryID = " + Me.txtCategoryID1.Value.ToString
  	subrpt.DataSource = childDataSource
  	SubReport1.Report = subrpt
  End Sub
  

  Refer the below mentioned documentation link elaborating on how to pass DataSource to SubReport at runtime:
  
  https://help.grapecity.com/activereports/webhelp/AR13/webframe.html#arWLKScriptForSubreports.html

  Reports have been attached here for your reference.

  Regards,
  
  Esha
  
  SubReportinReport.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 1 July 2019, 5:07 pm EST

  Thank you Esha I will try this.
  
  Regards
  
  Brian

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 1 July 2019, 5:44 pm EST

  Esha,
  
  The important point is that “rpt” is always a special object that refers to the current report instead of Me, am I correct?

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 2 July 2019, 4:12 am EST

  Esha,
  
  The standalone test works perfectly, thanks.
  
  Trying to use it in my application where I have a DataTable with the info for the main report
  
  Dim ds As New DataSet
  
  Dim da As New SqlDataAdapter(cmd)
  
  da.Fill(ds)
  
  Dim dt As DataTable = ds.Tables(0)
  
  rpt.DataSource = dt
  
  rpt.Run(True)

  I am getting
  
  Unable to cast object of type ‘System.Data.DataTable’ to type ‘GrapeCity.ActiveReports.Data.SqlDBDataSource’.

  When it tries to do
  
  childDataSource.ConnectionString = CType(rpt.DataSource, GrapeCity.ActiveReports.Data.SqlDBDataSource).ConnectionString
  
  in the Detail_Format of the report

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 3 July 2019, 8:19 pm EST

  Hello,

  >>The important point is that “rpt” is always a special object that refers to the current report instead of Me, am I correct?
  
  That’s right while you use scripts. While using code, however, it mainly depends on the names/instances you create for the reports. The following walkthrough should be helpful in this case:
  
  https://help.grapecity.com/activereports/webhelp/AR12/webframe.html#arWLKSubreports.html

  As you are using a DataTable, you can avoid setting ConnectionString, etc for the subreport and set the report data source directly, as follows:

  Private Sub Detail_Format(sender As Object, e As EventArgs) Handles Detail.Format
       SubReport1.Report = subRpt
       SubReport1.Report.DataSource = dt.Select("StudentID=" + TextBox4.Text)
   End Sub
  

  Please refer the attached application for the implementation.

  Thanks,
  
  Esha

  prj_SectionReport_SubreportData.zip

• 

  softcomlimited

  • Post Options:
  • Link
    Copy

  Posted 9 July 2019, 11:24 pm EST

  Thanks again.