Crystal report with Two or More Tables

See more:

VB

Dear all,

I try to make a report with using two tables but facing a problem. I have a two table Bills and Sales.

Bills is a master Table and sales takes a detail infromation. When I run Crystal report with a single table Bills it running Ok but when I add the field of second table Sales the report showing nothing.

It mean it is not connecting with one another.But same command if I put in SQL Query it will run ok. It shows complete data of Bills and sales.

If you see the under mention code it will run ok when i remove Sales Dbf field from the report page but when I add Sales Dbf field not showing error. Kindly guide about the mistake.

Option Strict On
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports CrystalDecisions.CrystalReports.Engine
Public Class report
    Dim cryret As New ReportDocument
    Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load
        Dim rpt As New Invprt
        Dim myConnection As SqlConnection
        Dim MyCommand As New SqlCommand()
        Dim myDA As New SqlDataAdapter()
        Dim myDS As New Billing   'The DataSet you created.

        Try
            myConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=F:\jatin\sale\jatin_Data.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True")
            MyCommand.Connection = myConnection

            MyCommand.CommandText = "SELECT bills.docno" & _
          ", bills.glc, bills.tc,sales.qty  FROM BILLS INNER JOIN sales ON bills.tc = sales.tc and bills.docno=sales.docno order by bills.tc,bills.docno"

            MyCommand.CommandType = CommandType.Text
            myDA.SelectCommand = MyCommand

            myDA.Fill(myDS, "BILLS")
            rpt.SetDataSource(myDS)
            CrystalReportViewer1.ReportSource = rpt

        Catch Excep As Exception
            MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub
End Class