CODEWITHSUNDEEP
delphi
user763539
user763539

Reputation: 3689

Why isn't the data getting inserted?

What am I doing wrong here since no data gets inserted into my ARCHIVE table though there are 3 records in my T_Main table in which ACCOMPLISHED =True?

Database is Accuracer.

procedure TForm1.AdvGlowButton1Click(Sender: TObject);
begin
case cxRadioGroup1.ItemIndex of
 0: begin
    if MessageDlg('Are you sure ?', mtConfirmation, [mbYes, mbNo], 0) = mrNo then
    begin Exit;
    end else
    Data_Module.T_MAIN.First;
    while not Data_Module.T_MAIN.Eof  do begin
    Data_Module.INS_ARCH.Close;
    Data_Module.INS_ARCH.SQL.Clear;
    Data_Module.INS_ARCH.SQL.Text :='INSERT INTO ARCHIVE (T_ID,FOR_DATE,DONE_WHEN)'
    + 'SELECT :a3,:a4,:a5 FROM T_MAIN WHERE ACCOMPLISHED =True ';
    Data_Module.INS_ARCH.Params.ParamByName('a3').AsInteger := Data_Module.T_MAIN.FieldByName('T_ID').AsInteger;
    Data_Module.INS_ARCH.Params.ParamByName('a4').AsDate := Data_Module.T_MAIN.FieldByName('FOR_DATE').AsDateTime;
    Data_Module.INS_ARCH.Params.ParamByName('a5').AsDate := Data_Module.T_MAIN.FieldByName('DONE_WHEN').AsDateTime;
    Data_Module.T_MAIN.Next;
    Data_Module.INS_ARCH.ExecSQL;
end;
end;
end;

Upvotes: 1

Views: 115

Answers (3)

moskito-x
moskito-x

Reputation: 11958

your query is wrong

Data_Module.INS_ARCH.SQL.Text :='INSERT ... SELECT :a3,:a4,:a5 FROM T_MAIN ....';

you fill the params with data not with real excisting Database fields.
example:

Data_Module.INS_ARCH.SQL.Text :='INSERT ... SELECT 1000, 2015-08-22, 2015-08-01 FROM T_MAIN ....';
  • Are you shure 1000 or 2015-08-22 or 2015-08-01 are really existing database fields?

EDIT from your answer :

This, of course works , because you setting valid field names in your select statement. 

SELECT T_ID,FOR_DATE,DONE_WHEN FROM T_MAIN

With your code your select is something like 

SELECT 1000, 2015-08-22, 2015-08-01 FROM T_MAIN

Upvotes: 1

Jerry Dodge
Jerry Dodge

Reputation: 27266

You're not supposed to use parameters for field names. It's not meant for that. Parameters are intended for actual data values. Note this other question / answer: How to use ADO Query Parameters to specify table and field names? Your real problem though as explained in moskito-x's answer is that you're passing data values as if they're field names.

On a side note, there are other things wrong with your piece of code. Here's my attempt to at least clean it up for you to better read it:

procedure TForm1.AdvGlowButton1Click(Sender: TObject);
begin
  case cxRadioGroup1.ItemIndex of
    0: begin
      if MessageDlg('Are you sure ?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
      begin
        Data_Module.T_MAIN.First;
        while not Data_Module.T_MAIN.Eof  do begin
          Data_Module.INS_ARCH.Close;
          Data_Module.INS_ARCH.SQL.Clear;
          Data_Module.INS_ARCH.SQL.Text :='INSERT INTO ARCHIVE (T_ID,FOR_DATE,DONE_WHEN)'
            + 'SELECT :a3,:a4,:a5 FROM T_MAIN WHERE ACCOMPLISHED =True ';
          Data_Module.INS_ARCH.Params.ParamByName('a3').AsInteger := Data_Module.T_MAIN.FieldByName('T_ID').AsInteger;
          Data_Module.INS_ARCH.Params.ParamByName('a4').AsDate := Data_Module.T_MAIN.FieldByName('FOR_DATE').AsDateTime;
          Data_Module.INS_ARCH.Params.ParamByName('a5').AsDate := Data_Module.T_MAIN.FieldByName('DONE_WHEN').AsDateTime;
          Data_Module.T_MAIN.Next;
          Data_Module.INS_ARCH.ExecSQL;
        end;
      end else begin
        Exit;
      end; //MessageDlg
    end; //case = 0
  end; //case
end; //procedure

And as yet another note, since these are inside a data module, shouldn't this procedure also be inside the data module? It worries me how every call is referencing Data_Module..

Upvotes: 1

user763539
user763539

Reputation: 3689

it seems this works :

procedure TForm1.AdvGlowButton1Click(Sender: TObject);
begin
case cxRadioGroup1.ItemIndex of
 0: begin
    if MessageDlg('Are you sure ?', mtConfirmation, [mbYes, mbNo], 0) = mrNo then
    begin Exit;
    end else begin
    Data_Module.INS_ARCH.Close;
    Data_Module.INS_ARCH.SQL.Clear;
    Data_Module.INS_ARCH.SQL.Text :='INSERT INTO ARCHIVE (T_ID,FOR_DATE,DONE_WHEN) SELECT T_ID,FOR_DATE,DONE_WHEN FROM T_MAIN WHERE ACCOMPLISHED =True ';
    Data_Module.INS_ARCH.ExecSQL;
end;
end;
end;

Upvotes: 0

Related Questions