Why is it the query doesnt work

[psychofoo]

Dim oapp As CRAXDRT.Application
    Dim oreport As CRAXDRT.Report
    Dim oRs As Recordset
    Dim sSQL As String
 
    Set oRs = New Recordset
    Set oRs = connect.Execute("SELECT * FROM tCommission where contno like '" & NContNo & "'")
    Set oapp = New CRAXDRT.Application
    Set oreport = oapp.OpenReport(App.Path & "\report1.rpt", 1) '\rptCommission
    oreport.Database.SetDataSource oRs, 3, 1
    RptCommission.ReportSource = oreport
    RptCommission.ViewReport

it always display the first record in the table.. help pls...

[pnish]

Normally when you use the Like statement in an SQL query you would also use wildcards. Try this and see if it makes a difference:

Set oRs = connect.Execute("SELECT * FROM tCommission where contno like '%" & NContNo & "%'")

Note the percent signs. This SQL would find any contno that contained NContNo.

[psychofoo]

thanx for d reply... still.. it doesnt work... by the way, im try a new approach which is using a stored procedure but my problem is the code in vb6... dnt know how to link the crystal report using stored procedure...

[pnish]

Sorry. I've never used stored procedures with Crystal so I'm probably not much use to you.

Good luck.

[psychofoo]

can u tell me how u display ur query in Crystal?

[pnish]

Up until very recently I used the Crystal API for reporting, but am now starting to use theCrystal RDC and the Report Viewer. I use 'Selection Formulas' rather than queries, similar to the following:

Option Explicit
 
Dim Report As New CrystalReport1
Dim crProp As CRAXDRT.ConnectionProperties
 
Private Sub Form_Load()
 
    Screen.MousePointer = vbHourglass
    
    Set crProp = Report.Database.Tables(1).ConnectionProperties
    crProp.Item("Database Name") = App.Path & "\mydb.mdb"
    crProp.Item("Database Password") = "mypassword"
    
    [color=red]Report.RecordSelectionFormula = "{mytable.myfield}='condition'"[/color]
    
    CRViewer91.ReportSource = Report
    CRViewer91.ViewReport
    Screen.MousePointer = vbDefault
 
End Sub

So I guess your query would look something like:

Report.RecordSelectionFormula = "{tCommission.contno} Like '%" & NContNo & "%'"