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
