Hi delhigle,
For displaying data from multiple table you need to do the following steps.
1. You need to add another datatable to the dataset(.xsd file)
2. Then in Field Explorer Right click on Database Fields -> Database Export.. -> Right click on DataSetname Refresh it.
3. Then add the new table to the Selected Tables section and click ok.
4. Now add the fields to the report as per your requirement.
5. Change the code with the below to display data from multiple table.
C#
protected void Page_Load(object sender, EventArgs e)
{
ReportDocument crystalReport = new ReportDocument();
crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
Customers dsCustomers = GetData("select * from customers GO select EmployeeID,FirstName,City,Country from Employees");
//crystalReport.SetDataSource(dsCustomers);
crystalReport.Database.Tables[0].SetDataSource(dsCustomers.Tables["Table"]);
crystalReport.Database.Tables[1].SetDataSource(dsCustomers.Tables["Table1"]);
CrystalReportViewer1.ReportSource = crystalReport;
}
private Customers GetData(string query)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlCommand cmd = new SqlCommand(query);
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (Customers dsCustomers = new Customers())
{
sda.Fill(dsCustomers);
return dsCustomers;
}
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim crystalReport As ReportDocument = New ReportDocument()
crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"))
Dim dsCustomers As Customers = GetData("select * from customers GO select EmployeeID,FirstName,City,Country from Employees")
crystalReport.Database.Tables(0).SetDataSource(dsCustomers.Tables("Table"))
crystalReport.Database.Tables(1).SetDataSource(dsCustomers.Tables("Table1"))
CrystalReportViewer1.ReportSource = crystalReport
End Sub
Private Function GetData(ByVal query As String) As Customers
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand(query)
Using con As SqlConnection = New SqlConnection(conString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dsCustomers As Customers = New Customers()
sda.Fill(dsCustomers)
Return dsCustomers
End Using
End Using
End Using
End Function
