Crystal Report + Stored Procedure returns no data
 

Hello,

My manager is all over me because of this issue ... please can some help me

I am working on an VB6 application which use Crystal Report 8.0.1.0 for reports and the backend database is SQL Server 2005.

My report uses stored procedure to get the records from database. The Stored Procedure access multiple database to get the records.

I am having trouble with few Crystal Reports which I am calling from VB6. The report is the cumulative report and the issue is that the crystal Report is not returning any data.

I am passing 2 parameter to the report:
1. DATE passed as varchar
2. VARCHAR

Reference in Code
1. Crystal Viewer Control (cviewer.oca)
2. Crystal ActiveX Report Viewer Library 11.0
3. Crystal Reports ActiveX Designer Run Time Library 11.0

Components used in Code
1. Crystal ActiveX Report Viewer Library 11.0 (crviewer.dll)
2. Crystal Report Control (Crystl32.OCX)



Here is the code I am using

--------------------------

Option Explicit

Dim crApp As New CRAXDRT.Application
Dim crRep As CRAXDRT.Report
Dim crParamDefs As CRAXDRT.ParameterFieldDefinitions
Dim crParamDef As CRAXDRT.ParameterFieldDefinition
Dim crDBTab As CRAXDRT.DatabaseTable
Dim HasExportOptionShown As Boolean
Dim HasReportPrinted As Boolean

Dim strDefaultPrinter As String, defaultindex As Byte
Dim ValueName As String

Dim rs As New ADODB.Recordset
Dim rsItem As New ADODB.Recordset

Dim DriverName, PrinterName, Port As String
Dim DontPrint As Boolean

Dim today As Date

Private Sub Form_Load()
today = DateWithCentury(Date)
End Sub

Private Sub Command1_Click()


'''''rpt2 is Crystal Report Control on the form (Crystl32.OCX)
rpt2.PrintFileType = crptExcel50
rpt2.Destination = crptToFile
rpt2.ProgressDialog = False
rpt2.WindowState = crptMinimized
rpt2.DiscardSavedData = True

Dim filesys,demofile
Dim foldername As String
Dim fso As New FileSystemObject

'variables for date range and work formula
Dim WorkFormula, mBeginDate, mEndDate
mBeginDate = Format(CStr(Month(today)) + "/" + "1/" + CStr(Year(today)), "YYYY/MM/DD")

If CDate(today) = CDate("1/30/08") Or CDate(today) = CDate("1/31/08") Then
mBeginDate = "12/30/2007"
End If
mBeginDate = Format(mBeginDate, "YYYY/MM/DD")
mEndDate = Format(today, "YYYY/MM/DD")

WorkFormula = "{sometable.somedatefield}>=#" & CDate(mBeginDate) & " 00:00:00# AND "
WorkFormula = WorkFormula & " {sometable.somedatefield}<=#" & CDate(mEndDate) & " 23:59:59#"

Set filesys = CreateObject("Scripting.FileSystemObject")

foldername = CStr(Format(today, "mm_dd_YY"))

If fso.FolderExists("C:\FOLDER\" & foldername & "\") = False Then
fso.CreateFolder "C:\FOLDER\" & foldername & "\"
End If

rpt2.ReportFileName = "C:\FOLDER\Reports\Report.rpt"
rpt2.PrintFileName = "C:\FOLDER\" & foldername & "\Report.xls"
rpt2.DiscardSavedData = True
rpt2.ReportTitle = "From: " & mBeginDate & " To: " & mEndDate
rpt2.StoredProcParam(0) = mBeginDate
rpt2.StoredProcParam(1) = "SOMEPARAM"
LoadReport rpt2.ReportFileName, rpt2.PrintFileName, 2
End Sub

Private Sub LoadReport(ByVal reportname As String, ByVal ExcelName As String, ByVal Method As Integer)
Dim crSections As CRAXDRT.Sections
Dim crSection As CRAXDRT.Section
Dim crRepObjs As CRAXDRT.ReportObjects
Dim crSubRepObj As CRAXDRT.SubreportObject
Dim crSubReport As CRAXDRT.Report
Dim j As Integer, k As Integer
Dim i As Integer

Dim ConnectionInfo As CRAXDRT.ConnectionProperties

Dim ServerName As String
ServerName = "MYDBSERVERNAME"

Set crRep = crApp.OpenReport(reportname, 0)
'If crRep.Database.Tables(1).DllName = "crdb_ado.dll" And InStr(reportname, "SOMEREPORT") <= 0 Then
' Set ConnectionInfo = crRep.Database.Tables(1).ConnectionProperties
' crRep.Database.Tables(1).DllName = "crdb_odbc.dll"
' ConnectionInfo.DeleteAll
' ConnectionInfo.Add "DSN", ServerName
' ConnectionInfo.Add "User ID", "xxxxx"
' ConnectionInfo.Add "Password", "xxxxx"
'ElseIf crRep.Database.Tables(1).DllName = "crdb_odbc.dll" Then
'Else
crRep.Database.LogOnServer "p2ssql.dll", ServerName, "MYDBNAME", "xxxx", "xxxx"
For Each crDBTab In crRep.Database.Tables
crDBTab.SetLogOnInfo ServerName, "MYDBNAME", "xxxx", "xxxx"
Next
'End If
crRep.EnableParameterPrompting = False

With rpt2
If .DiscardSavedData = True Then
crRep.DiscardSavedData
End If
For i = 1 To crRep.ParameterFields.Count
crRep.ParameterFields.Item(i).ClearCurrentValueAndRange
crRep.ParameterFields.Item(i).AddCurrentValue .StoredProcParam(i - 1)
DoEvents
Next

Set crSections = crRep.Sections
For i = 1 To crSections.Count
Set crSection = crSections.Item(i)
Set crRepObjs = crSection.ReportObjects
For j = 1 To crRepObjs.Count
If crRepObjs.Item(j).Kind = crSubreportObject Then
Set crSubReport = crRep.OpenSubreport(crRepObjs.Item(j).SubreportName)
For k = 1 To crSubReport.ParameterFields.Count
crSubReport.ParameterFields(k).ClearCurrentValueAndRange
crSubReport.ParameterFields(k).AddCurrentValue .StoredProcParam(k - 1)
Next
End If
Next
Next
crRep.ReportTitle = .ReportTitle
rpt.ReportSource = crRep

crRep.FormulaSyntax = crCrystalSyntaxFormula '

If .SelectionFormula <> "" Then
If crRep.RecordSelectionFormula = "" Then
crRep.RecordSelectionFormula = .SelectionFormula
Else
crRep.RecordSelectionFormula = crRep.RecordSelectionFormula & " AND " & .SelectionFormula
End If
End If

End With

rpt2.Destination = Method

Select Case rpt2.Destination
Case 1 'Printer
Me.WindowState = vbMinimized
rpt.ViewReport
DoEvents
crRep.PrintOut False
Case 0 'Screen
Me.WindowState = vbMaximized
rpt.EnableDrillDown = rpt2.WindowAllowDrillDown
rpt.EnableExportButton = True
rpt.EnableGroupTree = False
rpt.EnableRefreshButton = True
rpt.EnablePrintButton = True
rpt.EnableSelectExpertButton = True
rpt.Zoom (100)
rpt.ViewReport
Case 2 'Excel
Me.WindowState = vbMinimized
rpt.ViewReport
DoEvents
HasExportOptionShown = False
Export (ExcelName)
End Select

End Sub

Private Sub Export(ByVal ExcelName As String)
If Not HasExportOptionShown Then
HasExportOptionShown = True
Else
Exit Sub
End If
With crRep.ExportOptions
.ExcelTabHasColumnHeadings = True
.FormatType = crEFTExcel50
.DestinationType = crEDTDiskFile
.DiskFileName = ExcelName
End With
crRep.Export False
End Sub

Private Sub PrintOut()
If Not HasReportPrinted Then
HasReportPrinted = True
Else
Exit Sub
End If
End Sub

--------------------------

Thank you everyone for answer in advance