Solved

Data from multiple tables to create a Crystal Report

Posted on 2007-04-09
18
302 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:Freerider
  • 9
  • 8
18 Comments
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Agree.
Crystal doesn't particularly like multiple data sources unless you are usng subreports

mlmcc
0
 

Author Comment

by:Freerider
Comment Utility
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
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
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
 

Author Comment

by:Freerider
Comment Utility
Priest04,

The tables are linked properly - with INNER JOIN.
Instead of passing through code, how would I insert directly in CR designer?
0
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
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
 

Author Comment

by:Freerider
Comment Utility
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
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
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
 

Author Comment

by:Freerider
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 18

Expert Comment

by:Priest04
Comment Utility
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
 

Author Comment

by:Freerider
Comment Utility
I need some more time to think about this...
0
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
If something troubles you that I have posted, feel free to ask, I will explain again...
0
 

Author Comment

by:Freerider
Comment Utility
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
 
LVL 18

Accepted Solution

by:
Priest04 earned 250 total points
Comment Utility
... 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
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
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
 

Author Comment

by:Freerider
Comment Utility
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
 

Author Comment

by:Freerider
Comment Utility
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
 
LVL 18

Expert Comment

by:Priest04
Comment Utility
You are welcome
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now