Empty Crystal Report opened with SQL recordsource in VB 6 1

[Saners]

I have created a report in Crystal Report 9 with a single date parameter. I call this report in some VB 6 code (below). The problem I have is that an empty report is printed each time I run the code with a valid date. (Running the stored procedure independently with a valid date always returns records). I do not seem to be linking my recordsource (an SQL stored procedure apsp_srptDispensation) to my report and a blank report is printed each time. (i.e headers are printed and no detail records).

All suggestions appreciated.


The VB 6 code is:


Dim crCrystal As CRAXDRT.Application 'loads report from file
Dim crReport As CRAXDRT.Report 'holds report
Dim strError As String
Dim avarparameters(1, 5)
Dim rst As adodb.Recordset

' 1. Run stored procedure to get report data
' set the date parameter
'

avarparameters(0, 1) = "Run Date" 'Name
avarparameters(0, 2) = adDate ' Data type
avarparameters(0, 3) = 8 ' size
avarparameters(0, 4) = adParamInput 'direction
avarparameters(0, 5) = datLastRunDate 'value

Set rst = ExecuteSP_ADOrst("apsp_srptDispensation", "ISD_DVL1", "ADESPS", avarparameters())
If rst.EOF Then
MsgBox "No records have met the selection criteria for the Dispensation Report"
rst.Close
GoTo DispensationsReport_EXit
End If

Set crCrystal = New CRAXDRT.Application 'manages reports
Set crReport = crCrystal.OpenReport("c:\crystal parking reports\rptDispensationADHOC.rpt") 'open report

' Make sure the user isn't asked for the parameter values
crReport.EnableParameterPrompting = False

crReport.DiscardSavedData 'clears report so we can work from the recordset
crReport.Database.SetDataSource rst 'link report to recordset

crReport.PrintOut ' print the report

'Once the report is displayed, we can then cleanup out objects:

rst.Close 'ALL BELOW HERE IS CLEANUP
Set rst = Nothing

Set crCrystal = Nothing
Set crReport = Nothing

DispensationsReport_EXit:
Exit Function

 

[vidru]

Try adding DoEvents after the printing:
[tt]
crReport.PrintOut ' print the report
DoEvents
...
...
[/tt]
I couldn't duplicate the problem (my print job printed with or without the DoEvents), but if I sent the report to a CRViewer, it wouldn't have any data unless the DoEvents was in there. Also, I didn't have the problem at all if I took everything out of the function.

-dave

 

[Saners]

Many thanks Vidru. Unfortunately adding the 'DoEvents' didn't work.

Stepping through the code shows that the recordset is produced as rst.EOF is false. Yet linking the report to the recordset doesn't seem to be working. Do I have to set a value within Crystal? In the report I have 'Save Data With Report' set to false and 'Discard Saved Data when loading reports' set to true.

Any other suggestions welcomed.

 

[vidru]

When you created the report, did you use ADO as the data source?

-dave

 

[Saners]

Thanks for getting back.

No I used the ODBC (RDO) data source to link to my SQL 7 database. Does this make a difference?

Something else... the recordcount returned in the VB code is -1 which Crystal cannot generate a report from. Do you think this is an issue and if so what do I do to get round the problem?

 

[vidru]

The record count being -1 could be because you're using a server-side cursor for the recordset. Try setting it to client side to see if it makes any difference:
[tt]
rst.CursorLocation = adUseClient
[/tt]
You could also throw in a Debug statement to check for a value. If there's a field that should always return a value (i.e. never be NULL), have a look at the first row in the recordset to see if it's got a value:
[tt]
Debug.Print rst("YourField")
[/tt]
Don't have CR9 here, but in 8.5, as long as I've set up the report to expect Active Data, it doesn't seem to matter if the report was set up using RDO - it accepts an ADO recordset at runtime.

-dave

 

[Saners]

Thanks Dave.

Tried your suggestion but setting the Cursor Location made no difference.

I put a Debug.Print in the code which correctly displayed the recordset field. So the recordset IS being returned.

How do I set the report to expect Active Data? I could try that.

 

[vidru]

I ran a test with CR9 using ODBC(RDO) as the data source for the report. In VB, I create an ADO recordset from a stored procedure, then pass that to the report and I get data.

Is it possible that the stored procedure is returning something different than the report is expecting? You might try recreating the report, and after choosing ODBC(RDO) as your data source, use a Command. The text of the command should be your procedure name with hardcoded parameters, like this:
[tt]
ReportCasesEnteredInDateRange '5/20/04', '5/25/04'
[/tt]
-dave

 

[Saners]

Thanks once more. I finally got this to work by setting

rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic

You advice has been much appreciated.

 

[vidru]

I was wondering about this yesterday. Glad you got it resolved.

-dave