Board index » delphi » Passing NULL value as actual Parameter !!!

Bernard Stibbe

Delphi Developer
Sun, 29 Feb 2004 18:42:22 GMT

Passing NULL value as actual Parameter !!!

Hello,

One simple question:

We try to pass NULL values via parameters
by using the following function:

-----------

function TfrmMain.assignParams: Boolean;
var i, j: integer;
    ChildControl: TControl;
    anEdit: TEdit;
    paramCount: integer;
begin
   result := true;
   for i:= 0 to tbsParams.ControlCount - 1 do
   begin
      ChildControl := tbsParams.Controls[I];
      if (ChildControl is TEdit) then
      begin
         anEdit := TEdit(ChildControl);
         j := 0;
         try
            paramCount := anEdit.Tag; // How many params are linked to this
Control !
            for j:= 0 to qrySQL.Parameters.Count - 1 do
            begin
               // Serach on name
               if (('edt_' + qrySQL.Parameters.Items[j].Name) = anEdit.name)
then
               begin

Quote
>>              if anEdit.Text = '' then
>>              begin
>>                 qrySQL.Parameters.Items[j].Attributes :=

qrySQL.Parameters.Items[j].Attributes + [paNullable];
Quote
>>                 qrySQL.Parameters.Items[j].Direction := pdInput;
>>                 qrySQL.Parameters.Items[j].Value := Null;
>>              end

                  else
                    qrySQL.Parameters.Items[j].Value := anEdit.text; //
Assign value
                  dec(ParamCount);
// More params ?
                  if ParamCount = 0 then break;
               end;
            end;
         except on e: exception do
            begin
               ShowMsg('Assignment error in Parameter ' +
qrySQL.Parameters.Items[j].Name, ERROR);
               ShowMsg('   Value=' +  anEdit.Text + ' , DataType=' +
FieldTypeNames[qrySQL.Parameters.Items[j].DataType], Error);

               result := False;
            end;
         end;
      end;
  end;

  if result then
     showMsg(intToStr(qrySQL.Parameters.Count) + ' Parameters assigned',
INFO);
end;
-------------------------

We set the attribute correct !!
But the programs complains that not everything has been
correct assigned or filled for the parameters.

The question are
- what do I wrong ?
- how do you pass NULL values as actual parameter to the database.

The database we use is MS-SQL V7 ?

Thanks for any advise

Regards,

Bernard Stinne

 
Michael A. Ivanovsk

Delphi Developer
Tue, 02 Mar 2004 14:45:31 GMT

Re:Passing NULL value as actual Parameter !!!


Quote
> We try to pass NULL values via parameters
> by using the following function:

> >>                 qrySQL.Parameters.Items[j].Value := Null;
> The question are
> - what do I wrong ?
> - how do you pass NULL values as actual parameter to the database.

You must set VarNull instead Null. Null is a Pointer, but Value - Variant.

--
Michael A. Ivanovsky
www.amrita.ru
i...@amrita.ru
icq 70775040

Vassil Nazaro

Delphi Developer
Tue, 02 Mar 2004 15:00:00 GMT

Re:Passing NULL value as actual Parameter !!!


Quote
> You must set VarNull instead Null. Null is a Pointer, but Value - Variant.

You've got that wrong. It's Nil that's a pointer but Null is a variant and
VarNull is an integer type constant initialized to $0001.

--
Vassil Nazarov
http://web.orbitel.bg/vassil/

Brian Nobl

Delphi Developer
Tue, 02 Mar 2004 17:09:25 GMT

Re:Passing NULL value as actual Parameter !!!


But if you want the parameter value in the stored procedure to actuially
conatin the
value 'NULL' then this is not much use as now it will hold '1' . for eg if
the store procedure is
trying to search where the specified parameter is NUll then
it will not return the result required.  The store procedure will ahve to
map the varnull ie '1' to
NULL before searching which is just not nice to have to do and maintain.

Any other ideas anyone or is this just not possible

Quote
"Vassil Nazarov" <vas...@mail.orbitel.bg> wrote in message

news:3ba1aa47_1@dnews...
Quote
> > You must set VarNull instead Null. Null is a Pointer, but Value -
Variant.

> You've got that wrong. It's Nil that's a pointer but Null is a variant and
> VarNull is an integer type constant initialized to $0001.

> --
> Vassil Nazarov
> http://web.orbitel.bg/vassil/

Bernard Stibb

Delphi Developer
Tue, 02 Mar 2004 22:13:59 GMT

Re:Passing NULL value as actual Parameter !!!


The question is still open.
I tried to set also the parameter on 'Unassigned'
see following example:

var
   qrySQL: TADOQuery;
begin

..
qrySQL.Parameters.Items[j].Attributes :=
   qrySQL.Parameters.Items[j].Attributes + [paNullable];
qrySQL.Parameters.Items[j].Direction := pdInput;
qrySQL.Parameters.Items[j].Value := UnAssigned;
..
end;

But the message 'Parameter object is improperly defined. Inconsistent or incomplete information was provided'
appears ....

So that is also not the solution.

Any other ideas ?

Bernard Stibbe

Quote
"Brian Noble" <b.no...@kainos.com> wrote:
>But if you want the parameter value in the stored procedure to actuially
>conatin the
>value 'NULL' then this is not much use as now it will hold '1' . for eg if
>the store procedure is
>trying to search where the specified parameter is NUll then
>it will not return the result required.  The store procedure will ahve to
>map the varnull ie '1' to
>NULL before searching which is just not nice to have to do and maintain.

>Any other ideas anyone or is this just not possible

>"Vassil Nazarov" <vas...@mail.orbitel.bg> wrote in message
>news:3ba1aa47_1@dnews...
>> > You must set VarNull instead Null. Null is a Pointer, but Value -
>Variant.

>> You've got that wrong. It's Nil that's a pointer but Null is a variant and
>> VarNull is an integer type constant initialized to $0001.

>> --
>> Vassil Nazarov
>> http://web.orbitel.bg/vassil/

Vassil Nazaro

Delphi Developer
Tue, 02 Mar 2004 23:04:10 GMT

Re:Passing NULL value as actual Parameter !!!


Quote
> I tried to set also the parameter on 'Unassigned'

This is not what you need. Null is ok. In your code I didn't see
initialization of the parameter's type which is most probably
the reason for the error you get.

Note that if you had a SQL like this:

... WHERE AColumn = :Parameter

and you passed Null as a value to the Parameter you would most
probably get an empty resultset. Change your SQL like this instead:

... WHERE AColumn IS NULL

Whether passing Null values to parameters works as expected depends
on the provider.

HTH
--
Vassil Nazarov
http://web.orbitel.bg/vassil/

Vassil Nazaro

Delphi Developer
Tue, 02 Mar 2004 22:52:12 GMT

Re:Passing NULL value as actual Parameter !!!


Quote
> But if you want the parameter value in the stored procedure to actuially
> conatin the
> value 'NULL' then this is not much use as now it will hold '1' . for eg if

What isn't much use? The assignment ".Value:=Null" would leave Value
containing a Null value not 1.

--
Vassil Nazarov
http://web.orbitel.bg/vassil/

MW

Delphi Developer
Mon, 29 Mar 2004 06:58:45 GMT

Re:Passing NULL value as actual Parameter !!!


try:
  where IsNull(AColumn,0) = IsNull(:Parameter,0)

You will of course have to use the correct datatype and a value that should not appear in your data.  Be sure
:Parameter is of the correct type.

Quote
"Vassil Nazarov" <vas...@mail.orbitel.bg> wrote:
>> I tried to set also the parameter on 'Unassigned'>
>This is not what you need. Null is ok. In your code I didn't see
>initialization of the parameter's type which is most probably
>the reason for the error you get.

>Note that if you had a SQL like this:

>.... WHERE AColumn = :Parameter

>and you passed Null as a value to the Parameter you would most
>probably get an empty resultset. Change your SQL like this instead:

>.... WHERE AColumn IS NULL

>Whether passing Null values to parameters works as expected depends
>on the provider.

>HTH
>--
>Vassil Nazarov
>http://web.orbitel.bg/vassil/

Other Threads

1. Passing Null Values to Query's Parameters

2. error assigning NULL to a TSQLDataSet parameter - dbExpress (Error msg: No value for parameter FldDate)

3. passing actual parameter to Events handlers?

4. Passing NULL parameters to a stored procedure

5. Passing NULL parameters via TADOQuery

6. how to pass null parameter to a stored procedure with TAdoStoredProc

7. how to pass parameter NULL?

8. Passing Null values in a query?

9. Passing null values to a stored procedure

10. More info on problem with NULL parameter values in ADO