Couldn't resist trying this one out.
>From the dfm:
object Query1: TQuery
--A query with one date parameter
ParamData = <
item
DataType = ftDateTime
Name = 'dateparam'
ParamType = ptInput
end>
end
And my source:
procedure TForm1.FormCreate(Sender: TObject);
begin
query1.prepare;
end;
procedure TForm1.Button1Click(Sender: TObject);
procedure showquery;
begin
with query1 do begin
if params[0].bound then begin
showmessage('bound');
end
else showmessage('not bound');
if params[0].datatype = ftdatetime then begin
showmessage('yes');
end
else showmessage('no');
showmessage(vartostr(params[0].value));
end;
end;
begin
with query1 do begin
showmessage('A');
params[0].Clear;
showquery; // Should be: not bound, yes, blank
showmessage('B');
params[0].asdatetime := now;
showquery; // Should be: bound, yes, "now"
showmessage('C');
params[0].Clear;
showquery; // Should be: bound, yes, blank
showmessage('D');
params[0].asdatetime := now;
showquery; // Should be: bound, yes, "now"
end;
end;
>Turned out that the problem was that the query was Prepared - doing a
>Unprepare fixed it.
Well, my test worked just as expected, Prepare'd or no. BUT having said
that, I remember having a similar problem when using Delphi 4, where
Clear'ing the parameter seemed to require me to set the datatype as well as
the value (I never worked it out properly and I'm using D5 now so things
_may_ be different). Grant, could you try this code and see what you get?
It couldn't be a problem specific to parameterised Insert statements - could
it???? Did you set the datatype and paramtype of all the params before you
began?
Cheers,
Carl
-----Original Message-----
From: Grant Black [mailto:[EMAIL PROTECTED]]
Sent: Friday, 7 January 2000 8:53
To: Multiple recipients of list delphi
Subject: RE: [DUG]: Bug in parameterized query?
Turned out that the problem was that the query was Prepared - doing a
Unprepare fixed it.
Why a prepared query should not work correctly when a param is set to NULL
is entirely another matter...
> -----Original Message-----
> From: Grant Black [mailto:[EMAIL PROTECTED]]
> Sent: Friday, 7 January 2000 09:27
> To: Multiple recipients of list delphi
> Subject: [DUG]: Bug in parameterized query?
>
>
> Strange - I noticed in some e-mailed reports a whole bunch of
> data coming
> out with no dates.
>
> I was worried that it was Y2k related but it turns out to be
> the innocent
> looking code snipped & pasted below.
>
> The problems is that if I Clear the 'CollDate' Param so that
> the datetime
> field is set to NULL, then _all_ subsequent times this query
> is fired, the
> field is set to null even though the Param is being set to a correct
> DateTime.
>
> Is .Clear the wrong way to set the field to NULL - is there
> another way in a
> parameterized query?
> I could just set the date to 0.0 but I don't like the idea of
> setting magic
> numbers in a date time field...
>
> procedure AppendSummaryRecord(..., CollectionDate :
> TLongDateArray,...)
> var
> CollDateTime : TDateTime;
> begin
> with qInsertIntoTable do
> begin
> ParamByName('CadID').AsInteger := iCADID;
> ParamByName('OwnerID').AsInteger := iCustomerID;
>
> {converts from cad date time format to a TDateTime.
> If the date
> time is invalid (CAD can send 0.0 datetime then set the field
> to null}
> CollDateTime := LongDateToDateTime(CollectionDate);
> if CollDateTime <> 0 then
> ParamByName('CollDate').AsDateTime := CollDateTime
> else
> ParamByName('CollDate').Clear; {set to null - the
> reporting side
> handles this nicely}
> ...
> ExecSQL; {probably need a try ... except}
> end;
> end;
>
> Grant Black
> Team Leader - PC Tools
> SmartMove (NZ) Ltd
> Phone: +64 9 361-0219 extn 719
> Email: [EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]>
> Web :
> http://www.smartmove.co.nz <http://www.smartmove.co.nz/>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
