Binding crystal report with multi table dataset

[breaknrun12]

I'm trying to add a crystal report to my application that uses a dataset with two tables. how do I fill the dataset with the data from the two tables, create the relationship between the two tables, and bind the report to the dataset? Here's the code I got working for the report when it only contained data from one table. What do I need to do add a second table so I can join the tables and display fields from both table on the report?

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim rpt As New CrystalReport1()
        Dim myConnection As SqlConnection
        Dim myCommand As New SqlCommand
        Dim myDA As New SqlDataAdapter
        Dim myDS As New DataSet1

        Try
            myConnection = New SqlConnection("Data Source=LTRSTATEDB;Initial Catalog=Northwind;User ID=sa;Password=LTRAdmin")
            myCommand.Connection = myConnection
            myCommand.CommandText = ("SELECT * FROM Customers")
            myCommand.CommandType = CommandType.Text
            myDA.SelectCommand = myCommand
            myDA.Fill(myDS, "Customers")

            rpt.SetDataSource(myDS)
            CrystalReportViewer1.ReportSource = rpt

        Catch ex As Exception

        End Try

    End Sub

 

[RiverGuy]

DataSets may contain one or more DataTables. You can fill multiple tables in a DataSet by using different DataAdapters, which have different SelectCommands. For example:

Command1.CommandText = "SELECT * FROM Table1" 
Command2.CommandText = "SELECT * FROM Table2"
DataAdapter1.SelectCommand = Command1
DataAdapter2.SelectCommand = Command2
DataAdapter1.Fill(MyDataSet, "Table1")
DataAdapter2.Fill(MyDataSet, "Table2")
Report.SetDataSource(MyDataSet)

 

[breaknrun12]

How do I join the two tables together so I can display them both on the report. Each line of the report has data from both tables like the query:

SELECT Customers.CompanyName, Orders.OrderID
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID

 

[RiverGuy]

Then your Crystal report really isn't using two tables. It's using a SQL query that is sees as a single resultset, almost as if it is using one single table.

So you have two options. Either fill up a single table in your DataSet with that Join query--DataTables don't have to resemble the physical table in the database--you can use a Join in the .SelectCommand of your DataAdapter. Or, redesign your Crystal report so that the join is performed in Crystal itself.

 

[breaknrun12]

how do I set the join in the report?

 

[RiverGuy]

In the Crystal GUI from what I remember, but I would think it would be more efficient to leave it as it is. Just change your DataAdapter command

Command.CommandText = "SELECT Customers.CompanyName, "
 Command.CommandText &= "Orders.OrderID "
Command.CommandText &= "FROM Customers INNER JOIN "
Command.CommandText &= "Orders ON Customers.CustomerID = Orders.CustomerID "

 

[breaknrun12]

I've changed the code to the below, but it doesn't populate the report. do you see anything wrong with this code?

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim rpt As New CrystalReport1()
        Dim myConnection As SqlConnection
        Dim myCommand As New SqlCommand
        Dim myDA As New SqlDataAdapter
        Dim myDS As New DataSet1

        Try
            myConnection = New SqlConnection("Data Source=xxxxxx;Initial Catalog=Northwind;User ID=xx;Password=xxxx")
            myCommand.Connection = myConnection
            myCommand.CommandText = ("SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID")
            myCommand.CommandType = CommandType.Text
            myDA.SelectCommand = myCommand
            myDA.Fill(myDS, "query")
 


            rpt.SetDataSource(myDS)
            CrystalReportViewer1.ReportSource = rpt

        Catch ex As Exception

        End Try

    End Sub

 

[RiverGuy]

I don't see anything incorrect, but if memory recalls, you have to change your connection source in Crystal to "ADO.Net (XML)" if your source is currently connected directly to the database.

Basically what this means is that you set it to a DataSet definition. You can generate an XML file to use for the source with the code myDS.WriteXML() or myDS.WriteXMLSchema(). This would only be a one-time thing. You just need an XML file to use as the source for your Crystal report. Then, you can set the datasource to your DataSet.