?
Solved

Data from multiple tables to create a Crystal Report

Posted on 2007-04-09
18
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 18879384
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 101

Expert Comment

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

mlmcc
0
 

Author Comment

by:Freerider
ID: 18880779
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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 
LVL 18

Expert Comment

by:Priest04
ID: 18881294
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
ID: 18881624
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
ID: 18881968
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
ID: 18882271
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
ID: 18882647
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
ID: 18896884
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
 
LVL 18

Expert Comment

by:Priest04
ID: 18897242
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
ID: 18911858
I need some more time to think about this...
0
 
LVL 18

Expert Comment

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

Author Comment

by:Freerider
ID: 18913946
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 1000 total points
ID: 18914918
... 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
ID: 18914922
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
ID: 18940280
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
ID: 18940514
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
ID: 18941618
You are welcome
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

765 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