Why is it the query doesnt work

VB Code:

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...

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:

VB Code:

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

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

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...

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

Good luck.

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

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:

VB Code:

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:

VB Code:

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