Data from multiple tables to create a Crystal Report

Hi all,

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)
        objConn.Open()
        strSQL = "SELECT * FROM Invoices"
        daT1 = New OleDbDataAdapter(strSQL, objConn)
        DataSet1 = New DataSet
        daT1.Fill(DataSet1, "Invoices")

        strSQL = "SELECT * FROM InvoiceLines WHERE (InvoiceLines.Line_InvoiceNumber) = '" & InvoiceNumberToPrint & "'"
        daT2 = New OleDbDataAdapter(strSQL, objConn)
        daT2.Fill(DataSet1, "InvoiceLines")

        End If
        Dim rpt As New rptInvoices
        rpt.SetDataSource(DataSet1)
        CrystalReportViewer1.ReportSource = rpt

        objConn.Close()
--- 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)
        daT3.Fill(DataSet1, "Customers")

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.
FreeriderAsked:
Who is Participating?
 
Priest04Commented:
... correct, and that is one way to do it. There is a chapter in a book I gave the link to, than explains hot to bind CR to dataset schema (page 333).

In my post, I was not referring to binding report to a dataset schema, but to an access database directly. Similar thing you do when you create a dataset schema, can be done in crystal report designer too. You add all 3 tables to crystal reports, and create links between these tables. Then you choose fields to be displayed on the report.  And in the end, all you need to do is filter records for one invoice only, using RecordSelectionFormula property.

report.RecordSelectionFormula = "{tablename.fieldname} = '" & InvoiceNumber & '"

note that there are single and doulbe quotes, so I will put space between:

{Invoices.Invoice_Number} = ' " & InvoiceNumber & ' "


And thats, all... I used to use this approach in the beginning when working with CR. Later, I found easier to maintain report if I bind it to a query instead of table(s). This is done with AddCommand option and creating paramers. I wont discuss this method, since it may confuse you, try these methods first.
0
 
Priest04Commented:
Better way I believe would be to join data and pass joined data  to crystal reports. I used extensively CR with vb6, didnt try it with vb.net, but should be the same. You need to create query like this

SELECT * FROM (Invoices i INNER JOIN Customers c ON i.customer_id=c.id) INNER JOIN InvoiceLines il ON i.id=il.invoice_id WHERE i.InvoiceNumber=" & InvoiceNumberToPrint

if you think that sometimes you will print invoice without the customer data, then replace first INNER JOIN with LEFT JOIN
0
 
mlmccCommented:
Agree.
Crystal doesn't particularly like multiple data sources unless you are usng subreports

mlmcc
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FreeriderAuthor Commented:
Thanks,
what I don't understand is why the above method works for TWO tables but not three.
I have built the SQL statement as you mentioned, but not sure how to fill the dataset with it.
This cannot be the correct way to fill a dataset with the query you suggest?:

    daT1 = New OleDbDataAdapter(strSQL, objConn)
        DataSet1 = New DataSet
        daT1.Fill(DataSet1, "Invoices")

Since the "Invoices" part is referring to the source table.
So, how do I push the data to the report?

0
 
Priest04Commented:
This tables should be connected through relation: primary key - foreign key. Suchquery can be inserted directly in crystal reports designer, or passed through code as you are doing.

In this example you are having three tables: Invoice, InvoiceLines and Customers. All three tables should have relationships between them, and you use this relationships to pass them joined in one table only.

If you still have questions, feel free to ask.

Goran
0
 
FreeriderAuthor Commented:
Priest04,

The tables are linked properly - with INNER JOIN.
Instead of passing through code, how would I insert directly in CR designer?
0
 
Priest04Commented:
How and where did you link them propertly? In the example you have posted there is no such relationship. In your first query

SELECT * FROM Invoices

you are getting all the invoices, which is not necessary - you only need one invoice

In 2nd query you are getting the invoice data - and there you only retreive data from one invoice, which is correct. But you dont create a relationship between these two tables, so no INNER JOIN exist.  The same is with Customert table, you also need to create relationship between Invoice Table and Customer Table.

Basically, you could have had only ONE query that contains a join clause for ALL 3 tables, and fill only ONE table that will hold the joined data, and pass this table to CR.

I would recommend this way, since you have already created report, and just need to pass to it data.

In crystal reports 9 designer (should be the same in your version) there is an option to add tables from database to your report, and link them visually, as you would create relationships in MS access. This will add database fields in Field explorer, which you drag&drop on your report You would also need to create parameter for report, and pass invoice number(Id) so, the report would know what invoice to display.

If you want to learn more about crystal reports in VS 2005, here are some tutorials (download the pdf file) available from crystal reports site.

http://diamond.businessobjects.com/node/222

Goran
0
 
FreeriderAuthor Commented:
I made the relationships in Visual Studio's dataset editor - The database expert shows a diagram of the linked tables.

I am able to create the following query which returns the required invoice details:

SELECT Invoices.Invoice_CustomerID, Customers.Customer_ID, Customers.Address, InvoiceLines.Line_InvoiceNumber, InvoiceLines.Line_WorkDate, InvoiceLines.Line_Description, InvoiceLines.Line_Price, Invoices.Invoice_Number, Invoices.Invoice_Type
FROM         Invoices, Customers, InvoiceLines
WHERE     (Customers.Customer_ID = 52) AND (Invoices.Invoice_CustomerID = 52) AND (InvoiceLines.Line_InvoiceNumber = '070407172350')

I'll read through the pdf you linked to and get back later when I work out how to create a parameter for report...
0
 
Priest04Commented:
Lets take a look at your query... You dont create a link between Invoices and InvoiceLines tables, so it is the wrong way. Second, you dont need to pass customerid in query, since you are passing the invoice number, and when you pass the invoice number, only one customer exist for it, so the query itslef will return this customer 52. This should be the appropriate form

SELECT Invoices.Invoice_CustomerID, Customers.Customer_ID, Customers.Address, InvoiceLines.Line_InvoiceNumber, InvoiceLines.Line_WorkDate, InvoiceLines.Line_Description, InvoiceLines.Line_Price, Invoices.Invoice_Number, Invoices.Invoice_Type
FROM         Invoices, Customers, InvoiceLines
WHERE     (Customers.Customer_ID = Invoices.Invoice_CustomerID) AND
 (Invoices.InvoiceID=InvoiceLines.InvoiceID) AND
(Invoices.InvoiceNumber = '070407172350')

If you use Invoice number as a foreigh key in InvoiceLIne table, and not the InvoiceID, then you should replace line

 (Invoices.InvoiceID=InvoiceLines.InvoiceID)

with

 (Invoices.InvoiceNumber=InvoiceLines.Line_InvoiceNumber)

Goran
0
 
FreeriderAuthor Commented:
I have been going through the tutorial and get the sample to work ok.

I'm a little unsure about the following line of code:
myOleDbDataAdapter.Fill(myDataSet, DATATABLE_NAME)

I mean, what determines the DATATABLE_NAME if I am retrieving records from three tables?
0
 
Priest04Commented:
The soultion has already beenm given to you, I dont know what was not clear. You DONT need to pass to CR Dataset that contains three tables. You can (and should) pass data from 3 tables joined in ONE table. What troubles do you have with this approach?

DATATABLE_NAME can be anything, as a matter of fact you dont event need to provide it, since there is an overloaded method Fill that takes only one parameter - DataSet. So this works too

myOleDbDataAdapter.Fill(myDataSet)

In this case a default name (for example - Table1)will be given to a new DataTable that was created during Fill method.

The important thing is what sql query are you passing to dataadapter's Select command object (strSQL variable). If can be

strSQL="SELECT * FROM Customer"

after calling the Fill method and passing empty dataset, the result will be 1 DataTable in DataSet that contains data from Customers table

or it can be

strSQL="SELECT * FROM Customers c INNER JOIN Orders o ON c.CustomerID=Orders.CustomerID"

after calling the Fill method and passing empty dataset, the result will still be 1 DataTable in DataSet that contains JOINED data from both Customers and Orders tables

In your case, you need to pass data joined from 3 tables, data from 1 particular order, no need to retreieve all orders, since you will be displaying only one order.

If you had problems with the query I gave you, then provide us with structure of these 3 tables, and you will get the correct sql query.

Goran
0
 
FreeriderAuthor Commented:
I need some more time to think about this...
0
 
Priest04Commented:
If something troubles you that I have posted, feel free to ask, I will explain again...
0
 
FreeriderAuthor Commented:
Goran,

I need to check I haven't done anything wrong from the beginning:

I have created my Access database using ADOX.
Tables created using the following code:

"CREATE TABLE Customers ([Customer_ID] COUNTER NOT NULL CONSTRAINT keyer PRIMARY KEY, [Address] Text(50)"

"CREATE TABLE Invoices ([Invoice_ID] COUNTER NOT NULL CONSTRAINT keyer PRIMARY KEY, [Invoice_Number] Text(15), [Invoice_CustomerID] INTEGER"

CREATE TABLE InvoiceLines ([Line_ID] COUNTER NOT NULL CONSTRAINT keyer PRIMARY KEY, [Line_InvoiceNumber] Text(15)"
-----
Obviously, there are more fields, but just for simplicity, I've shown the important ones.

I then created the Dataset Schema, adding the three tables above, using drag'n'drop.

Then I added the relations as follows:

First relation is:
Parent Table: Customers - Key Columns: Customer_ID
Child Table: Invoices - Foreign Key: Invoice_CustomerID

Second relation:
Parent Table: Invoices - Key Columns: Invoice_Number
Child Table: InvoiceLines - Foreign Key: Line_InvoiceNumber
-----

Does this look correct so far?
0
 
Priest04Commented:
parond, I made a silly typing mistake

report.RecordSelectionFormula = "{tablename.fieldname} = '" & InvoiceNumber & "'"

note that there are single and doulbe quotes, so I will put space between:

{Invoices.Invoice_Number} = ' " & InvoiceNumber & " ' "

the final output would be

{Invoices.Invoice_Number} = '1234'
0
 
FreeriderAuthor Commented:
Hi, I have recently bought murach's VB.NET db programming with ADO.NET, so have been busy reading it.

Continuing with my last post:

I create a new report and add the three required tables - unfortunately, I can only view the SQL query CR produces, not edit it - it looks like this:

 SELECT `Customers`.`Address`, `Customers`.`Contact_Name`, `Customers`.`Area`, `Customers`.`Postcode`, `InvoiceLines`.`Line_WorkDate`, `InvoiceLines`.`Line_Price`, `Invoices`.`Invoice_Number`, `Invoices`.`Invoice_Date`, `Invoices`.`Invoice_Terms`, `Invoices`.`Invoice_PaymentDays`, `InvoiceLines`.`Line_Description`
 FROM   (`Customers` `Customers` INNER JOIN `Invoices` `Invoices` ON `Customers`.`Customer_ID`=`Invoices`.`Invoice_CustomerID`) INNER JOIN `InvoiceLines` `InvoiceLines` ON `Invoices`.`Invoice_Number`=`InvoiceLines`.`Line_InvoiceNumber`
 ORDER BY `Invoices`.`Invoice_Number`

I know this shows ALL the invoices, but at least it shows all the correct information on each invoice.

So, how do I over-ride the SQL query produced by CR, with my own?
Simply putting it in the Form Load event doesn't seem to do anything.

I just re-read your last post. I guess this explains what I need to do. but where do I put the RecordSelectionFormula code?
0
 
FreeriderAuthor Commented:
Doesn't matter - I found it in the PDF.
I put report.SelectionFormula = "{Invoices.Invoice_Number} = '" & InvoiceNumberToPrint & "'"
in the Form Load event, and it worked.

Thanks for all your help.
0
 
Priest04Commented:
You are welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.