CR & VB Not returning expected results?

I'm running a report within a VB wrapper. However the data returned isn't what's expected.

Attached is what I get returned when running the following code: -

VB Code:

Option Explicit
 
Dim crxApp As New CRAXDRT.Application
Dim CRXRpt As CRAXDRT.Report
Dim CRXDB As CRAXDRT.Database
Dim CRXTbl As CRAXDRT.DatabaseTable
 
 
Private Sub Form_Load()
 
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim crxReport As CRAXDRT.Report
Dim crxApp As CRAXDRT.Application
Dim crxTable As CRAXDRT.DatabaseTable
Dim sql As String
 
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set crxApp = New CRAXDRT.Application
Set crxReport = crxApp.OpenReport("C:\Test.rpt", 1)
 
sql = "SELECT TB_CASHREGS.DESCRIPTION, TB_TRANS_CARDS.LOAD_CASH, TB_TRANS_CARDS.SALDO_CASH, " & _
                "TB_CUSTOMERS.CARD_NUM, TB_CUSTOMERS.DESCRIPTION " & _
                "FROM TB_TRANSACTIONS, TB_CASHREGS, TB_CUSTOMERS, TB_TRANS_CARDS " & _
                "WHERE  TB_TRANSACTIONS.CASHREG_ID = TB_CASHREGS.ID " & _
                "AND TB_TRANSACTIONS.CARD_NUM = TB_CUSTOMERS.CARD_NUM " & _
                "AND TB_TRANSACTIONS.ID = TB_TRANS_CARDS.TRANSACTION_ID " & _
                "AND TB_TRANSACTIONS.CARD_NUM = '1052'"
 
cn.Open "Provider=OraOLEDB.Oracle; Data Source=tcpos; User Id=lyons; Password=matrix;"
 
rs.Open sql, cn, adOpenKeyset
 
'now verify all tables within the report
 For Each crxTable In crxReport.Database.Tables
      With crxTable.ConnectionProperties
        .Item("Provider") = "OraOLEDB.Oracle"
        .Item("Data source") = "tcpos"
        '.Item("Initial Catalog") = " " 'if none, use ""
        .Item("User ID") = "lyons"
        .Item("Password") = "matrix"
      End With
Next
 
    crxReport.DiscardSavedData
    crxReport.Database.SetDataSource rs
      
    CRViewer91.ReportSource = crxReport
    CRViewer91.ViewReport
    
End Sub

This isn't the same as when run through crystal or when the query is run directly against the DB.

As can be clearly seen the records are just the first repeated.

Any idea what I've missed?

I've looked through the Crsytal help, but nothing, to be honest not the best on-line help :(

Any help would be appreciated.

Regards

No idea why you are getting duplicate records but for starters comment out all the code that deals with the ADO recordset. The report will connect to Oracle on its own. Does setting the Datasource to a Recordset over-ride the retrieving data directly from Oracle? Or will Crystal link both?

Thanks for the reply.

The reason I've got the ADO in the code is because I want to develope the report so that the user can select different Card Numbers (CRAD_NUM) and then run the report, rather than having it as static.

This is possible isn't it?

Regards

Usually its one or the other. IE the report does all (or most of the work) by connecting to the database or your VB code connects and passes the data source to the report.

Create a Parameter in your report. You can then prompt for and pass the parameter value from VB to the report.

Try this one if it works.

VB Code:

Set crxReport = crxApp.OpenReport("C:\Test.rpt")
     .
     .
     .
     crxReport.Database.SetDataSource rs, 3, 1

BTW, I would rather use SPROC rather than using a dynamic recordset populating the report. Once you access your report in your VB program, it CR automatically prompts for the parameter that the end user needs to type in.

I guess if the report is a simple one it is OK. However, I later found out that this was totally challenging and inflexible when the report gets more complex especially when you have sub-reports. So basically, I droped this method (ADO - dynamic rs) of populating my CR report.

Or as Bruce pointed out, instead of a SPROC, create a parameter in your report inside CR instead.