Example

The following code illustrates the use of the ItemIndex property to make sure the Params object is pointing to the first Params item:

if Crpe1.SQL.Params.ItemIndex <> 0 then Crpe1.SQL.Params[0];

SQL Params Name property

Declaration

property Name: TCrStoredProcParamName;

Type

TCrStoredProcParamName = string[128];

Description

The Name property is the key, or lookup property for the Params object. As such, it cannot be directly overwritten with a new value. Instead, setting a new value to the Name property will cause it to navigate the Params object to the matching item, or generate an error if the name does not exist. Before using the Name property to navigate through the Params, the Retrieve method should be called, or the manual Add method.

Example

This example illustrates the use of the Name property to navigate through the Params object:

Crpe1.ReportName := 'C:\Company.rpt'; Crpe1.SQL.Params.Retrieve;

{Set Params object to item 'FirstParam' name} Crpe1.SQL.Params.Name := 'FirstParam';

{Set Params item Value} Crpe1.SQL.Params.Value := 'CA'; Crpe1.Output := toWindow; Crpe1.Execute;

Note that since there is an internal index in the Params object (and most of the other Crystal VCL sub-class objects), it is not necessary to specify a subscript (i.e. Params[0].Value) every time a property is set. Once the sub-class object is pointing to a certain item, the other properties apply to that item, whether the subscript is specified or not.

VCL Reference

638

SQL Params ParamType property

Declaration

property ParamType: TCrStoredProcParamType;

Type

TCrStoredProcParamType = (spLongVarChar, spBinary, spVarBinary, spLongVarBinary, spBigInt, spTinyInt, spBit, spChar, spNumeric, spDecimal, spInteger, spSmallInt, spFloat, spReal, spDouble, spDate, spTime, spTimeStamp, spVarChar);

Description

ParamType is a read-only property that will only have a meaningful value if the Retrieve method is used to obtain Params items from the Report. It indicates what data type the Value property is expecting.

NOTE: It is not possible to change the type of a Params item at runtime. This must be done within the Crystal Reports designer.

Example

The ParamType property can be used to check the type of Value the Params item expects:

Crpe1.ReportName := 'C:\Company.rpt'; Crpe1.SQL.Params.Retrieve;

for cnt := 0 to (Crpe1.SQL.Params.Count - 1) do begin

case Crpe1.SQL.Params[cnt].ParamType of

spLongVarChar	: Crpe1.SQL.Params[cnt].Value := 'CA';
spBinary	: Crpe1.SQL.Params[cnt].Value := '0'; {?}
spVarBinary	: Crpe1.SQL.Params[cnt].Value := '10'; {?}
spLongVarBinary	: Crpe1.SQL.Params[cnt].Value := '10'; {?}
spBigInt	: Crpe1.SQL.Params[cnt].AsInteger := 1;
spTinyInt	: Crpe1.SQL.Params[cnt].AsInteger := 1;
spBit	: Crpe1.SQL.Params[cnt].AsNumber := 1; {?}
spChar	: Crpe1.SQL.Params[cnt].Value := 'CA';
spNumeric	: Crpe1.SQL.Params[cnt].AsInteger := 1;
spDecimal	: Crpe1.SQL.Params[cnt].AsFloat := 1.23;
spInteger	: Crpe1.SQL.Params[cnt].AsInteger := 1;
spSmallInt	: Crpe1.SQL.Params[cnt].AsInteger := 1;
spFloat	: Crpe1.SQL.Params[cnt].AsFloat := 1.23;
spReal	: Crpe1.SQL.Params[cnt].AsFloat := 1.23;

VCL Reference

639

spDouble	: Crpe1.SQL.Params[cnt].AsFloat := 1.23;
spDate	: Crpe1.SQL.Params[cnt].AsDate := Now;
spTime	: Crpe1.SQL.Params[cnt].Value := '12:00:00.000';
spTimeStamp	: Crpe1.SQL.Params[cnt].AsDate := Now;
spVarChar	: Crpe1.SQL.Params[cnt].Value := 'CA';
end;
end;

Crpe1.Output := toWindow;

Crpe1.Execute;

SQL Params Value property

Declaration

property Value: string;

Description

The Value property contains the actual value of the Stored Procedure Parameter. If the Retrieve method was called, the Value property will show the current value of the parameter(s) as stored in the Report.

All parameter values are stored as string values. If you wish to pass other types, they will either need to be converted to a string representation, or you will need to use one of the four other properties that can be used to read and write to the Text property using native Delphi types: AsInteger, AsFloat, AsBoolean, AsDate.

To pass a number to the Value property directly, pass the value in quotes like this: '100'. The Crystal Reports Print Engine will treat this as the value 100. To pass it via the AsInteger property do this: Params.AsInteger := 100.

If you want to set the parameter value for the Stored Procedure to NULL, pass the string constant 'CRWNull' to the Value property.

Example

The Value property is used to pass new values to a Stored Procedure Parameter:

Crpe1.ReportName := 'C:\Company.rpt'; Crpe1.SQL.Params.Retrieve;

for cnt := 0 to (Crpe1.SQL.Params.Count - 1) do Crpe1.SQL.Params[cnt].Value := 'CA';

Crpe1.Output := toWindow; Crpe1.Execute;

VCL Reference

640

SQL Params Add method

Declaration

procedure Add(ParamNumber: TCrParamNumber);

Type

TCrParamNumber = integer;

Description

The Add method adds an item to the Params object and sets the internal index to that item. It requires a Stored Procedure Parameter number as a parameter, which should be a number corresponding to a Stored Procedure Parameter in the Report. Stored Procedure Parameters are numbered starting from zero. If the Add method is used, the steps to using Params are:

1Add an item to the Params object (you must specify the Parameter number).

2Fill the Value property.

3When Execute is called, the properties will be sent to the Print Engine.

NOTE: It is easier to use the Retrieve method instead of Add, since it ensures that the number of items, and the Parameter numbers are correct for the current Report.

Example

The Add method can be used to manually add an item to the Params object, instead of using Retrieve:

Crpe1.ReportName := 'C:\Company.rpt';

{Specify Parameter Name - must be same as in Report} Crpe1.SQL.Params.Add('Parameter1'); Crpe1.SQL.Params.Value := 'CA';

Crpe1.Output := toWindow; Crpe1.Execute;

SQL Params Clear method

Declaration

procedure Clear;

VCL Reference

641