Board index » delphi » Tdatetime field and Parameter( need help)

Tdatetime field and Parameter( need help)

Delphi5Pro, ADOExpress and MS Access97(come with Delphi5)
After I spent much time on Parameterised SQL, I am getting more and more
confused.
look at the following code,SaleDate is the Datetime field in Orders
table,All Adodataset1 properties use default.

procedure TForm1.Button1Click(Sender: TObject);
var
 Dt:TdateTime;
begin
 adoDataset1.Active:=false;
 dt:=EncodeDate(1994,4,10);
 adoDataset1.CommandText:='select  * from orders'+#13#10+
                           'where SaleDate>=:DateParam'+#13#10+
                           'Order by SaleDate';
 adoDataSet1.Parameters.ParamByName('DateParam').DataType:=ftDate;<----
return dataset depends on the control Panel  Date format setting .If format
is 'mm/dd/yy', it works fine, if I change to 'dd/mm/yy', return dataset is
wrong.
adoDataSet1.Parameters.ParamByName('DateParam').DataType:=ftDatetime;<----
return dataset also depends on the Date format setting.

adoDataSet1.Parameters.ParamByName('DateParam').DataType:=ftString;<----seem
s less depends on the Date format setting,but still has problem.

 AdoDataSet1.Active:=true;

end;

So, According the documentation if the Datatype:=ftDate it pass a double to
OLE DB provider,but it seems not. I am not sure this problem with ADO or
ADOexpress.
I look into the ADODB.pas , when ftDate parameter pass to ADO it converts to
adDBdate rather than adDate, but the MS Access Provider DATETIME field is
adDate(DBTYPE_DATE), Is this the problem?

Joe

 

Re:Tdatetime field and Parameter( need help)


Quote
Joe Li <joe...@callista.net> wrote in message

news:7u5kop$4hj14@forums.borland.com...

Quote
> is 'mm/dd/yy', it works fine, if I change to 'dd/mm/yy', return dataset is

wrong..

Wrong  how?  When I try this it works fine for me regardless of the date format
specified in the control panel.

Quote
> adoDataSet1.Parameters.ParamByName('DateParam').DataType:=ftString;<----seem
> s less depends on the Date format setting,but still has problem.

What  problem?

Quote
> I look into the ADODB.pas , when ftDate parameter pass to ADO it converts to
> adDBdate rather than adDate, but the MS Access Provider DATETIME field is
> adDate(DBTYPE_DATE), Is this the problem?

Perhaps it's related.  You could modify the VCL source and change the mapping to
adDate instead to see if that changes the result.

In my testing with your code posted here, I always see the correct results.
Perhaps you can post a complete code example that fails for you?

Mark

Re:Tdatetime field and Parameter( need help)


I experienced the same problem, my regional setting is English British
(format dd/mm/yy), when I query for 15/10/99, the result is correct, but
when I query for 01/10/99 then the result set is 10/1/99.
I found the workaround for this problem by changing the parameter type
from TDate to Integer, then set the parameter value in integer type, it
works for me.

Indra

In article <7ulfbm$ge...@forums.borland.com>,
  "Mark Edington (Borland)" <meding...@nolunchmeat.com> wrote:

Quote
> Joe Li <joe...@callista.net> wrote in message
> news:7u5kop$4hj14@forums.borland.com...

> > is 'mm/dd/yy', it works fine, if I change to 'dd/mm/yy', return
dataset is
> wrong..

> Wrong  how?  When I try this it works fine for me regardless of the
date format
> specified in the control panel.

adoDataSet1.Parameters.ParamByName('DateParam').DataType:=ftString;<----
seem

Quote
> > s less depends on the Date format setting,but still has problem.

> What  problem?

> > I look into the ADODB.pas , when ftDate parameter pass to ADO it
converts to
> > adDBdate rather than adDate, but the MS Access Provider DATETIME
field is
> > adDate(DBTYPE_DATE), Is this the problem?

> Perhaps it's related.  You could modify the VCL source and change the
mapping to
> adDate instead to see if that changes the result.

> In my testing with your code posted here, I always see the correct
results.
> Perhaps you can post a complete code example that fails for you?

> Mark

Sent via Deja.com http://www.deja.com/
Before you buy.

Other Threads