Lazarus

Programming => Databases => Topic started by: madref on November 20, 2019, 08:48:13 am

Title: [Solved] Update form after an edit
Post by: madref on November 20, 2019, 08:48:13 am
I have a form with a StringGrid on it. This string grid is filled by a query (I choose this for certain reasons above a TDBGrid).
From this string grid I select 1 record to edit in another form.
This all works perfect.
The grid is nicely updated....but here it also goes wrong.
The query is sorted on date and time.
And because of this the update should also re-arrange the query so that the the dates are in the sorted order.
But that doe not happen....


How can I correct this?
Title: Re: Update form after an edit
Post by: MarkMLl on November 20, 2019, 09:11:20 am
Please explain a little more, possibly with an example of what the grid looks like and what it should look like.

MarkMLl
Title: Re: Update form after an edit
Post by: madref on November 20, 2019, 10:08:02 am
This is the code to edit.
It opens the form to edit and then comes backt to it's routine.
Code: Pascal  [Select][+][-]
  1. procedure TForm_Wedstrijd_Overzicht.BT_GameEditClick(Sender: TObject);
  2. var
  3.   wID: Integer;
  4.   fWI: TForm_WedstrijdInformatie;
  5.   c: TGridColumn;
  6.   F: TField;
  7.  
  8.  
  9. begin
  10.   if TQ_Wedstrijden.RecordCount =  0 then begin
  11.     Form_Message.MsgWindow(mThink, bOk,
  12.       'There are no games in the database.' + sCrLf +
  13.       'So no game can be edited !!', Prgnaam);
  14.     Exit;
  15.   end;  // if
  16.   wID := StrToInt (Grid_wedstrijden.Cells[0, Grid_wedstrijden.Row]);
  17.   fWI := TForm_WedstrijdInformatie.Create(Self);
  18.   try
  19.     fWI.ShowWedstrijd(wID);
  20.   finally
  21.     fWI.Free;
  22.   end;
  23.   TQ_Wedstrijden.Refresh;
  24.   TQ_Wedstrijden.Locate('Wed_ID', wID, []);
  25.   for wID := 0 to Grid_wedstrijden.Columns.Count - 1 do begin
  26.     c := Grid_wedstrijden.Columns[wID];
  27.     F := TField(c.Tag);
  28.     Grid_wedstrijden.Cells [wID + Grid_wedstrijden.FixedCols, Grid_wedstrijden.Row] := F.AsString;
  29.   end;
  30. end;     // BT_GameEditClick
  31.  


Notice that the 'Screen Before Edit' has the correct sorting and the 'Screen After Edit' not
Title: Re: Update form after an edit
Post by: MarkMLl on November 20, 2019, 10:35:32 am
As I see it you have a choice. The crude way of doing it is to use privileged information (i.e. state in the single program that's doing all the work) to recognise that something's changed, and to rerun the query that populates the main grid on screen.

The less-crude way is to use a database that has a notification/event facility (PostgreSQL, Firebird) and to make sure that when a row in the database is changed the program receives an event warning it that it's got to reload the grid.

MarkMLl
Title: Re: Update form after an edit
Post by: madref on November 20, 2019, 11:01:55 am
I am using SQLite
Title: Re: Update form after an edit
Post by: MarkMLl on November 20, 2019, 11:06:30 am
Which I think implies that the visibility of the database is limited to the single program. In that case the "principle of least hassle" is probably to simply re-run the query any time you know something's changed.

MarkMLl
Title: Re: Update form after an edit
Post by: madref on November 20, 2019, 11:18:34 am
That's is what I am doing all the time.


But I found the 'problem'.
The problem was a piece of code that every time I called this routine it would go looking for the closest game to the current date.
I only needed that part at the start when the form is being showed the first time.


I made that a separate procedure and deleted that part from the routine and put it somewhere else.
And vola. it worked.


Thanks for the help
Title: Re: Update form after an edit
Post by: Zvoni on November 21, 2019, 09:29:44 am
As I see it you have a choice. The crude way of doing it is to use privileged information (i.e. state in the single program that's doing all the work) to recognise that something's changed, and to rerun the query that populates the main grid on screen.

The less-crude way is to use a database that has a notification/event facility (PostgreSQL, Firebird) and to make sure that when a row in the database is changed the program receives an event warning it that it's got to reload the grid.

MarkMLl
You forgot SQLite has a Data Changed Notification, too.
Title: Re: Update form after an edit
Post by: MarkMLl on November 21, 2019, 10:30:20 am
You forgot SQLite has a Data Changed Notification, too.

I didn't forget, I didn't know.

Does that mean that you're volunteering to add it to the LCL components? At the moment they only support PostgreSQL and Firebird :-)

MarkMLl
Title: Re: Update form after an edit
Post by: Zvoni on November 21, 2019, 11:07:34 am
You forgot SQLite has a Data Changed Notification, too.

I didn't forget, I didn't know.

Does that mean that you're volunteering to add it to the LCL components? At the moment they only support PostgreSQL and Firebird :-)

MarkMLl
Hell no! Whatever gave you that idea? I'm still bumbling around a lot.  :P :-[
I'll probably come up with a class-helper for sqlite3conn, but that's about it.
There is one thing you have to consider with this Data Change Notification in SQLite3: You cannot use the same db-connection to call a refresh for, which actually committed the update (at least that's how i understand it).
Basically it boils down to:
Have/use 2 sqlite3conn-Objects
Register the callback-function with dbconn1 (and that's the one you use for CUD (NOT CRUD), but use dbconn2 to do the reads/display
https://www.sqlite.org/c3ref/update_hook.html
TinyPortal © 2005-2018