Hi all,

Firstly: I am using crystal version that comes with visual studio .net 2003, SQL Server and ADO.Net
I am trying to create a report that takes information from more than one table: for example, if you have a customer table and an orders table, based on a sql join query, i want the report to display all orders for a particular customer.
Now, i know my main code is not the problem because if i try to connect to just one table, it works. As soon as i try to add another table, link it in the dataset and write a join query, nothing comes up.

Here is my main code:

**********************
Public Class WebForm1

Inherits System.Web.UI.Page


''CR Variable (the actual physical report file- in this case CrystalReport1.rpt- don't specify extension
Dim crReportDocument As CrystalReport4

''ADO.NET Variables
Dim adoOleDbConnection As OleDbConnection
Dim adoOleDbDataAdapter As OleDbDataAdapter
Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
Dim dataSet As Dataset7

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here

''Build a connection string
Dim connectionString As String = ""
connectionString = "Provider=SQLOLEDB;"
connectionString += "Server=WEBMASTER;Database=Pubs;"
connectionString += "User ID=sa;Password=thames"

''Create and open a connection using the connection string
adoOleDbConnection = New OleDbConnection(connectionString)

''Build a SQL statement to query the datasource
Dim sqlString As String = ""
sqlString = "SELECT TOP 5 dbo.pub_info.pr_info, dbo.publishers.pub_name, dbo.publishers.city, dbo.publishers.state, dbo.publishers.country FROM dbo.pub_info INNER JOIN dbo.publishers ON dbo.pub_info.pub_id = dbo.publishers.pub_id"

''Retrieve the data using the SQL statement and existing connection
adoOleDbDataAdapter = New OleDbDataAdapter(sqlString, adoOleDbConnection)

''Create a instance of a Dataset
dataSet = New Dataset7

''Fill the dataset with the data retrieved. The name of the table
''in the dataset must be the same as the table name in the report.
adoOleDbDataAdapter.Fill(dataSet, "Pubs")

''Create an instance of the strongly-typed report object
crReportDocument = New CrystalReport4

''Pass the populated dataset to the report
crReportDocument.SetDataSource(dataSet)

''Set the viewer to the report object to be previewed.
CrystalReportViewer1.ReportSource = crReportDocument

End Sub

End Class

Any help Much appreciated!

I have a number of reports that use data from multiple tables. What I did was create and XML dataset that includes all of the tables I need in the report, without any relationships. I then create the crystal report based on the xml dataset and specify the relationships in the crystal report. When I runt he report I simply populate the table sin the dataset one at a time, and then runt he report.

Here's some code. Good luck.

                        oOleConn.Open()
                        s = "Select CD_DEPT, ID_APPL, Round(Sum(VL_COST),2) as VL_COST, CD_COST_CNTR, NUM_RATE, DS_UNIT, Round(Sum(NU_UNITS),4) as NU_UNITS, DS_BILLING_YEAR " & _
                                "From billing_entries " & _
                                "Where ds_fscl_year = '" & msFiscal & "' And cd_period = " & msPeriod & " "
                        oOleCmd.CommandText = s
                        oOleDA.Fill(dsRepBill3, "billing_entries")
                        oOleCmd.CommandText = "Select cd_cost_cntr, ds_cost_cntr, fl_telecomm From cost_centers"
                        oOleDA.Fill(dsRepBill3, "cost_centers")
                        oOleCmd.CommandText = "Select id_dept, ds_dept From vw_org"
                        oOleDA.Fill(dsRepBill3, "vw_org")
                        oOleCmd.CommandText = "Select id_appl, ds_sys From vw_sid"
                        oOleDA.Fill(dsRepBill3, "vw_sid")
                        oOleConn.Close()

                        RepBill.SetDataSource(dsRepBill3)

I am trying with below code, its not giving me error but, when this code is executed, I dont see any records. But, When I run it at sql I see the record.
whats the other best way to solve this problem (accessing multiple tables to dataset and dataset to report)?

what wrong did I do here? Do I need dataadapter one for each table or one for all table?

ALL SEVEN TABLES ARE ADDED TO DATASET ds.XSD
Dim conn As SqlConnection
Dim da1 As SqlDataAdapter
Dim da2 As SqlDataAdapter
Dim da3 As SqlDataAdapter
Dim da4 As SqlDataAdapter
Dim da5 As SqlDataAdapter
Dim da6 As SqlDataAdapter
Dim da7 As SqlDataAdapter
Dim connstr As String = "server=nt360dbs2;uid=test;pwd=;database=tco"
conn = New SqlConnection(connstr)
If conn.State = ConnectionState.Open Then conn.Open()
Dim ds As New DataSet

Dim qry1 As String = "Select * from claims where claims.ccanbr='" & txtccanbr.Text & "'"
da1 = New SqlDataAdapter(qry1, conn)
da1.Fill(ds, "claims")

Dim qry2 As String = "Select * from claimtextfailure where ccanbr='" & txtccanbr.Text & "'"
da2 = New SqlDataAdapter(qry2, conn)
da2.Fill(ds, "claimtextfailure")

Dim qry3 As String = "Select * from part360 where ccanbr='" & txtccanbr.Text & "'"
da3 = New SqlDataAdapter(qry3, conn)
da3.Fill(ds, "part360")

Dim qry4 As String = "Select * from claimtypecodes Inner Join Claims ON ClaimTypeCodes.[CCA Type]=Claims.CCAType where Claims.ccanbr='" & txtccanbr.Text & "'"
da4 = New SqlDataAdapter(qry4, conn)
da4.Fill(ds, "claimtypecodes")

Dim qry5 As String = "Select * from Inventorylist INNER JOIN Claims ON Claims.FPARTNOSPACE=InventoryList.PartNumber Where Claims.ccanbr='" & txtccanbr.Text & "'"
da5 = New SqlDataAdapter(qry5, conn)
da5.Fill(ds, "InventoryList")

Dim qry6 As String = "Select * from tLBRHR360 where ccanbr='" & txtccanbr.Text & "'"
da6 = New SqlDataAdapter(qry6, conn)
da6.Fill(ds, "tLBRHR360")


Dim qry7 As String = "Select * from vendorinfo INNER JOIN InventoryList ON InventoryList.PrimaryVendor=VendorInfo.VendorNum"
da7 = New SqlDataAdapter(qry7, conn)
da7.Fill(ds, "vendorinfo")

crv.Visible = True
rpt.Database.Tables(0).SetDataSource(ds)
'rpt.SetDataSource(ds.Tables(0))
crv.ReportSource = rpt
conn.Close()

this class is working find where you can view report contain Dataset:

Imports System.Data
Imports System.Data.OracleClient
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports System
Imports System.ComponentModel


Public Class RptHC
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Protected WithEvents CrystalReportViewer1 AsCrystalDecisions.Web.CrystalReportViewer
    Dim crReportDocument As HC '(HC is the report name)

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()

           End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Session("LOGIN_STATUS") = 1 Then
            If Not Page.IsPostBack Then
                showReport()

            End If
        Else
            Response.Redirect("LOGIN.ASPX")
        End If

    End Sub
    Private Sub showReport()
        Dim connectionString As String
        connectionString = ConfigurationSettings.AppSettings("xx")
        Dim objConnection As New OracleConnection(connectionString)
        Dim oOracleDataAdapter As New OracleDataAdapter
        Dim strSql As String = "SELECT * FROM v_HC "
        Dim dsHC As New DataSet("V_HC")
        Dim oComm As New OracleDataAdapter(strSql, connectionString)
        oComm.Fill(dsHC, "V_HC")
        Me.crReportDocument = New HC
        crReportDocument.SetDataSource(dsHC)
        CrystalReportViewer1.ReportSource = crReportDocument


    End Sub


    Private Sub CrystalReportViewer1_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Init
        showReport()
    End Sub
    Private Sub CrystalReportViewer1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer1.Load
        showReport()
    End Sub
End Class

Good luck,

Hi divinyl.
It was a nice Issue.
let me give u a small and a simple solution for this.
This issue can be solved using two different ways. First way is by taking a single Custom table in a xsd Data set. and second will be the best one which should contain all the tables u required and a custom table ( Efficient )
So let me tell u abt the first one.
U need to take ADDNEW XSD data set and create ELEMENT that should contain all the fields u want in ur report.
Remember Just Add those fields u want in ur datast and the a Primary key.
Now. USing Programming Fill this dataset completely. i mean u said u have 2 tables.
First fill dataset with FIrst table and then using the FK . One by one Fill all the other fields of the dataset with other tbl fields.
Now u will be having only one dataset filled with all ur desired fields.
Now just Bind it to ur report and in report Connect to dataset.xsd and drag the fields.. in this report u will not able to use any parameter as u are using a custom table dataset.
hope u understand this concept.
ok
bye



Mohammed Raza Ullah Khan