Passing Stored procedure parameter from VB

[jpember]

I'm using the RDC in VB6 to export a CR8.5 report to .PDF format. The report is based on a SQL Server stored proc that expects 5 parameters. I've tried using the parameterfields method to add the parameters, but I get a subscript out of range error.
Can anyone help me get the parameters thru to the report?

Code is as follows:
With rdcReport
For n = 1 To .Database.Tables.Count
.Database.Tables.SetLogOnInfo "Europe"
Next n
.ExportOptions.FormatType = crEFTPortableDocFormat
.ExportOptions.DestinationType = crEDTDiskFile
.ExportOptions.DiskFileName = sPath & "\" & sReportName & ".pdf"
.ExportOptions.PDFExportAllPages = True
.ParameterFields(0) = "@dtToday;'" & sParameter(0) & "'; True"
.ParameterFields(1) = "@dtYesterday;'" & sParameter(1) & "'; True"
.ParameterFields(2) = "@dtPrMth;'" & sParameter(2) & "'; True"
.ParameterFields(3) = "@vType;'" & sParameter(3) & "'; True"
.ParameterFields(4) = "@vRerun;'" & sParameter(4) & "'; True"
.DiscardSavedData
.Export False
End With


Thx,
jacqui

 

[jpember]

Well, it seems as if my first problem is that the collection is 1 based, so I've changed my array list to start from 1 to 5. Now I'm getting a type mismatch because the first parameter is a date.

Any suggestions anyone?

 

[jpember]

Looks like I'm getting there myself! The date parameter has to be declared as a date variable in the VB program before adding it as a parameter. I've had to use the .AddDefaultValue method to get the CR to accept the values I'm passing it as the new parameter values.
It all works fine except now I'm trying to switch the prompting off by setting the .EnableParameterPrompting to false. As soon as I do this, I get an error from SQL Server saying 'Invalid Cursor State'. According to the description of this message it's saying the transaction logs are full in the database. This is in fact not true at all - heaps of space and the report is produced fine if I allow it to prompt.
Any ideas out there?

 

[kenhamady]

Try AddCurrentValue instead of AddDefaultValue. This should skip the user prompt. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jpember]

I tried that originally and again now that it's almost working, but the with AddCurrentValue, parameters are being ignored totally. I just get all the prompts as at the last time the report was produced successfully.
At least when I use the AddDefaultValue, the parameters are being set and the user just has to click on the OK button to process the export to PDF.
Most bizarre....

 

[kenhamady]

As far as I know, the difference between these two options is that one prompts and the other doesn't. Post the command syntax you are using for CurrentValue.

Also, does the report have saved data? If so, try saving the report without the data rather than discarding it at runtime.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jpember]

I have saved my report without data as you suggested and also added the CurrentValue lines of code (I first tried replacing the AddDefault but this didn't work either).

With rdcReport
.ExportOptions.FormatType = crEFTPortableDocFormat
.ExportOptions.DestinationType = crEDTDiskFile
.ExportOptions.DiskFileName = sPath
.ExportOptions.PDFExportAllPages = True
For n = 1 To 5
.ParameterFields.ClearCurrentValueAndRange
Next
For n = 1 To 3
dtParm = Format((sParameter), "dd mmm yyyy&quot
.ParameterFields.AddCurrentValue dtParm
.ParameterFields.AddDefaultValue dtParm
Next
.ParameterFields(4).AddDefaultValue sParameter(4)
.ParameterFields(5).AddDefaultValue sParameter(5)
.ParameterFields(4).AddCurrentValue sParameter(4)
.ParameterFields(5).AddCurrentValue sParameter(5)
.DiscardSavedData
.EnableParameterPrompting = False
.PrintOut True, 1
MsgBox "Report has been printed. "
End With

Still getting an error saying Invalid cursor state.

Thx,
Jacqui

 

[kenhamady]

I recommend that you try a simpler report until you get the syntax for passing a single date successfully wtihout a prompt.

The problem is probably in your date literal which needs to arrive in the report in the following syntax:

Date (yyyy,mm,dd)

You should not need to add both current and default values, and I don't know that you need any of these:

DiscardSavedData
EnableParameterPrompting = False
ClearCurrentValueAndRange
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com