[SOLVED] TTL Record Count, i.e. a DBEdit that informs user Total Clients, etc.

[1HuntnMan]

The application I'm working on I have a DBEdit on the Clients, Contacts, Proposals, Sales Orders forms that just displays the total clients, proposals, etc. for the user. I don't understand why it displays properly the first time into a form, i.e. Clients but if you exit back to the main form and go back into Clients Mgt., the procedure is executing, check by step-by-step. So, it must be something about the component, i.e. the DBEdit on the form. The second time into any of these forms, the DBEdit is blank.  Here's the procedure:

Code: Pascal  [Select][+][-]

1. procedure TFrmClientsMgt.UpdateTTLClients;
2. begin
3.   if FClosing or (csDestroying in ComponentState) then Exit;
4.   if (EditTTLClnts = nil) or (QryCountClnts = nil) then Exit;
5.   SafeClose(QryCountClnts);
6.   QryCountClnts.SQL.Text:= 'SELECT COUNT(*) AS TTL FROM CLIENTS';
7.   try
8.     QryCountClnts.Open;
9.     EditTTLClnts.ReadOnly:= False;
10.     EditTTLClnts.Text:= QryCountClnts.FieldByName('TTL').AsString;
11.     EditTTLClnts.Repaint;
12.   finally
13.     EditTTLClnts.ReadOnly:= True;
14.     QryCountClnts.Close;
15.   end;
16. end;
17.  
18.  

[1HuntnMan]

I'm executing this procedure in: AfterDelete and AfterPost.

[Zvoni]

I'm executing this procedure in: AfterDelete and AfterPost.

And have these deletes/Posts been "Committed"?

EDIT: I'm smelling unneccessary code here.
There are some tricks in SQL you can do, to cut down unneccessary code.
If you're interested.....

[1HuntnMan]

I put this off for awhile while adding html Help for the application.  But, tried a couple of changes.  Changed the TTL Appointments DBEdit on the Appts form from a DBEdit to a TEdit because it's not stored in any of the SQLite3 tables.  Then, tried executing the UPDateTTLAppts procedure instead of in AfterPost and AfterDelete to just when I execute the Save procedure and Delete procedure. For example the BitBtnSaveClick procedure, executing just after the Appointment was saved instead of AfterPost.  Same thing moving UpDateTTLAppts to just after the record is deleted in the BitBtnDelete procedure. Still disappears after you go back into any form where I'm attempting to show the Total Appointments, Total Clients, etc.  When you first go into any of these forms I'm executing the UpDateTTLClients, Appts., etc. in the Form Show.  Then it's only executed when you save/add a new record or delete a record. As long as I'm in the unit i.e. Appointments Mgt. I can add new Appts., delete a couple and the TEdit Total Appts. updates correctly. But, if I exit the form and then go back into the Appts. form, the TEdit is DEAD! It never updates even thought stepping thru, executing is as it's supposed to execute.  Really quite a mystery to me???

Code: Pascal  [Select][+][-]

1. procedure TFrmApptsMgt.BitBtnSaveClick(Sender: TObject);
2. begin
3.   if QryAppts.FieldByName('CNTKLNAME').AsString = '' then
4.     begin
5.       ShowMessage('WARNING! No Appointment-Contact selected!');
6.       Exit;
7.     end;
8.   try
9.     if (QryAppts.State in [dsEdit, dsInsert]) or (QryAppts.ChangeCount > 0) then
10.       begin
11.         SafeClose(QryCntks);
12.         SafeClose(QryClients);
13.         SafeClose(DSrcStates.DataSet);
14.         SafeClose(DSrcCntries.DataSet);
15.         SaveQueryChanges(QryAppts, PMSDataModule.TransPMSDB);
16.         EnsureQueryReady(QryAppts, True);
17.         UpdateTTLAppts;
18.         EnsureQueryReady(QryCntks, False);
19.         EnsureQueryReady(QryClients, False);
20.         ReOpenQuery(PMSDataModule.QryStates);
21.         ReOpenQuery(PMSDataModule.QryCntries);
22.       end;
23.   except on E: Exception do
24.     begin
25.       PMSDataModule.TransPMSDB.Rollback;
26.       ShowMessage('Error saving current Appointment record: '+E.Message);
27.     end;
28.   end;
29. end;
30.  

[Zvoni]

Just took a look again at your original code with the DBEdit
see comment in code

Code: Pascal  [Select][+][-]

1. procedure TFrmClientsMgt.UpdateTTLClients;
2. begin
3.   if FClosing or (csDestroying in ComponentState) then Exit;
4.   if (EditTTLClnts = nil) or (QryCountClnts = nil) then Exit;
5.   SafeClose(QryCountClnts);
6.   QryCountClnts.SQL.Text:= 'SELECT COUNT(*) AS TTL FROM CLIENTS';
7.   try
8.     QryCountClnts.Open;
9.     EditTTLClnts.ReadOnly:= False;
10.     EditTTLClnts.Text:= QryCountClnts.FieldByName('TTL').AsString;
11.     EditTTLClnts.Repaint;
12.   finally
13.     EditTTLClnts.ReadOnly:= True;
14.     QryCountClnts.Close;  //THIS IS YOU PROBLEM. COMMENT THIS OUT!
15.   end;
16. end;
17.  
18.  

I had a similar Problem with a DBGrid, until i figured out, that when closing the Dataset your DB-Control depends on, it blanks out the control!
Which in Hindsight makes perfect sense, since a DB-Control is a "live"-connection to your Dataset

[LeP]

I had a similar Problem with a DBGrid, until i figured out, that when closing the Dataset your DB-Control depends on, it blanks out the control!
Which in Hindsight makes perfect sense, since a DB-Control is a "live"-connection to your Dataset

I use the "DisableCOntrols" of Dataset before every db operations and even when you close the dataset the datas on DBGrid are still visible.

[Zvoni]

I had a similar Problem with a DBGrid, until i figured out, that when closing the Dataset your DB-Control depends on, it blanks out the control!
Which in Hindsight makes perfect sense, since a DB-Control is a "live"-connection to your Dataset

I use the "DisableCOntrols" of Dataset before every db operations and even when you close the dataset the datas on DBGrid are still visible.

??????
https://www.freepascal.org/docs-html/fcl/db/tdataset.disablecontrols.html

DisableControls tells the dataset to stop sending data-related events to the controls.

The moment you call EnableControls, the connection is "live" again...... and then close your Dataset... empty control.

Suit yourself......

[LeP]

DisableControls tells the dataset to stop sending data-related events to the controls.

The moment you call EnableControls, the connection is "live" again...... and then close your Dataset... empty control.
Suit yourself......

Yes, of course. My point is about don't show empty grid or grid that is uograding when you change something.
It's normal and should be as is that when there is non connection the grid is empty.

But I do it when change something on dataset (load biggest data, close / open) so the display is stable and the operations on dataset are much faster.

[Zvoni]

DisableControls tells the dataset to stop sending data-related events to the controls.

The moment you call EnableControls, the connection is "live" again...... and then close your Dataset... empty control.
Suit yourself......

Yes, of course. My point is about don't show empty grid or grid that is uograding when you change something.
It's normal and should be as is that when there is non connection the grid is empty.

But I do it when change something on dataset (load biggest data, close / open) so the display is stable and the operations on dataset are much faster.

I do know, what DisableControl/EnableControl is for.

You disable the controls, you do your query

now either one oh these 2
1) you enable controls, and they update/repaint immediatly, then you close your Dataset --> Control is repainted again, and it's empty
2) you close your Dataset, and then enable controls, and they update/repaint immediatly --> because Dataset is closed, control is empty


I'm out of here.....

[Xenno]

Yes, @1HuntnMan, it could be related to an uncommitted transaction. This sort of issue usually doesn't happen when no transactions are involved. Unfortunately, transactions are mandatory in SQLdb, even though, as far as I know, no database engine strictly requires a transaction to exist for every single SQL statement execution.

You might want to try tracing your transaction calls (including auto calls) to see where a transaction might be left open or uncommitted.

[1HuntnMan]

Okay, Zvoni, I'm going to try leaving the QryCountClnts open instead of closing even though after the procedure TFrmClientsMgt.UpdateTTLClients; finishes, the TEdit on the form updates properly with the Total Client Records and testing after inserting/appending a new record and also after Deleting a record.  The TEdit is updated properly with the Total Client records.  When you close the Clients Mgt. form back to my main form, go into Proposals or Sales Orders and then back to main form. Then go back into ClientsMgt, the TEdit is blank???? The UpDateTTLClients procedure is executed when you first enter the form in the FormShow.  So, that's my mystery.

I'm going to comment out closing the QryCountClnts at the end of the UpdateTTLClients procedure and see if this makes a difference. I don't see why but will give it a try. Be back in a bit...

[1HuntnMan]

Okay Zvoni and all, finally figured this out. Put this off for awhile again but reading again thru all the posts, got to thinking. A while back I was having issues with Database is Locked error and I think Zvoni or someone suggested that they had a similar issue because they had DBrowser up with the database open. So, I still had code in my FormClose in most of these forms in which I was setting a TEdit with the total record count. All this code was an attempt to destroy components on the way out of the form.  None of this was necessary in the first place to eliminate the Database is Locked error. So, removed all this unnecessary code, tested and now my total record count is working properly no matter how many times I open a form, close it, open it, close it. Awesome! I really want to thank all for the discussions esp. DisableControls/EnableControls. That was unnecessary also!