Copy dataset into Access
lazarus / 16-Databases

----------------------------------------------------------------------
Hansvb · 2016-02-27 21:26

With ZQuery i execute a query. I want to insert the result dataset
complete in an Access database. I think i have to generate some 
insert statement. What is the best way to insert the result dataset
complete in an Access databse?

----------------------------------------------------------------------
mangakissa · 2016-02-27 21:31

Hansvb, it depents how many tables has to be inserted in the Access
database. Is it one to one, then its simple:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var teller
: integer;begin  for teller := 1 to selectquery.fieldcount - 1 do 
begin     insertquery.params[teller].value :=
selectquery.params[teller].value;  end;end;

----------------------------------------------------------------------
Hansvb · 2016-02-27 21:45

Can i view the insertquery.params?
i never used it so i am not sure if in can execute the insert query

----------------------------------------------------------------------
Hansvb · 2016-02-28 10:40

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var teller
: integer;begin  for teller := 1 to selectquery.fieldcount - 1 do 
begin     insertquery.params[teller].value :=
selectquery.params[teller].value;  end;end;
This gives a list index out of bounds. My select Query has 18 fields.
(selectquery.Fieldcount)

----------------------------------------------------------------------
mangakissa · 2016-02-28 10:42

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i // Assumes
the the selecttable has 5 fields procedure selectinto(aDataset :
TDataset);var InsertQuery : TSQLQuery;    TransAction :
TSQLTransAction;    Index       : integer;begin  try    InsertQuery :=
TSQLQuery.create(nil)    TransAction :=
TSQLTransAction.create(InsertQuery);    TransAction.Database :=
MyConnection1;    InsertQuery.Database := MyConnection1;   
InsertQuery.Transaction := TransAction;    InsertQuery.SQL.Text :=
'insert into myaccestable (field1, field2,field3,field4) values
(:field1, :field2,:field3,:field4)';    aDataset.first;    while not
aDataset.eof do    begin      //forget the primary key      for Index
:= 1 to aDataset.fieldcount - 1 do         InsertQuery.params[Index -
1].Value := aDataset.fields[Index].Value;      InsertQuery.ExecSQL;  
  Transaction.CommitRetaining;      aDataset.Next;    end;  finally  
InsertQuery.free;  end;end;  If some fields has to be inserted you can
use parambyname() instead.

This procedure copies whole records from table to table.

----------------------------------------------------------------------
mangakissa · 2016-02-28 10:48

If you have selected fields in insert.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
selectinto(aDataset : TDataset);var InsertQuery : TSQLQuery;   
TransAction : TSQLTransAction;    Index       : integer;begin  try   
InsertQuery := TSQLQuery.create(nil)    TransAction :=
TSQLTransAction.create(InsertQuery);    TransAction.Database :=
MyConnection1;    InsertQuery.Database := MyConnection1;   
InsertQuery.Transaction := TransAction;    InsertQuery.SQL.Text :=
'insert into myaccestable (field1, field2,field3,field4) values
(:field1, :field2,:field3,:field4)';    aDataset.first;    while not
aDataset.eof do    begin      //forget the primary key     
InsertQuery.paramByName('field1).Value :=
aDataset.fieldbyname('getfirst').Value;    
 InsertQuery.paramByName('field2).Value :=
aDataset.fieldbyname('getname').Value;     
InsertQuery.paramByName('field3).Value :=
aDataset.fieldbyname('getmessage').Value;    
 InsertQuery.paramByName('field4).Value :=
aDataset.fieldbyname('birthof').Value;      InsertQuery.ExecSQL;     
Transaction.CommitRetaining;      aDataset.Next;    end;  finally   
InsertQuery.free;  end;end;