senad
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 " :-) ,
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 " :-) ,
bde or ado?
ASKER
ado
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,AdoQ uery2) then
showmessage('There was an Error');
end;
procedure TForm1.Button2Click(Sender : TObject);
begin
if not Move_Record(AdoQuery2,AdoQ uery1) then
showmessage('There was an Error');
end;
end.
meikl ;-)
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
begin
if not Move_Record(AdoQuery1,AdoQ
showmessage('There was an Error');
end;
procedure TForm1.Button2Click(Sender
begin
if not Move_Record(AdoQuery2,AdoQ
showmessage('There was an Error');
end;
end.
meikl ;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ah, with sql-statements, never guessed that, geo :-))
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)
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 ?
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 ;-)
or do you need it in form of sql-statements?
-> see geo's comment
meikl ;-)
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 ...
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 ...
ASKER
ASource, ADest (I assume this is Table1- Table2)??
i will try this...
i will try this...
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('Fie ldName').A sString;
Tell me Mieikl whatya thinnk and help me write this properly.
This is easier...(i think...)Youre the boss...
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('Fie
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(S ender: 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(Se nder: 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
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
begin
ADOConnection1.Open;
ADOTable1.Open;
ADOTable2.Open;
end;
procedure TForm1.btnMoveRightClick(S
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(Se
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 ;-)
geo is the boss :-))
-> nothing to append to geo's last comment
meikl ;-)
Wow, thanks meikl :-)))
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..
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..
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)
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-ADOTable 1-ADOConne ction1 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
'ADOTable1.Fields[0].Value
Regards, Geo
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...
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...
ASKER
I will try seting all tables to active without the button and see how it goes...
ASKER
Project Project1.exe raised exception class EListError with message 'Listindex out of bounds (0)'.Process stopped.Use Step or Run to continue.
??????????????
??????????????
ASKER
Ahhh...got it....forgot to link the f... queery to adocon1...
ASKER
Made it to PREMIUM !
************************** *
**************************
glad you got it work :-)))
Me too. Good luck !