[RESOLVED] recordset result on crystal

[wizkid]

Hi RobDog888,
please help me!!!
your code is working fine.
but my problem is..
i am not getting the select * from table query values on crystal.
i was already created the report newreport.rpt.it is a blank report.
and i want the query result when i press command9 button.
nearly from 3 weeks i working on this report
please give me some solution

Private Sub Command9_Click()
    Dim oreport As CRAXDRT.report
    Dim oapp As CRAXDRT.Application
    Dim oRs As Recordset
    Dim sSQL As String
 
    Set oRs = New Recordset
    Set oRs = db_conn.Execute("SELECT * FROM table")
    Set oapp = New CRAXDRT.Application
    Set oreport = oapp.OpenReport(App.Path & "\newreport.rpt", 1) 
 
    oreport.Database.SetDataSource oRs, 3, 1
 
    CRViewer91.ReportSource = oreport
    CRViewer91.ViewReport
End sub

thank u

[RobDog888]

Ok, you say you created the report in CR but did you use the same fields from Table when you bound the textboxes to the fields?

[wizkid]

hi robdog888,
thanks for your reply.
really i don't know how to get the values to report.
i created a blankreport just adding one table to data expert in crystal report.
i haven't added any fields to report .it is completely blank.
could you please advice me how to create the report,and get the values from sql query to crystal report.
thanks
wizkid

[Hack]

Moved to reporting section

[RobDog888]

Oh, thats why. You need to open the field explorer and drag the field you want on your report to the actual report. If you dont add any fields to the report page then your report wil be blank.

[wizkid]

Hi, thanks for ur suggestion but the thing is i am not asking for the crystal report designing, i know crystal reports designing but what i wanted to do is, i wanted to run some queries and get those results on the report, i am doing some calculations for the style_code and all, so we need to show them calculations and figures on the report.

i have one more doubt,on ur code you have given as

Set oRs = New Recordset
Set oRs = db_conn.Execute("SELECT * FROM table")
Set oapp = New CRAXDRT.Application
Set oreport = oapp.OpenReport(App.Path & "\newreport.rpt", 1) 
 
[COLOR=RoyalBlue]oreport.Database.SetDataSource oRs, 3, 1[/COLOR]
 
CRViewer91.ReportSource = oreport
CRViewer91.ViewReport
End sub

i wanted to know, what will the blue colour line code do there ?

thank you,
wizkid.

[RobDog888]

It binds the ADO recordset resultset to the report but it will only show data if you have the corresponding fields bound to controls on the report. You can use a generic sql statement that brings in the desired fields in CR. Then all you need to do is modify the sql query to perform any calculations, etc.

[wizkid]

Hi RobDog888,
thanks for your reply.
ok fine.
for example i want to get the result..like select * from employee where staffid=123 into the report.
1.to get the result how should i design the report and
2.which code should i write in vb
please give me steps
thanks a lot
wizkid

[RobDog888]

Ok, in CR set your database location and create an ADO connection to it. Then select the Employees table as your source table. Once you have that part you can add the fields to your details section. Make sure you have "Save data with report" unchecked in the report properties.

next in VB do like my code example but the ADO connectionstring will be pointing to the database location. It should either be relative if its a local db or an UNC path to the server\share it resides. Then when you .Open your recordset, pass the new sql statement like you posted. The the rest is the same.

[wizkid]

Hi RobDog888,
Thanks a lot for your suggestions.

i followed all your instructions.

Dim db_conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Form_Load()
   
    Dim CRReport As CRAXDRT.Report
    Dim CRApp As New CRAXDRT.Application
    
    'Open the Crystal Report
    Set CRReport = CRApp.OpenReport("C:\Program Files\Microsoft Visual Studio\VB98\newreport\Report2.rpt")
    Set rs = New ADODB.Recordset
    db_conn.Open "Driver={Microsoft ODBC for Oracle};" & _
    "Server=TEST;" & _
    "Uid=scott;" & _
    "Pwd=tiger"
    Dim sqlq As String
    sqlq = "Select * from Employee where EmpID = 1"
    With CRReport
        .Database.Tables(1).Location = App.Path & "\MagData"
        rs.Open sqlq, db_conn, adOpenStatic, adLockReadOnly
       
  End With
        
End Sub

MagData is our oracle database
rs.Open sqlq, db_conn, adOpenStatic, adLockReadOnly -------after this statement how can i proceed? i want to show the report in CrystalActiveXReportViewer
thank you
wizkid

[wizkid]

Hi RobDog888,
Thank you very much for your help.
thanks a lot.
i got it.
it is working fine

Dim db_conn As New ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Form_Load()
   
    Dim CRReport As CRAXDRT.Report
    Dim CRApp As New CRAXDRT.Application
    
    'Open the Crystal Report
    Set CRReport = CRApp.OpenReport("C:\Program Files\Microsoft Visual Studio\VB98\newreport\Report2.rpt")
    Set rs = New ADODB.Recordset
    db_conn.Open "Driver={Microsoft ODBC for Oracle};" & _
    "Server=Test;" & _
    "Uid=Scott;" & _
    "Pwd=tiger"
    Dim sqlq As String
    sqlq = "Select empid from employee where empid like 'M4%'"
    With CRReport
        'Set the Access database path
        .Database.Tables(1).Location = App.Path & "\MaData"
        rs.Open sqlq, db_conn, adOpenStatic, adLockReadOnly
         .Database.SetDataSource rs, 3, 1
        CrystalActiveXReportViewer1.ReportSource = CRReport
       CrystalActiveXReportViewer1.ViewReport
  End With
        
End Sub

if i get any problem i will ask you.

once again thank you
wizkid

[RobDog888]

Glad its finally working for you now.

Ps, dont forget to 'Resolve' your thread so others will know its solved.