Crystal Report + Stored Procedure returns no data

[zambi]

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