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.OL
objConn = New OleDbConnection(strConnect
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_Invoice
daT2 = New OleDbDataAdapter(strSQL, objConn)
daT2.Fill(DataSet1, "InvoiceLines")
End If
Dim rpt As New rptInvoices
rpt.SetDataSource(DataSet1
CrystalReportViewer1.Repor
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.
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.OL
objConn = New OleDbConnection(strConnect
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_Invoice
daT2 = New OleDbDataAdapter(strSQL, objConn)
daT2.Fill(DataSet1, "InvoiceLines")
End If
Dim rpt As New rptInvoices
rpt.SetDataSource(DataSet1
CrystalReportViewer1.Repor
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.
Agree.
Crystal doesn't particularly like multiple data sources unless you are usng subreports
mlmcc
Crystal doesn't particularly like multiple data sources unless you are usng subreports
mlmcc
ASKER
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?
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?
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
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
ASKER
Priest04,
The tables are linked properly - with INNER JOIN.
Instead of passing through code, how would I insert directly in CR designer?
The tables are linked properly - with INNER JOIN.
Instead of passing through code, how would I insert directly in CR designer?
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
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
ASKER
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_CustomerI
FROM Invoices, Customers, InvoiceLines
WHERE (Customers.Customer_ID = 52) AND (Invoices.Invoice_Customer
I'll read through the pdf you linked to and get back later when I work out how to create a parameter for report...
I am able to create the following query which returns the required invoice details:
SELECT Invoices.Invoice_CustomerI
FROM Invoices, Customers, InvoiceLines
WHERE (Customers.Customer_ID = 52) AND (Invoices.Invoice_Customer
I'll read through the pdf you linked to and get back later when I work out how to create a parameter for report...
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_CustomerI
FROM Invoices, Customers, InvoiceLines
WHERE (Customers.Customer_ID = Invoices.Invoice_CustomerI
(Invoices.InvoiceID=Invoic
(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=Invoic
with
(Invoices.InvoiceNumber=In
Goran
SELECT Invoices.Invoice_CustomerI
FROM Invoices, Customers, InvoiceLines
WHERE (Customers.Customer_ID = Invoices.Invoice_CustomerI
(Invoices.InvoiceID=Invoic
(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=Invoic
with
(Invoices.InvoiceNumber=In
Goran
ASKER
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(my
I mean, what determines the DATATABLE_NAME if I am retrieving records from three tables?
I'm a little unsure about the following line of code:
myOleDbDataAdapter.Fill(my
I mean, what determines the DATATABLE_NAME if I am retrieving records from three tables?
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(my
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.Custom
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
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(my
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.Custom
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
ASKER
I need some more time to think about this...
If something troubles you that I have posted, feel free to ask, I will explain again...
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
parond, I made a silly typing mistake
report.RecordSelectionForm
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'
report.RecordSelectionForm
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'
ASKER
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`
FROM (`Customers` `Customers` INNER JOIN `Invoices` `Invoices` ON `Customers`.`Customer_ID`=
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?
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`
FROM (`Customers` `Customers` INNER JOIN `Invoices` `Invoices` ON `Customers`.`Customer_ID`=
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?
ASKER
Doesn't matter - I found it in the PDF.
I put report.SelectionFormula = "{Invoices.Invoice_Number}
in the Form Load event, and it worked.
Thanks for all your help.
I put report.SelectionFormula = "{Invoices.Invoice_Number}
in the Form Load event, and it worked.
Thanks for all your help.
You are welcome
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