[ RESOLVED ] Binding Two tables to a Crystal Report

[donhughberto]

Many thanks for all those who helped me to date. I have another issue. I have created a crystal report with two tables, one for company details as a header and one for report details as a filler.

I have been trying to get dates to filter the report and have done so successfully but the header part of the report does not show any data at all. I have binded the crystal report using a report viewer. Here is code i use to call crystal report. Lets say second table is called Company details, how do i get this information to show up with rescinded documents information. I have attached a copy fo what my final report will look like.


Private Sub BtnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnOpen.Click
Dim sqlsearch As String
Dim con As New OleDb.OleDbConnection

Dim hugh As Date = DateTimePicker1.Value.Date
Dim hugh2 As Date = DateTimePicker2.Value.Date.AddDays(1)


sqlsearch = "SELECT * FROM TableRescindedDocuments, TableCompany Details WHERE DateRescinded BETWEEN @startDate AND @endDate"

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\FoodQualityDB1.accdb"


' Once again we execute the SQL statements against our DataBase

Dim adapter As New OleDbDataAdapter(sqlsearch, con)

With adapter.SelectCommand.Parameters
.AddWithValue("@startDate", hugh)
.AddWithValue("@endDate", hugh2)
End With

' Shows the records and updates the DataGridView

Dim dt As New DataTable("TableRescindedDocuments")

adapter.Fill(dt)
Dim rpt As New CrystalReportRescindedDocuments

rpt.SetDataSource(dt)
RepResDoc.CrystalReportViewer1.ReportSource = rpt
RepResDoc.Show()
Me.Close()
End Sub

[cellus205]

Here's how you would set the datasource for each of the two tables. Instead of using .SetDataSource() for the entire data, just set each table individually to the specific table that you are using

MainReport.Database.Tables("CompanyDetails").SetDataSource(DataTable1)
MainReport.Database.Tables("ReportDetails").SetDataSource(DataTable2)

[donhughberto]

Thanks for reply, ok so use two data tables. U mean like this. Giving me an error,

Error 2 'SetDataSource' is not a member of 'System.Data.DataTable'. C:\Users\Hugh\Desktop\Food Quality Software - Tiziano1\BRC Test\SelectDateRescinded.vb 37 9 HMT Food Software



RepResDoc.FoodQualityDB1DataSet.Tables("CompanyDetails").SetDataSource(dt)
RepResDoc.FoodQualityDB1DataSet.Tables("TableRescindedDocuments").SetDataSource(dt)

Here's how you would set the datasource for each of the two tables. Instead of using .SetDataSource() for the entire data, just set each table individually to the specific table that you are using

MainReport.Database.Tables("CompanyDetails").SetDataSource(DataTable1)
MainReport.Database.Tables("ReportDetails").SetDataSource(DataTable2)

[donhughberto]

Dim sqlsearch As String
Dim con As New OleDb.OleDbConnection

Dim hugh As Date = DateTimePicker1.Value.Date
Dim hugh2 As Date = DateTimePicker2.Value.Date.AddDays(1)


sqlsearch = "SELECT * FROM TableCustomerComplaint, TableCompanyDetails WHERE DateComplaint BETWEEN @startDate AND @endDate"

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\FoodQualityDB1.accdb"


' Once again we execute the SQL statements against our DataBase
Dim dt As New DataSet()

Dim adapter As New OleDbDataAdapter(sqlsearch, con)

With adapter.SelectCommand.Parameters
.AddWithValue("@startDate", hugh)
.AddWithValue("@endDate", hugh2)
End With

' Shows the records and updates the DataGridView


adapter.Fill(dt, "TableCustomerComplaint")
adapter.Fill(dt, "TableCompanyDetails")
Dim rpt As New CrystalReportCustomerComplaints

rpt.SetDataSource(dt)
ReportCustomerComplaintsSelectDate.CrystalReportViewer1.ReportSource = rpt
ReportCustomerComplaintsSelectDate.Show()
Me.Close()
End Sub