Link to home
Start Free TrialLog in
Avatar of senad
senadFlag for Slovenia

asked on

move record from one table to another

this is just for learning sake.
i have 2 tables
in first numbers from 1 to 10
other (same structure) is empty.
So i got 2 grids displaying status of the tables.
how can i move selected record from 1 table to another and back
(on button click,hopefully in ascd order and the data is visible in grids?
Operation code name "Tossing numbers arround " :-) ,
Avatar of kretzschmar
kretzschmar
Flag of Germany image

bde or ado?
Avatar of senad

ASKER

ado
SOLUTION
Avatar of kretzschmar
kretzschmar
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of senad

ASKER

wanted to do it with a queery...
there is just no difference, well a very little bit

unit ado_record_move_query_u;

interface

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

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    Splitter1: TSplitter;
    DBGrid2: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    ADOQuery1: TADOQuery;  //select * from numtab1 order by anumber
    ADOQuery2: TADOQuery;  //select * from numtab2 order by anumber
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function move_record(ASource, ADest : TCustomADODataset) : boolean;
var i : integer;
begin
  result := false;
  try
    ADest.Insert;
    for i := 0 to ASource.FieldCount - 1 do
      ADest.Fields[i].Value := ASource.Fields[i].Value;
    ADest.Post;
    try
      ASource.Delete;  //try the delete
      result := true;
    except
      ADest.Delete;  //except delete it at dest
    end;
    ASource.Requery([]);
    ADest.Requery([]);
  except
    //nothing
  end;
end;


//use on Buttonclick
procedure TForm1.Button1Click(Sender: TObject);
begin
  if not Move_Record(AdoQuery1,AdoQuery2) then
    showmessage('There was an Error');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  if not Move_Record(AdoQuery2,AdoQuery1) then
    showmessage('There was an Error');
end;

end.

meikl ;-)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ah, with sql-statements, never guessed that, geo :-))
Avatar of geobul
geobul

meikl, I started writing my comment before seeing your second one :-)  I still don't know what senad wants...
Actually your third comment (second example)
Avatar of senad

ASKER

what kind of SQL is that ?
i'll give you 200 pts if you compile/implement that...
What I want..simple.
Take one record from one table and put it into another.
I select the record in grid and "beam" it into second table (also deleting it).
Now I have that record in my second table (is visible in another grid).
And again...I select record and put it back into first table...
Little silly but i wonder how it goes:

Tables are simple:
Table1                                                   Table2      
Number                                                 Number

In 1st one for demo i put numbers 1-10.

So now i have in grid 1displayed                                    In grid 2

Number (column header)                                                 Number (column header)
1                                                                                      (empty for now)                                                                    
2
3
4
and so on till 10.

Now if i select "4" in 1st grid and run queery  i "put" it in the right grid.
And so on and vice versa...
Clear enough ?
? already done, see my samples above,

or do you need it in form of sql-statements?
-> see geo's comment

meikl ;-)
Avatar of senad

ASKER

sql would be nice....
geos sql is a little weird couldnt compile...
inserting 1st field...???how do i know what user selected??
SQL.Text := 'INSERT INTO Table2 SELECT Table1.* FROM Table1 WHERE table1.number = :1or 2 or 3 ';// ???
 Parameters[0].Value := ADOTable1.Fields[0].Value;
 ExecSQL;
sorry new at this ...
Avatar of senad

ASKER

ASource, ADest  (I assume this is Table1- Table2)??

i will try this...
Avatar of senad

ASKER

Havent tried this yet but I am wondering something else:
I think it can be done more simpler using SQL.
Since i have an edit displaying (on scroll event of the table )
its value it could run something like :
AdoQuery1.delete from table 1 value  edit1.text := Adotable1.fieldbyname('FieldName').AsString;
Tell me Mieikl whatya thinnk and help me write this properly.
This is easier...(i think...)Youre the boss...

Hi,

There are two tables Table1 and Table2 with one field named 'Number' in a database.
On the form there are one:
- ADOConnection1
- two tables ADOTable1 and ADOTable2 pointing to Table1 and Table2 and linked to ADOConnection1
- two datasource components pointing at ADOTable1 and ADOTable2
- two DBGrids linked to DataSource1 and DataSource2
- one ADOQuery1 component linked to the ADOConnection1
- three buttons - one for opening the connection and the tables, one for moving from table1 to table2 (btnMoveRight) and one for moving a record from Table2 to Table1 (btnMoveLeft).

Usage:
- click btnOpen to open the tables
- select a row in DBGrid1 (Table1) and press btnMoveRight. The selected record goes to table2 in DBGrid2.
- repeat the same for DBGrid2 and btnMoveLeft.

-----------
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Grids, DBGrids, Db, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOTable1: TADOTable;
    ADOTable2: TADOTable;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DataSource2: TDataSource;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    btnOpen: TButton;
    btnMoveRight: TButton;
    btnMoveLeft: TButton;
    procedure btnOpenClick(Sender: TObject);
    procedure btnMoveRightClick(Sender: TObject);
    procedure btnMoveLeftClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.btnOpenClick(Sender: TObject);
begin
  ADOConnection1.Open;
  ADOTable1.Open;
  ADOTable2.Open;
end;

procedure TForm1.btnMoveRightClick(Sender: TObject);
begin
  with ADOQuery1 do begin
   // insert from 'Table1' (ADOTable1 component) into 'Table2'
   Close;
   SQL.Text := 'INSERT INTO Table2 SELECT Table1.* FROM Table1 WHERE Table1.Number = :par';
   Parameters[0].Value := ADOTable1.Fields[0].Value;
   ExecSQL;

   // delete from 'Table1'
   Close;
   SQL.Text := 'DELETE FROM Table1 WHERE Table1.Number = :par';
   Parameters[0].Value := ADOTable1.Fields[0].Value;
   ExecSQL;
  end;
  ADOTable1.Requery;
  ADOTable2.Requery;
end;

procedure TForm1.btnMoveLeftClick(Sender: TObject);
begin
  with ADOQuery1 do begin
   // insert from 'Table2' (ADOTable2 component) into 'Table1'
   Close;
   SQL.Text := 'INSERT INTO Table1 SELECT Table2.* FROM Table2 WHERE Table2.Number = :par';
   Parameters[0].Value := ADOTable2.Fields[0].Value;
   ExecSQL;

   // delete from 'FirstTable'
   Close;
   SQL.Text := 'DELETE FROM Table2 WHERE Table2.Number = :par';
   Parameters[0].Value := ADOTable2.Fields[0].Value;
   ExecSQL;
  end;
  ADOTable1.Requery;
  ADOTable2.Requery;
end;

end.
----------
Regards, Geo
>Youre the boss...

geo is the boss :-))
-> nothing to append to geo's last comment

meikl ;-)
Wow, thanks meikl :-)))
Avatar of senad

ASKER

Dont know... i will try this as i prefer SQL (just delphi synath gives me hard time).
At first glance i think i will get comp. error on parameters issue as i usually allways do in ado...
Must print this...
OK se ya later..
Avatar of senad

ASKER

I tried the first part first ...:

  SQL.Text := 'INSERT INTO Table2 SELECT Table1.* FROM Table1 WHERE Table1.Number = :par';
  Parameters[0].Value := ADOTable1.Fields[0].Value;
  ExecSQL;
 and I get      "ListIndex out of bounds (0)"
structure of table1 is
number (Long integer)
Your ADOTable1 is not opened, I guess. That's why it has no Fields[0] property set.
BTW you can't copy one small part of the code above (isolated from the rest of it) and to expect it to run.

'ADOTable1.Fields[0].Value' in the code above presumes that you have a DBGrid-DataSource-ADOTable1-ADOConnection1 chain on your form, ADOConnection1 is opened, ADOTable1 is opened, you can see the rows in the grid and you have already selected one of them before executing that part of code.

Regards, Geo
Avatar of senad

ASKER

I just run the INSERT stuff and it does not work.No need to run the DELETE if I cant run INSERT,or no...???
As Yoda said ,Insert it is...
I can read you know...
Also,everything is connected right.
Perhaps ado connection is set to autoconnect true and hence the problem.
Didnt look really...
I will check this tomorow....
May the force be with you...

Avatar of senad

ASKER

I will try seting all tables to active without the button and see how it goes...
Avatar of senad

ASKER

Project Project1.exe raised exception class EListError with message 'Listindex out of bounds (0)'.Process stopped.Use Step or Run to continue.

??????????????
Avatar of senad

ASKER

Ahhh...got it....forgot to link the f... queery to adocon1...
Avatar of senad

ASKER

Made it to PREMIUM !
***************************
glad you got it work :-)))
Me too. Good luck !