Data from multiple tables to create a Crystal Report
Posted on 2007-04-09
I am using VS2005 VB.NET to print an invoice from my Dataset.
I am using this method to push the data to the report:
---Code starts ----
Dim objConn As OleDbConnection
Dim daT1, daT2 As OleDbDataAdapter
Dim DataSet1 As DataSet
Dim strConnection As String
Dim strSQL As String = ""
strConnection = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & Application.StartupPath & "\Customers.mdb"
objConn = New OleDbConnection(strConnection)
strSQL = "SELECT * FROM Invoices"
daT1 = New OleDbDataAdapter(strSQL, objConn)
DataSet1 = New DataSet
strSQL = "SELECT * FROM InvoiceLines WHERE (InvoiceLines.Line_InvoiceNumber) = '" & InvoiceNumberToPrint & "'"
daT2 = New OleDbDataAdapter(strSQL, objConn)
Dim rpt As New rptInvoices
CrystalReportViewer1.ReportSource = rpt
--- Code ends ---
Using this method I can create a report with all the required Invoice details as the report editor also contains the required Tables.
What I need to do, is include the Customer information (address) from the Customers table.
I thought I could do it by adding the fields from the Customers table in the report editor and adding the following code:
strSQL = "SELECT * FROM Customers
daT3 = New OleDbDataAdapter(strSQL, objConn)
Now, when I run the report which includes even one field from the Customers table, the report is blank.
Can anyone help me get information from three tables (which all have a relationship) into my report?
Thanks in advance.