Crystal Report with SQL Statement

[bradth]

Alright, here's my problem. I have created a query that I have imported into crystal reports that runs fine. When I preview my report, it displays all the data that it is supposed to. I also have a VB form that asks you for the Start Date, End Date, and a Number. It then displays the data from the same query and displays the data in a grid (Janus Gridex). I have a print button on the bottom of the form that when pressed it is to bring up the crystal report created with the data that is filtered by the start date, end date, and number. When I press print, all I have is the crystal report coming up with the column headings as they should be, but with no data. Anyone have any idea?

 

[synapsevampire]

You might consider posting what's in the print button code.

The version of your software is also important, perhaps the sample VB Crystal apps will be of use to you:

http://support.businessobjects.com/communityCS/TechnicalPapers/apps_rdc.pdf.asp

-k

 

[bradth]

I am using Crystal Reports version 8 and VB editor.

Here is the code from the print button:

Private Sub PrintC_Click()
    'Open the SQL connection
    Set cn = New ADODB.Connection
    'Concatenate string to connect to the SQL database
    constring = "Provider=MSDASQL" & _
            ";Data Source=" & sqlDataSourceName & _
            ";User ID=" & userId & _
            ";Password=" & sqlPassword & _
            ";Initial Catalog=" & interCompanyId

    With cn
        .ConnectionString = constring
        .CursorLocation = adUseNone
        .Open
    End With

    strStartDate = Me.startdate & " 00:00:00"
    strEndDate = Me.enddate & " 00:00:00"
    strMORange = "," & Me.monumber & ","

    Set rst = New ADODB.Recordset
    strSQL = "execute dbo.test '" & strStartDate & "','" & strEndDate & "','" & strMORange & "'"

    
    rst.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    
    
    Set crxReport = crxApplication.OpenReport(RPT1, 0)
    crxReport.ParameterFields(1).SetCurrentValue (strStartDate)
    crxReport.ParameterFields(2).SetCurrentValue (strEndDate)
    crxReport.ParameterFields(3).SetCurrentValue (strMORange)
    crxReport.Database.SetDataSource rst, 3, 1
    
    crxReport.DiscardSavedData 
    crxReport.ReadRecords 
    
    CrystalReportViewer.CRViewer1.ReportSource = crxReport 
    CrystalReportViewer.Height = 400
    CrystalReportViewer.Width = 600

    CrystalReportViewer.CRViewer1.Top = 0
    CrystalReportViewer.CRViewer1.Left = 0
    CrystalReportViewer.CRViewer1.Height = 370
    CrystalReportViewer.CRViewer1.Width = 580
    CrystalReportViewer.CRViewer1.EnableGroupTree = False 
    CrystalReportViewer.CRViewer1.EnableRefreshButton = False     
    CrystalReportViewer.CRViewer1.ViewReport 
    CrystalReportViewer.Show 

    rst.Close
    cn.Close
    Set rst = Nothing
    Set cn = Nothing

End Sub

Thanks!

 

[jbullman]

A really helpful technique is to issue the SaveAs method - save a copy of the changed report so you can look at it in Crystal and see what the vb code has created. It's a method of the Report object, and is easy to use. Put it after you change the report in your code, but before you view.

Judith Ullman

http://www.innovativeinformation.com

Metro New York Crystal Decisions Training and Consulting Partner