Visual Basic Crystal Reports - How to use store procedure with optional or null value params (Crystal XI and VB.Net)

Author

How to use store procedure with optional or null value params (Crystal XI and VB.Net)

Brad

2005-05-18, 9:00 pm

I'm working with CR XI (11) and VB.Net (framework 1.1). For all report I
use ms sql stored procedures with parameters. I would like to use with sp's
which have optional parameters or parameters which can be null.

For optional sql parameters:
So far it seems if I don't set a parameter in code, the code errors with
"Missing Parameter Values".

For sql parameters which allows null value
If I attempt to set a report parameter value to null (using documented
method for prior crystal version) the code bombs with invalid object msg
when I attempt to set the value to nothing (interesting note that exploring
the object in VS watch window the value is already nothing...so I'm not sure
why I have to set it to nothing...per documentation).
example:
crParameterFieldDefinition.EnableNullValue = True
Dim crParameterDiscreteValue As New ParameterDiscreteValue
Dim crParameterValues As ParameterValues
crParameterDiscreteValue.Value = Nothing
crParameterValues = crParameterFieldDefinition.CurrentValues
crParameterValues.Add(crParameterDiscreteValue)
crParameterFieldDefinition.CurrentValues.Add(crParameterValues)

Sponsored Links