A SQL statement, a great god, please help-CodePudding

To access database, there are two fields: one is the 'acquisition time (e.g., the 2015-08-15 11:23:45), one is' time' (11), now want to in the data updated, inserted into the intercept 'access time of hours' time, but always tried many times is not successful, hope everybody to give directions, the code is as follows:
With ADOQuery1 do
The begin
The Close;
SQL. The Clear;
SQL. The Add (' UPDATE SJB SET time=DATEPART (hh, access time) ');
ExecSQL;
The end;
Always prompt hh no default value (should be less than value)
Thank you very much!!!!!!

CodePudding user response:

You under reference, compiled by

 procedure TForm1. FormCreate (Sender: TObject); 
Var 
CreateDB: Variant; 
SName, SQL, Source: string; 
The begin 
SName:=ExtractFilePath (ParamStr (0)) + 'test. The MDB'; 

Source:='. The Provider=Microsoft Jet. The OLEDB. 4.0. '
='+ +' Data Source sName; 

If not FileExists (sName) then 
The begin 
CreateDB:=CreateOleObject (' ADOX. Catalog); 
CreateDB. Create (Source); 
The create table SQL:='SJB' + 
'(plus or minus ao? COUNTER PRIMARY KEY, '+' 
'?? E? E +?? Datetime, '+' 
'e +???? Int) '; 

Con1. The ConnectionString:=Source; 
Con1. Connected:=true; 

Qry1. Connection:=con1; 

Qry1. Close; 
Qry1. SQL. The Clear; 
Qry1. SQL. The Add (SQL); 
Qry1. ExecSQL (); 
End the else 
The begin 
Con1. The ConnectionString:=Source; 
Con1. Connected:=true; 

Qry1. Connection:=con1; 
The end; 
The end; 

Procedure TForm1. Btn1Click (Sender: TObject); 
Var 
SQL: string; 
The begin 
SQL:='INSERT INTO SJB (?????? E? E +?? , e +?????? ) VALUES (Now (), 11) '; 
Qry1. Close; 
Qry1. SQL. The Clear; 
Qry1. SQL. The Add (SQL); 
Qry1. ExecSQL (); 
The end; 

Procedure TForm1. Btn2Click (Sender: TObject); 
Var 
SQL: string; 
The begin 
SQL:='(UPDATE SJB SET e +????=DATEPART (' h ', Now ())) '; 
Qry1. Close; 
Qry1. SQL. The Clear; 
Qry1. SQL. The Add (SQL); 
Qry1. ExecSQL (); 
The end;

CodePudding user response:

 procedure TForm1. FormCreate (Sender: TObject); 
Var 
CreateDB: Variant; 
SName, SQL, Source: string; 
The begin 
SName:=ExtractFilePath (ParamStr (0)) + 'test. The MDB'; 

Source:='. The Provider=Microsoft Jet. The OLEDB. 4.0. '
='+ +' Data Source sName; 

If not FileExists (sName) then 
The begin 
CreateDB:=CreateOleObject (' ADOX. Catalog); 
CreateDB. Create (Source); 
The create table SQL:='SJB' + 
'(PRIMARY KEY number COUNTER,' + '
'get time datetime,' + 
'time int); 

Con1. The ConnectionString:=Source; 
Con1. Connected:=true; 

Qry1. Connection:=con1; 

Qry1. Close; 
Qry1. SQL. The Clear; 
Qry1. SQL. The Add (SQL); 
Qry1. ExecSQL (); 
End the else 
The begin 
Con1. The ConnectionString:=Source; 
Con1. Connected:=true; 

Qry1. Connection:=con1; 
The end; 
The end; 

Procedure TForm1. Btn1Click (Sender: TObject); 
Var 
SQL: string; 
The begin 
SQL:="INSERT INTO SJB (access time, the time period) VALUES (Now (), 11) '; 
Qry1. Close; 
Qry1. SQL. The Clear; 
Qry1. SQL. The Add (SQL); 
Qry1. ExecSQL (); 
The end; 

Procedure TForm1. Btn2Click (Sender: TObject); 
Var 
SQL: string; 
The begin 
SQL:='(UPDATE SJB SET time=DATEPART (' h', Now ())) '; 
Qry1. Close; 
Qry1. SQL. The Clear; 
Qry1. SQL. The Add (SQL); 
Qry1. ExecSQL (); 
The end;

CodePudding user response:

First of all thank you for the brothers, give it a try, no,
ACCESS database and is essentially different, such as some fields need add [] in the ACCESS, such as: the number/code,

CodePudding user response:

1, regarding the date/time, to advance to set the default character

 
DateSeparator:='-'; 
ShortDateFormat:='- dd yyyy - MM; 
LongDateFormat:='yyyy' years', MM ' 'month', 'dd' ', ' '; 
TimeSeparator:=':'; 
TimeAMString:='AM'; 
TimePMString:='PM'; 
Hh: nn ShortTimeFormat:=' '; 
LongTimeFormat:='hh: nn: ss';

2, by opening files associated with DBGrid ADOQuery1, only through ADOQuery1 UpDate refresh data,

3,
For the connect to the database file
Button2 open table
Button3 updates a data
By cyclic update all the data for the

4, related code, has been run through, please refer to borrow,

5, unit code:

 
The unit Unit1; 

Interface 

USES the 
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, 
Dialogs, DB, ADODB, Grids, DBGrids, StdCtrls; 

Type 
TForm1=class (TForm) 
DBGrid1: TDBGrid; 
ADOConnection1: TADOConnection; 
ADOQuery1: TADOQuery; 
For: TButton; 
DataSource1: TDataSource; 
Button2: TButton; 
Button3: TButton; 
Edit1: TEdit; 
Button4: TButton; 
ADOQuery2: TADOQuery; 
Procedure Button1Click (Sender: TObject); 
Procedure Button2Click (Sender: TObject); 
Procedure Button3Click (Sender: TObject); 
Procedure FormCreate (Sender: TObject); 
Procedure Button4Click (Sender: TObject); 
Private 
{Private declarations} 
Public 
{Public declarations} 
The end; 

Var 
Form1: TForm1; 

Implementation 

{$R *. DFM} 

Procedure TForm1. FormCreate (Sender: TObject); 
The begin 
DateSeparator:='-'; 
ShortDateFormat:='- dd yyyy - MM; 
LongDateFormat:='yyyy' years', MM ' 'month', 'dd' ', ' '; 
TimeSeparator:=':'; 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull