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
com.Connection = con
Dim adp As New OleDbDataAdapter
adp.SelectCommand = com
Dim ds As New DataSet
adp.Fill(ds, "LoanMaster")
Try
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
com.Connection = con
Dim adp As New OleDbDataAdapter
adp.SelectCommand = com
Dim ds As New DataSet
adp.Fill(ds, "MembersAccount")
adp.Fill(ds, "LoanMaster")
adp.Fill(ds, "LoanTransactions")
Try
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