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:)