Dear DaniWeb Programmers,

please help me with my problem. im trying to fetch rows in 3 TABLES using JOined Queries
im tried the query first in MS ACCESS and it worked but when i tried to show it in Crystal report it wont show its data. Heres the code below:

Imports System.Data.OleDb

Public Class Form1
    Dim con As OleDbConnection
    Dim MyDA As OleDbDataAdapter
    Dim result As New DataSet
    Dim q2 As New OleDbCommand
    Dim sql As String
    Dim sort As String

    Public userType As String

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call BindReport()
    End Sub

    Sub BindReport()

        Dim dbProvider As String
        Dim dbSource As String
        Dim fldr As String

        dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        fldr = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\CrystalReport\CrystalReport\db.mdb;"
        dbSource = "Data Source = " & fldr

        con = New OleDbConnection(dbProvider & dbSource)
        con.Open()

        sql = "SELECT order_history.orderNO,   order_history.quantity, order_history.totalPrice,  order_history.dateOfPurchase," _
         & " Clients.LastName, Clients.FirstName, Clients.MidName," _
        & " Inventory.brandName" _
        & " FROM order_history, Clients, Inventory" _
        & " WHERE Clients.clientID = order_history.clientID AND" _
        & " Inventory.itemNO = order_history.itemNO"

        MyDA = New OleDbDataAdapter(sql, con)

        Dim myDS As New DataSet1()

        'This is our DataSet created at Design Time      
        MyDA.Fill(myDS, "Clients")
        'You have to use the same name as that of your Dataset that you created during design time
        Dim oRpt As New CrystalReport1()
        ' This is the Crystal Report file created at Design Time
        oRpt.SetDataSource(myDS)
        ' Set the SetDataSource property of the Report to the Dataset
        CrystalReportViewer1.ReportSource = oRpt
        ' Set the Crystal Report Viewer's property to the oRpt Report object that we created
    End Sub
End Class

i really need this to work. so guys if you have time please help me. thanks:)

hey guys im still looking for anwers thanks!

You have to load your crystal report

oRpt.Load("<full-path-of-crystall-report-file>")

at line no. 45

I hope it will help you

Best Of Luck.

why don't you try for sql connection??

@shilu2
Whts the region for using SQL connection.
There is no any region using SQL or access for crystal report

You have to load your crystal report

oRpt.Load("<full-path-of-crystall-report-file>")

at line no. 45

I hope it will help you

Best Of Luck.

i tried this code but it still doesnt work:(

by the way i change it to this line.

oRpt.Load("C:\folder\folder\crystal1.rpt")

this is what your trying to tell me right?

by the way guys this code actually works with 1 TABLE in dataset what i trying to do now is to add 2 more tables and join query them and show it in crystal report

thx for all the replies :)

up this one

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.