The Delphi query Access date / time field error self summary

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Describe the phenomenon

Delphi recent study, when learning Access database connection found a problem: by using the
ADOConnection+DataSource+ADOQuery connect to the database
Connect. Execute the query without time types there is no problem, if you include the time inside a problem. The code as follows:
SqlStatement := Format('SELECT distinct Time FROM [Memo] WHERE datediff(''d'',Time,#%s#)=0 And Time >#10/29/2013 9:30#',[SysData]);//The Delphi query Access date field is this. 
  SqlAdd := Format(' And Time >#%s#',[SysTime]);
  try
    MADOQuery.Close;
    MADOQuery.SQL.Clear;
    MADOQuery.SQL.Add(SqlStatement);
    MADOQuery.SQL.Add(SqlAdd);
    MADOQuery.Open;

    if MADOQuery.RecordCount >0 then
      begin.....{Perform some operation}end;


That is: Execution
Select Distinct Time From [Memo] Where Time > #2013/10/29 9:30:0#This statement is a problem, But the implementation of Select Distinct Time From [Memo] Where Time > #2013/10/29# there is no problem;, With the attitude of learning and troubleshooting, First of all I doubt is the SQL statement has a problem, So will this statement in the Access implementation of the side, No problem can be achieved, Then doubt yourself in the fields of the Access set out the problem, Then he ran out of the fields of Access Time, Set it to the conventional time, Long time etc., Test for a long time finally found no Access what, It is innocent, Then is to find data, Then I saw the book says ":" is the key to symbols in Delphi system, I guess is not because of": ", So the Internet search this information, Just find that really have this thing: Delphi ":" the meaning of the parameters, That is to say the system take 9:30 as 30 is the 9 parameter and 9 parameter to check in 30 to, We process all know, This certainly does not look up., So you know, System error, ""

Below to find out why the solution:

The system self-test function leads to travel, so I just need to disable self-test, execute the SQL statement is TADOQuery, we only need to MADOQuery.ParamCheck:=False.,

SqlStatement := Format('SELECT distinct Time FROM [Memo] WHERE datediff(''d'',Time,#%s#)=0 And Time >#10/29/2013 9:30#',[SysData]);
  SqlAdd := Format(' And Time >#%s#',[SysTime]);
  try
    MADOQuery.Close;
    MADOQuery.SQL.Clear;
    MADOQuery.ParamCheck := False;//Changes here
MADOQuery.SQL.Add(SqlStatement); MADOQuery.SQL.Add(SqlAdd); MADOQuery.Open; if MADOQuery.RecordCount >0 then begin if you are afraid of themselves are not familiar with Delphi, banned this property can have a problem, you can after you execute the SQL statement you this is set back the property.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Nat at December 05, 2013 - 10:31 PM