Hello,

I am trying to create crystal report having fields from two related tables of MYSQL database.

I created a dataset with two data tables
and created all fields of both tables

then created a relationship between them

Table 1: Customer
ID
full_name
etc.

Table 2: Customer_Payments
customer_id
balance
etc.


Then I Created a crystal report and set the datasource to the DataSet XML


Below is my code the show report which showing fields from the table customer but not from customer_payments

Plz help me.

Code:
 Public Sub show_Customer() 'Procedure to retrieve record and store to datagridview
        Me.WindowState = FormWindowState.Maximized
        Try
            sqL = "SELECT c.ID, c.full_name, c.shop_name,c.Address, c.city,c.phone,c.mobile,sum(customer_payments.balance) as balance FROM Customer c LEFT OUTER JOIN customer_payments ON customer_payments.customer_id = c.ID GROUP BY c.ID ORDER BY STR_TO_DATE(c.cDate,'%m/%d/%Y %h:%i:%s %p') DESC"
            ConnDB()
            cmd = New MySqlCommand(sqL, conn)
            da = New MySqlDataAdapter
            dt = New DataTable
            da.SelectCommand = cmd
            da.Fill(dt)
            'Fill DataGridView
            custDGV.DataSource = dt

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            conn.Close()
            cmd.Dispose()
        End Try
    End Sub

Fill DataTable from DataGridView and set as datasource for report document

Code:
 Private Sub ToolStripButton5_Click(sender As Object, e As EventArgs) Handles ToolStripButton5.Click
        dt = New DataTable
        With dt
            .Columns.Add("ID")
            .Columns.Add("full_name")
            .Columns.Add("shop_name")
            .Columns.Add("address")
            .Columns.Add("city")
            .Columns.Add("phone")
            .Columns.Add("mobile")
            .Columns.Add("balance")
        End With
        For Each dr As DataGridViewRow In custDGV.Rows
            dt.Rows.Add(dr.Cells(0).Value, dr.Cells(1).Value, dr.Cells(2).Value, dr.Cells(3).Value, dr.Cells(4).Value, dr.Cells(5).Value, dr.Cells(6).Value, dr.Cells(7).Value)
            'MsgBox(dt.Rows(dr.Index).Item(1) & "  " & dt.Rows(dr.Index).Item(7))
        Next

        Dim rptDoc As CrystalDecisions.CrystalReports.Engine.ReportDocument
        rptDoc = New CrystalReport2
        rptDoc.SetDataSource(dt)
        frmCustomerPrint.CustReportViewer.ReportSource = rptDoc
        frmCustomerPrint.ShowDialog()
        frmCustomerPrint.Dispose()
    End Sub