Incorrect prompt syntax for string value = ''

[carbona]

I have a report in CE that accesses a stored procedure with multiple input parameters. When I try to run the report from eportfolio (activeX viewer), I am forced to enter a value for all NUMBER datatype parameters. However for STRING datatype params, I am not forced to enter a value, but anytime I don't enter a value for a string param datatype, I get the error message:

"The syntax of the value for prompt @promptname is incorrect near ''. Please correct the syntax and try again"

If I check the set value to null checkbox, or enter any character for the parameter value, I don't get the message, but I need a way to run the report whether or not a value is entered.

 

[Turkbear]

A stored procedure expects to find all of its INPUT parameters in the call to it ( at least in Oracle it will)
..so set the default value for the parameters to NULL or "" , but have it prompt on each run..That way you can leave the NULL if no value is needed, but it will have something for that parameter. ( Note, Including a stringvalue=NULL into a where clause will result in no records being returned,since NULL is not equal to anything - at least in Oracle , so be sure the SP tests for that and handles it )

 

[carbona]

Thanks for the quick response, TurkBear.

I'm using MSSQL 2000 and in my stored procedure I specify a null default for the input parameter, incase it doesn't get passed:

@promptname VARCHAR(20)= NULL,

also I handle an empty string ('') if that gets passed:

If @promptname = ''
SET @promptname = NULL

...and the funny thing is that this works fine in Crystal Report Designer (if I don't enter any value for this prompt and don't select the set value to null checkbox). But if I do the same thing in eportfolio, I get the error. It's as if the activeX control for the parameter prompts in CE are not able to handle empty strings.

The error is on [web component server].

 

[Turkbear]

Hi,
After you published the report ( or is it an unmanaged one ?) did you set defaults and options to re-prompt for the parameters in the CMC?

 

[carbona]

Yes, in the CMC for this prompt I've set

Set to NULL value = checked (I've also tried this unchecked)
Clear the current parameter value = unchecked
Prompt the user = checked