Using C1ReportDesigner is one of the easiest ways to create C1Report, wherein the connection string and all other properties of the report and its objects (sections, fields, sub-reports, etc.) are set. But, there might be certain scenarios where the user wants to change the connection string of C1Report and its objects at run-time. In this blog we'll discuss Changing the Connection String of the following at runtime :

• C1Report
• C1Report's Sub-Report
• C1Report's Chart Field

C1Report

In order to set/change the connection of C1Report at run-time, one needs to access the C1Report's DataSource.ConnectionString and DataSource.RecordSource properties.The following is the code for the same :

C1Report1.DataSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\stduser\\Documents\\ComponentOne Samples\\Common\\C1NWind.mdb;Persist Security Info=False"  'use your connection string  
C1Report1.DataSource.RecordSource = "Select LastName from Employees"  'use your query string  

C1Report's Subreport

Subreport added in C1Report is considered as any other field of the C1Report and can be accessed only as C1Report.Sections.Fields. Hence, in order to change the connection string of the subreport, one needs to first access the subreport-field of the mainreport and then change the value of its DataSource.ConnectionString and DataSource.RecordSource properties. You may refer to the following code for the implementation of the same.

C1Report1.Sections("Name of the Section").Fields("Name of Field").Subreport.DataSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\stduser\\Documents\\ComponentOne Samples\\Common\\C1NWind.mdb;Persist Security Info=False"  'use your connection string  
C1Report1.Sections("Name of the Section").Fields("Name of Field").Subreport.DataSource.RecordSource = "Select LastName from Employees"  'use your query string

Note : Make sure that the 'Calculated' Property of the MainReport as well the SubReport and its fields are set to true.

C1Report's Chart Field

In order to change datasource of the C1Report's Chart Field, you need to follow the undermentioned steps :

1. Load the report xml (having the chart field)
2. Create a new datatable (either in code or using MS access) based on the customized sql statement with the same column names as the fields in the report (which act as the source for the chart's x-y axis)
3. Now, change the datasource of the report and assign this new datatable as the source to the report
4. The new data will be represented in the fields of the report and the same will be reflected in the chart

Note : Multiple charts (added in same report) cannot have different datasources but their x-axis and y-axis can be bound to different columns of the same datatable. Code used for the same is :

C1Report1.Load("Name of ReportDefinition File", "Report Name")  
C1Report1.DataSource.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Users\\stduser\\Documents\\mdb files\\TestTable.mdb;Persist Security Info=False"  
C1Report1.DataSource.RecordSource = "Select * From Table1"  
C1PrintPreviewControl1.Document = C1Report

Conclusion

Make sure that the name of the fields added in the report xml is the same as the column-name(s) in the datatable used as source. Also, ensure that the same columns are queried in the recordsource property, or else a mismatch between the data-fetched and the fields will take place. Download Sample