Passing null value to Crystal Reports parameter

[PK]

Hi, I have an application that opens a Crystal report document and
passes in a value to a parameter in the report (pointing to an Oracle
DB). However, if I want to pass a "null" value to retrieve all records
that have no data for the specified field that the parameter is a
condition of (ie. SELECT * from <table> where <field> is null"), it
returns nothing. I think CR is building the SQL statement like "SELECT
* from <table> where <field> = null". If I put in a value, it retrieve
records properly. Other than creating a stored procedure to handle the
null value and build my own SQL to fetch the records (this would be
problematic for me because I want to be able to pass in multiple values
for a parameter), is there anything I can do?

Here is my code:

paramFieldDef = oRpt.DataDefinition.ParameterFields[0];
ParameterDiscreteValue discreteValue = new ParameterDiscreteValue();
ParameterValues curValues = paramFieldDef.CurrentValues;

discreteValue.Value = null;

curValues.Add(discreteValue);

[Alex Passos]

Try using this in your code:

discreteValue.Value = DBValue.Null;

instead and see if it will work.

"PK" <ko*****@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Hi, I have an application that opens a Crystal report document and
passes in a value to a parameter in the report (pointing to an Oracle
DB). However, if I want to pass a "null" value to retrieve all records
that have no data for the specified field that the parameter is a
condition of (ie. SELECT * from <table> where <field> is null"), it
returns nothing. I think CR is building the SQL statement like "SELECT
* from <table> where <field> = null". If I put in a value, it retrieve
records properly. Other than creating a stored procedure to handle the
null value and build my own SQL to fetch the records (this would be
problematic for me because I want to be able to pass in multiple values
for a parameter), is there anything I can do?

Here is my code:

paramFieldDef = oRpt.DataDefinition.ParameterFields[0];
ParameterDiscreteValue discreteValue = new ParameterDiscreteValue();
ParameterValues curValues = paramFieldDef.CurrentValues;

discreteValue.Value = null;

curValues.Add(discreteValue);

[PK]

I have tried that, and I get this error when I try to assign it:

"Value does not fall within the expected range"

Now, this might get into something more Crystal specific, but I edited
the record selection formula in the report to the following:

if IsNull({?PARAMETER}) Then
(
// returns all records where TABLE.COLUMN_A is null OR
// TABLE.COLUMN_A matches the other parameter values
// in the array (if parameter accepts multiple values)

IsNull({TABLE.COLUMN_A}) OR
{TABLE.COLUMN_A} = {?PARAMETER}
)
else
(
// returns all records where TABLE.COLUMN_A matches
// the parameter values in the array (if parameter
// accepts multiple values)

{TABLE.COLUMN_A} = {?PARAMETER}
)
This works when I pass in only 1 null parameter, but if I pass in an
array of parameter values (ie. 1, 2, null, 4, 5), the report only
returns records that meet the parameter values that were set AFTER the
null value was inserted. In the example above where the parameter
values are "1, 2, null, 4, 5", the report only retrieves records that
match values 4 and 5, ignoring 1, 2, and null.

for (int k = 0; k < strReportValue.Length; k++)
{
// discrete value
ParameterDiscreteValue discreteValue = new
ParameterDiscreteValue();
if (strReportValue[k] == "")
discreteValue.Value = null;
else
discreteValue.Value = strReportValue[k];

curValues.Add(discreteValue);
}

paramFieldDef.ApplyCurrentValues(curValues);