How do I use Crystal Report to generate a report from two different tables

See more:

VB

VB12

Please I need urgent assistant in dynamically generating a report at run-time from two or more tables within a database using crystal report.

If I use a single table I don't have issues but trying to generate from two or more tables is the challenge I have.

Below are code snippet of how I achieved my desired result using single table and how I tried to achieve result using multiple tables

#1 - Using single table (Worked perfectly)

Private Sub GenerateReport()
    Dim strCommandText As String
    strCommandText = "SELECT * FROM LoanMaster WHERE Pending=-1"
    '
    With lblWait
        .Visible = True
        .Refresh()
    End With

    '
    Dim com As New OleDbCommand(strCommandText)
    '
    Try
        com.CommandType = CommandType.Text  'Command type
        com.Connection = con     'give connection to command

        Dim adp As New OleDbDataAdapter 'declare adapter
        adp.SelectCommand = com         'select command for adapter to worker
        Dim ds As New DataSet           'declare dataset
        adp.Fill(ds, "LoanMaster")         'fill the dataset through adapter
        Try
            'Change database path
            rpt.SetDataSource(ds)
        Catch ex As CrystalDecisions.ReportSource.EnterpriseLogonException
            MsgBox(ex.Message)
        End Try
        With CrystalReportViewer1
            .ReportSource = rpt
            .ShowPrintButton = gbBoolCanPrint
            .ShowExportButton = gbBoolCanPrint
            .RefreshReport()
        End With
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Alert!")
    Finally
        lblWait.Visible = False
    End Try
End Sub

#2 - Using multiple tables (didn't Work)

Private Sub GenerateReport()
        Dim strCommandText As String
        strCommandText = "SELECT * FROM (MembersAccount INNER JOIN LoanMaster ON MembersAccount.MemberNo=LoanMaster.MemberNo) INNER JOIN LoanTransactions ON LoanMaster.LoanID=LoanTransactions.LoanID"
        '
        With lblWait
            .Visible = True
            .Refresh()
        End With

        '
        Dim com As New OleDbCommand(strCommandText)
        '
        Try
            com.CommandType = CommandType.Text  'Command type
            com.Connection = con     'give connection to command

            Dim adp As New OleDbDataAdapter 'declare adapter
            adp.SelectCommand = com         'select command for adapter to worker
            Dim ds As New DataSet           'declare dataset
            adp.Fill(ds, "MembersAccount")         'fill the dataset through adapter
            adp.Fill(ds, "LoanMaster")         'fill the dataset through adapter
            adp.Fill(ds, "LoanTransactions")         'fill the dataset through adapter
            ',LoanMaster,
            Try
                'Change database path
                rpt.SetDataSource(ds)
            Catch ex As CrystalDecisions.ReportSource.EnterpriseLogonException
                MsgBox(ex.Message)
            End Try
            With CrystalReportViewer1
                .ReportSource = rpt
                .ShowPrintButton = gbBoolCanPrint
                .ShowExportButton = gbBoolCanPrint
                .RefreshReport()
            End With
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Alert!")
        Finally
            lblWait.Visible = False
        End Try
    End Sub