Board index » delphi » Code Executes To Fast

Code Executes To Fast

I have the following code which should delete items from an Access database
via ADO but while the data is deleted the listbox is not updated unless I
rerun the delete with nothing to delete or I sleep for a while. Is there a
way for an adotable to reget the data that I know is updated.

procedure TDeleteArea.Button2Click(Sender: TObject);
var
x: Integer;
begin
adoMain.Active:=False;
for x:= 0 to cbToDelete.Items.Count - 1 do
    begin
    if cbToDelete.Checked[x] = True then
    begin
    GetRid(cbToDelete.Items.Strings[x]); ////routine to delete the data
    end;
    end;
/////sleep(4000);////makes it work but looks very ugly and may not work when
there are 20000 records.
cbToDelete.Clear;
adoMain.Active:=True;
adoMain.ReQuery;
adoMain.FindFirst;
cbToDelete.Clear;

while not adoMain.Eof do
begin
cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
adoMain.Next;
end;
end;

--
Brian Slack
Digital Wired Ltd.
http://www.digitalwired.net
br...@digitalwired.net
********************************************************
Agency Manager - can you manage without it ?
www.agencymanager.co.uk
********************************************************

 

Re:Code Executes To Fast


In <8jcgcg$e...@bornews.borland.com>, Brian Slack wrote:
Quote

> I have the following code which should delete items from an Access database
> via ADO but while the data is deleted the listbox is not updated unless I
> rerun the delete with nothing to delete or I sleep for a while. Is there a
> way for an adotable to reget the data that I know is updated.

<snip>

Brian
Any chance you're using mulitple Connection objects?  Access is well known for delaying applying updates for up to 5 seconds, unless the same connection is re-used to interrogate / requery, or whatever.  If you ensure all db accesses are via a single connection object you may eliminate your problem.

Roger Morton
roger.mor...@dial.pipex.com

Re:Code Executes To Fast


Sound like it as the table does the load and a command does the delete.

--
Brian Slack
Digital Wired Ltd.
http://www.digitalwired.net
br...@digitalwired.net
********************************************************
Agency Manager - can you manage without it ?
www.agencymanager.co.uk
********************************************************

Quote
"Roger Morton" <roger.mor...@nospamdial.pipex.com> wrote in message

news:RA15E0CEBA@LOFT...
Quote
> In <8jcgcg$e...@bornews.borland.com>, Brian Slack wrote:

> > I have the following code which should delete items from an Access
database
> > via ADO but while the data is deleted the listbox is not updated unless
I
> > rerun the delete with nothing to delete or I sleep for a while. Is there
a
> > way for an adotable to reget the data that I know is updated.

> <snip>

> Brian

> Any chance you're using mulitple Connection objects?  Access is well known

for delaying applying updates for up to 5 seconds, unless the same
connection is re-used to interrogate / requery, or whatever.  If you ensure
all db accesses are via a single connection object you may eliminate your
problem.
Quote

> Roger Morton
> roger.mor...@dial.pipex.com

Re:Code Executes To Fast


As ever it I tried 1 connection 1 table and 1 query with 1 connection
string. When I step through the code it works fine and it worked one time
out of ten but still doesn't update althought the delete is done. Thanks for
the help I need to think of another way round this !!!!

--
Brian Slack
Digital Wired Ltd.
http://www.digitalwired.net
br...@digitalwired.net
********************************************************
Agency Manager - can you manage without it ?
www.agencymanager.co.uk
********************************************************

Quote
"Brian Slack" <br...@digitalwired.net> wrote in message

news:8jcr1h$i8l1@bornews.borland.com...
Quote
> Sound like it as the table does the load and a command does the delete.

> --
> Brian Slack
> Digital Wired Ltd.
> http://www.digitalwired.net
> br...@digitalwired.net
> ********************************************************
> Agency Manager - can you manage without it ?
> www.agencymanager.co.uk
> ********************************************************
> "Roger Morton" <roger.mor...@nospamdial.pipex.com> wrote in message
> news:RA15E0CEBA@LOFT...
> > In <8jcgcg$e...@bornews.borland.com>, Brian Slack wrote:

> > > I have the following code which should delete items from an Access
> database
> > > via ADO but while the data is deleted the listbox is not updated
unless
> I
> > > rerun the delete with nothing to delete or I sleep for a while. Is
there
> a
> > > way for an adotable to reget the data that I know is updated.

> > <snip>

> > Brian

> > Any chance you're using mulitple Connection objects?  Access is well
known
> for delaying applying updates for up to 5 seconds, unless the same
> connection is re-used to interrogate / requery, or whatever.  If you
ensure
> all db accesses are via a single connection object you may eliminate your
> problem.

> > Roger Morton
> > roger.mor...@dial.pipex.com

Re:Code Executes To Fast


In <8jcv0a$i...@bornews.borland.com>, Brian Slack wrote:

Quote

> As ever it I tried 1 connection 1 table and 1 query with 1 connection
> string. When I step through the code it works fine and it worked one time
> out of ten but still doesn't update althought the delete is done. Thanks for
> the help I need to think of another way round this !!!!

Brian
Not quite sure of the implications of "1 query with one connection string".  Even if you've got the exact same connection *string* in, say, a table and a query, I think that means two separate connection objects get created by ADO.  I'm afraid I don't have ADOExpress, and hence work directly with the ADO COM objects; there you have the choice of supplying a recordset object either with a connection string (which provokes it into creating its own connection object from that string), or you can provide it with a connection *object* that you've already created yourself. The latter technique solves the Access synching problem.  

I assume the equivalent using ADOExpress is to place a TADOConnection component on a form / datamodule or whatever, and point all your table / query components at that single TADOConnection.

Roger Morton
roger.mor...@dial.pipex.com

Re:Code Executes To Fast


I have gone back to {*word*192} ADO as that seems to work as putting a Connection
component on didn't solve the problem

--
Brian Slack
Digital Wired Ltd.
http://www.digitalwired.net
br...@digitalwired.net
********************************************************
Agency Manager - can you manage without it ?
www.agencymanager.co.uk
********************************************************

Quote
"Roger Morton" <roger.mor...@nospamdial.pipex.com> wrote in message

news:RA15E0D7A9@LOFT...
Quote
> In <8jcv0a$i...@bornews.borland.com>, Brian Slack wrote:

> > As ever it I tried 1 connection 1 table and 1 query with 1 connection
> > string. When I step through the code it works fine and it worked one
time
> > out of ten but still doesn't update althought the delete is done. Thanks
for
> > the help I need to think of another way round this !!!!

> Brian

> Not quite sure of the implications of "1 query with one connection

string".  Even if you've got the exact same connection *string* in, say, a
table and a query, I think that means two separate connection objects get
created by ADO.  I'm afraid I don't have ADOExpress, and hence work directly
with the ADO COM objects; there you have the choice of supplying a recordset
object either with a connection string (which provokes it into creating its
own connection object from that string), or you can provide it with a
connection *object* that you've already created yourself. The latter
technique solves the Access synching problem.
Quote

> I assume the equivalent using ADOExpress is to place a TADOConnection

component on a form / datamodule or whatever, and point all your table /
query components at that single TADOConnection.
Quote

> Roger Morton
> roger.mor...@dial.pipex.com

Re:Code Executes To Fast


Quote
> procedure TDeleteArea.Button2Click(Sender: TObject);
> var
> x: Integer;
> begin
> adoMain.Active:=False;
> for x:= 0 to cbToDelete.Items.Count - 1 do
>     begin
>     if cbToDelete.Checked[x] = True then
>     begin
>     GetRid(cbToDelete.Items.Strings[x]); ////routine to delete the data

Can you give us the implementation of GetRid?

Quote
>     end;
>     end;
> /////sleep(4000);////makes it work but looks very ugly and may not work when
> there are 20000 records.

Seems like a problem in GetRid. Does it execute sync. or async.?

Quote
> cbToDelete.Clear;
> adoMain.Active:=True;
> adoMain.ReQuery;

The above line is redundant.

Quote
> adoMain.FindFirst;
> cbToDelete.Clear;

The above line is redundant.

Quote
> while not adoMain.Eof do
> begin
> cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
> adoMain.Next;
> end;
> end;

Everything that Roger wrote still holds.

--
V. Nazarov
IT Manager, Sofia Cable

Re:Code Executes To Fast


Here is the full code

type
  TDeleteArea = class(TForm)
    Button1: TButton;
    Button2: TButton;
    cbToDelete: TCheckListBox;
    adoMain: TADOTable;
    edtAdd: TEdit;
    Button3: TButton;
    adoDelete: TADOCommand;
    adoConn: TADOConnection;
    procedure FormCreate(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    function GetRid(WhatToGetRid:string):boolean;
  public
    procedure CreateParams(Var params: TCreateParams);override;
  end;

var
  DeleteArea: TDeleteArea;

implementation

{$R *.DFM}

procedure TDeleteArea.CreateParams(Var params: TCreateParams);
  begin
    inherited CreateParams( params );
    params.ExStyle := params.ExStyle or WS_EX_TOOLWINDOW and not
WS_EX_APPWINDOW;
end;

procedure TDeleteArea.FormCreate(Sender: TObject);
begin
adoMain.FindFirst;
while not adoMain.Eof do
        begin
        cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
        adoMain.Next;
        end;
end;

procedure TDeleteArea.Button3Click(Sender: TObject);
var
ToAdd:string;
begin
ToAdd:= edtAdd.Text;
if edtAdd.Text > '' then
        begin
        adoMain.Append;
        adoMain.InsertRecord([nil,ToAdd]);
        cbToDelete.Clear;
        adoMain.FindFirst;
        while not adoMain.Eof do
                begin

bToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
                adoMain.Next;
                end;
        end;
end;

procedure TDeleteArea.Button2Click(Sender: TObject);
var
x: Integer;
begin
for x:= 0 to cbToDelete.Items.Count - 1 do
    begin
    if cbToDelete.Checked[x] = True then
    begin
    GetRid(cbToDelete.Items.Strings[x]);
    end;
    end;

cbToDelete.Clear;
adoMain.FindFirst;
while not adoMain.Eof do
begin
cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
adoMain.Next;
end;
end;

procedure TDeleteArea.Button1Click(Sender: TObject);
begin
close;
end;

function TDeleteArea.GetRid(WhatToGetRid:string):boolean;
begin
        try
        adoDelete.Prepared:=False;
        adoDelete.CommandText:= 'DELETE * FROM Areas WHERE Areas.Areas = '''
+ WhatToGetRid + '''';
        adoDelete.Prepared:=True;
        adoDelete.Execute;
        Result:=True
        except
        Result:=False;
        end;
end;

end.

--
Brian Slack
Digital Wired Ltd.
http://www.digitalwired.net
br...@digitalwired.net
********************************************************
Agency Manager - can you manage without it ?
www.agencymanager.co.uk
********************************************************

Quote
"Vassil Nazarov" <s...@bulnet.bg> wrote in message news:395a8763@dnews...
> > procedure TDeleteArea.Button2Click(Sender: TObject);
> > var
> > x: Integer;
> > begin
> > adoMain.Active:=False;
> > for x:= 0 to cbToDelete.Items.Count - 1 do
> >     begin
> >     if cbToDelete.Checked[x] = True then
> >     begin
> >     GetRid(cbToDelete.Items.Strings[x]); ////routine to delete the data

> Can you give us the implementation of GetRid?

> >     end;
> >     end;
> > /////sleep(4000);////makes it work but looks very ugly and may not work
when
> > there are 20000 records.

> Seems like a problem in GetRid. Does it execute sync. or async.?

> > cbToDelete.Clear;
> > adoMain.Active:=True;
> > adoMain.ReQuery;

> The above line is redundant.

> > adoMain.FindFirst;
> > cbToDelete.Clear;

> The above line is redundant.

> > while not adoMain.Eof do
> > begin
> > cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
> > adoMain.Next;
> > end;
> > end;

> Everything that Roger wrote still holds.

> --
> V. Nazarov
> IT Manager, Sofia Cable

Re:Code Executes To Fast


Quote
"Brian Slack" <br...@digitalwired.net> wrote in message

news:8jevtk$70i2@bornews.borland.com...

Quote
> Here is the full code

> type
>   TDeleteArea = class(TForm)
>     Button1: TButton;
>     Button2: TButton;
>     cbToDelete: TCheckListBox;
>     adoMain: TADOTable;
>     edtAdd: TEdit;
>     Button3: TButton;
>     adoDelete: TADOCommand;
>     adoConn: TADOConnection;
>     procedure FormCreate(Sender: TObject);
>     procedure Button3Click(Sender: TObject);
>     procedure Button2Click(Sender: TObject);
>     procedure Button1Click(Sender: TObject);
>   private
>     function GetRid(WhatToGetRid:string):boolean;
>   public
>     procedure CreateParams(Var params: TCreateParams);override;
>   end;

> var
>   DeleteArea: TDeleteArea;

> implementation

> {$R *.DFM}

> procedure TDeleteArea.CreateParams(Var params: TCreateParams);
>   begin
>     inherited CreateParams( params );
>     params.ExStyle := params.ExStyle or WS_EX_TOOLWINDOW and not
> WS_EX_APPWINDOW;
> end;

> procedure TDeleteArea.FormCreate(Sender: TObject);
> begin
> adoMain.FindFirst;
> while not adoMain.Eof do
>         begin
>         cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
>         adoMain.Next;
>         end;
> end;

> procedure TDeleteArea.Button3Click(Sender: TObject);
> var
> ToAdd:string;
> begin
> ToAdd:= edtAdd.Text;
> if edtAdd.Text > '' then
>         begin
>         adoMain.Append;

The above line is redundant.

Quote
>         adoMain.InsertRecord([nil,ToAdd]);
>         cbToDelete.Clear;
>         adoMain.FindFirst;
>         while not adoMain.Eof do
>                 begin

> bToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
>                 adoMain.Next;
>                 end;
>         end;
> end;

For each new record you insert in adoMain you clear and populate
cbToDelete again and again. Brian, that's crazy!

Quote
> procedure TDeleteArea.Button2Click(Sender: TObject);
> var
> x: Integer;
> begin
> for x:= 0 to cbToDelete.Items.Count - 1 do
>     begin
>     if cbToDelete.Checked[x] = True then
>     begin
>     GetRid(cbToDelete.Items.Strings[x]);
>     end;
>     end;

> cbToDelete.Clear;

adoMain.Requery
{Add this line to see the results}

- Show quoted text -

Quote
> adoMain.FindFirst;
> while not adoMain.Eof do
> begin
> cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
> adoMain.Next;
> end;
> end;

> procedure TDeleteArea.Button1Click(Sender: TObject);
> begin
> close;
> end;

> function TDeleteArea.GetRid(WhatToGetRid:string):boolean;
> begin
>         try
>         adoDelete.Prepared:=False;
>         adoDelete.CommandText:= 'DELETE * FROM Areas WHERE Areas.Areas = '''
> + WhatToGetRid + '''';
>         adoDelete.Prepared:=True;
>         adoDelete.Execute;
>         Result:=True
>         except
>         Result:=False;
>         end;
> end;

1. In the procedure above it's better to leave adoDelete unprepared.
2. It's best to have:

 ...CommandText:= 'DELETE * FROM Areas WHERE Areas.Areas = :Areas';
    adoDelete.Prepared:=True;

once and then:

adoDelete.Parameters[0].Value := WhatToGetRid;
adoDelete.Execute;

many tymes.

3. Make shure that all of the following are true:
  - adoDelete.Connection is set to adoConn
  - adoMain.Connection is set to adoConn too.
  - adoDelete.ExecuteOptions=[eoExecuteNoRecords]
  - adoMain.ExecuteOptions=[ ]
4. Enjoy

HTH
--
V. Nazarov
IT Manager, Sofia Cable

Re:Code Executes To Fast


Perfect ....you genius....

--
Brian Slack
Digital Wired Ltd.
http://www.digitalwired.net
br...@digitalwired.net
********************************************************
Agency Manager - can you manage without it ?
www.agencymanager.co.uk
********************************************************

Quote
"Vassil Nazarov" <s...@bulnet.bg> wrote in message news:395b6aa9@dnews...
> "Brian Slack" <br...@digitalwired.net> wrote in message
> news:8jevtk$70i2@bornews.borland.com...
> > Here is the full code

> > type
> >   TDeleteArea = class(TForm)
> >     Button1: TButton;
> >     Button2: TButton;
> >     cbToDelete: TCheckListBox;
> >     adoMain: TADOTable;
> >     edtAdd: TEdit;
> >     Button3: TButton;
> >     adoDelete: TADOCommand;
> >     adoConn: TADOConnection;
> >     procedure FormCreate(Sender: TObject);
> >     procedure Button3Click(Sender: TObject);
> >     procedure Button2Click(Sender: TObject);
> >     procedure Button1Click(Sender: TObject);
> >   private
> >     function GetRid(WhatToGetRid:string):boolean;
> >   public
> >     procedure CreateParams(Var params: TCreateParams);override;
> >   end;

> > var
> >   DeleteArea: TDeleteArea;

> > implementation

> > {$R *.DFM}

> > procedure TDeleteArea.CreateParams(Var params: TCreateParams);
> >   begin
> >     inherited CreateParams( params );
> >     params.ExStyle := params.ExStyle or WS_EX_TOOLWINDOW and not
> > WS_EX_APPWINDOW;
> > end;

> > procedure TDeleteArea.FormCreate(Sender: TObject);
> > begin
> > adoMain.FindFirst;
> > while not adoMain.Eof do
> >         begin
> >         cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
> >         adoMain.Next;
> >         end;
> > end;

> > procedure TDeleteArea.Button3Click(Sender: TObject);
> > var
> > ToAdd:string;
> > begin
> > ToAdd:= edtAdd.Text;
> > if edtAdd.Text > '' then
> >         begin
> >         adoMain.Append;

> The above line is redundant.

> >         adoMain.InsertRecord([nil,ToAdd]);
> >         cbToDelete.Clear;
> >         adoMain.FindFirst;
> >         while not adoMain.Eof do
> >                 begin

> > bToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
> >                 adoMain.Next;
> >                 end;
> >         end;
> > end;

> For each new record you insert in adoMain you clear and populate
> cbToDelete again and again. Brian, that's crazy!

> > procedure TDeleteArea.Button2Click(Sender: TObject);
> > var
> > x: Integer;
> > begin
> > for x:= 0 to cbToDelete.Items.Count - 1 do
> >     begin
> >     if cbToDelete.Checked[x] = True then
> >     begin
> >     GetRid(cbToDelete.Items.Strings[x]);
> >     end;
> >     end;

> > cbToDelete.Clear;

> adoMain.Requery
> {Add this line to see the results}

> > adoMain.FindFirst;
> > while not adoMain.Eof do
> > begin
> > cbToDelete.Items.Add( adoMain.FieldByName('Areas').AsString);
> > adoMain.Next;
> > end;
> > end;

> > procedure TDeleteArea.Button1Click(Sender: TObject);
> > begin
> > close;
> > end;

> > function TDeleteArea.GetRid(WhatToGetRid:string):boolean;
> > begin
> >         try
> >         adoDelete.Prepared:=False;
> >         adoDelete.CommandText:= 'DELETE * FROM Areas WHERE Areas.Areas =
'''
> > + WhatToGetRid + '''';
> >         adoDelete.Prepared:=True;
> >         adoDelete.Execute;
> >         Result:=True
> >         except
> >         Result:=False;
> >         end;
> > end;

> 1. In the procedure above it's better to leave adoDelete unprepared.
> 2. It's best to have:

>  ...CommandText:= 'DELETE * FROM Areas WHERE Areas.Areas = :Areas';
>     adoDelete.Prepared:=True;

> once and then:

> adoDelete.Parameters[0].Value := WhatToGetRid;
> adoDelete.Execute;

> many tymes.

> 3. Make shure that all of the following are true:
>   - adoDelete.Connection is set to adoConn
>   - adoMain.Connection is set to adoConn too.
>   - adoDelete.ExecuteOptions=[eoExecuteNoRecords]
>   - adoMain.ExecuteOptions=[ ]
> 4. Enjoy

> HTH
> --
> V. Nazarov
> IT Manager, Sofia Cable

Re:Code Executes To Fast


Quote
> ....you genius....

Not a genius. Just familiar with basic Delphi features ;-)

--
V. Nazarov
IT Manager, Sofia Cable

Other Threads