?
Solved

delete from multiple tables with one button

Posted on 2005-03-28
5
Medium Priority
?
207 Views
Last Modified: 2010-04-05
I have 10 tables (ADOTable)
I display them in grid by means of radiobuttons i.e like this :

procedure TForm11.BitBtn1Click(Sender: TObject);
 Var I:integer;
begin
Case Radiogroup1.ItemIndex of
0: begin
for i:= 0 to Form11.ComponentCount - 1 do
if (Form11.Components[i] is TADOTable) then
(Form11.Components[i] as TAdoTable).Close;
ADOTable1.Open;
Datasource1.DataSet:=Adotable1 ;
end;

and so on for the remaining 9 tables.

Now,I added a query (ADOQuery1) so I can do some cleanup
with it.The basic cleanup parameters that I use to clean up tables
are the Autoinc field (ID) and the date field.
Example :

Adoquery1.Close;
Adoquery1.SQL.Clear;
AdoQuery1.SQL.Text:='delete from USERSLOG where USERSLOG.DATE between :d1 and :d2';
adoquery1.Parameters.ParamByName('d1').Value:= DateToStr(DateTimePicker1.Date);
adoquery1.Parameters.ParamByName('d2').Value:= DateToStr(DateTimePicker2.Date);
Adoquery1.ExecSQL;
AdoTable3.Requery();
or
Adoquery1.Close;
Adoquery1.SQL.Clear;
AdoQuery1.SQL.Text:='delete from log where log.id between :d1 and :d2';
adoquery1.Parameters.ParamByName('d1').Value:= Edit1.Text;
adoquery1.Parameters.ParamByName('d2').Value:= Edit2.Text;
Adoquery1.ExecSQL;
AdoTable1.Requery();

The problem is I would like to do cleanup with just one button.
So I guess I must tell the button on which table exactly to run the cleanup queery.
How do I do that???
I guess it could be done with stored procedures but I never used them before .
Baahh...Tell me your opinion and a solution.

0
Comment
Question by:senad
  • 2
  • 2
5 Comments
 
LVL 22

Author Comment

by:senad
ID: 13650232
Or do I declare a global variable:
Var CurrentQuery:TADOQuery

then drop multiple queeries (for every radiobutton) and on button click

if TRadioButton(radioGroup1.Components[1]).Checked = True
 then
Currentquery:=ADOQuery1
    begin

  something like this.... ???
0
 
LVL 11

Assisted Solution

by:pcsentinel
pcsentinel earned 400 total points
ID: 13650284
A simple way would be to assigne the adotable to a form level variable when you click the radiobutton and then use that when doing the clean up

i.e.

private
FTable: TADOTable


and then in the radiobutton code do

procedure TForm11.BitBtn1Click(Sender: TObject);
 Var I:integer;
begin
Case Radiogroup1.ItemIndex of
0: begin
for i:= 0 to Form11.ComponentCount - 1 do
if (Form11.Components[i] is TADOTable) then
(Form11.Components[i] as TAdoTable).Close;
ADOTable1.Open;
Datasource1.DataSet:=Adotable1 ;
FTable=ADOTable1;
end;

and in your cleanup use

FTable.Requery();

Hope this helps
0
 
LVL 17

Accepted Solution

by:
geobul earned 1600 total points
ID: 13650434
Hi,

You may build your SQL dynamically like the following:

procedure TForm11.BitBtn2Click(Sender: TObject); // just one button for cleanup
var
  t: TADOTable;
begin
  // common part
  Adoquery1.Close;
  Adoquery1.SQL.Clear;

  // for all db tables
  Case Radiogroup1.ItemIndex of
    0: begin // for Table1
      AdoQuery1.SQL.Text := 'delete from log where log.id between :d1 and :d2';
      adoquery1.Parameters.ParamByName('d1').Value:= Edit1.Text;
      adoquery1.Parameters.ParamByName('d2').Value:= Edit2.Text;
      t := ADOTable1;
    end;
    1: //
    2: begin // for Table3
      AdoQuery1.SQL.Text:='delete from USERSLOG where USERSLOG.DATE between :d1 and :d2';
      adoquery1.Parameters.ParamByName('d1').Value:= DateToStr(DateTimePicker1.Date);
      adoquery1.Parameters.ParamByName('d2').Value:= DateToStr(DateTimePicker2.Date);
      t := ADOTable3;
    end;
    // etc. for all tables
  end; //case
 
  // common part
  Adoquery1.ExecSQL;
  t.Requery();

end;

Regards, Geo
0
 
LVL 22

Author Comment

by:senad
ID: 13650513
I had it working another way but this way is simpler ...Thank's Geo...
0
 
LVL 17

Expert Comment

by:geobul
ID: 13651158
My pleasure :-)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question