Recent

Author Topic: Goto last record entered on a sorted dataset  (Read 1252 times)

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Goto last record entered on a sorted dataset
« on: December 23, 2018, 05:03:34 pm »
Hi,

I have a dataset that is sorted in the SQL query. When I add a new record it gets sorted and the new record prepositions in the DBGrid as it would be expected.

I'm trying to then go to that record. If I use SQLQuery.Last it does to the actual last record, again as would be expected.

Is there any simple way to go to the actual last record entered, i.e. where the Autonumber ID is the max?

Any help would be appreciated.

Thanks
Dave

« Last Edit: December 30, 2018, 05:21:05 pm by daveinhull »
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Goto last record entered on a sorted dataset
« Reply #1 on: December 24, 2018, 11:50:57 am »
I am assuming that you know which Actual_ID it has and then is't simple.


Code: Pascal  [Select]
  1.    SQLQuery.Refresh;
  2.    SQLQuery('YourIDTag_In SQLQuery', Actual_ID, []);
  3.  
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Goto last record entered on a sorted dataset
« Reply #2 on: December 24, 2018, 11:55:15 am »
Hi Madrid, No I don't know the actual ID as it is an auto number.
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Goto last record entered on a sorted dataset
« Reply #3 on: December 27, 2018, 01:23:02 pm »
Thats easy to obtain.
When you add a new record, save the ID and then you have your actual ID of the new record
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Goto last record entered on a sorted dataset
« Reply #4 on: December 28, 2018, 04:49:19 pm »
Thanks madref, thought I'd already tried that but was getting null - will need to check the code again. Thanks
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Goto last record entered on a sorted dataset
« Reply #5 on: December 30, 2018, 10:02:38 am »
Take a look at this small code
Code: Pascal  [Select]
  1.     TQ_Divisies.Open;
  2.     TQ_Divisies.Insert;
  3.     MaxID := StrToIntDef(MLookup('MAX(Divisie_ID)','tbl_Divisie','1=1'), 0);
  4.     MaxID := MaxID + 1;
  5.     TQ_Divisies.FieldByName('Divisie_ID').AsInteger := MaxID;
And this is my function MLookUp. Which is almost the same as the Delphi equivalent of DLookUp.
Code: Pascal  [Select]
  1. //-----------------------------------------------------------------------------------------
  2. // Search for the value of VELD in the tabel TABEL with a match to VOORWAARDE.
  3. //------------------------------------------------------------------------------------------
  4. function MLookup(Veld, Tabel, Voorwaarde: string): variant;
  5. var mySql: TSQLQuery;
  6. begin
  7.   mySQl := TSQLQuery.Create(nil);
  8.   mySQl.DataBase := Form_RefereeMain.Connect_RefereeDB;
  9.   try
  10.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);
  11.     mySql.Open;
  12. //    Result := mySQL.Fields[0].AsVariant;
  13.     Result := MySQL.Fields[0].Value;
  14.   finally
  15.     MySql.Free;
  16.   end;
  17. end;     // MLookup
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Goto last record entered on a sorted dataset
« Reply #6 on: December 30, 2018, 12:31:43 pm »
Thanks for the code madref, some useful learning points as well :D

I'd actually ended up doing this as suggested earlier.
Code: Pascal  [Select]
  1.       Query.Refresh;
  2.       SQLQuery8.SQL.Text := 'SELECT MAX(ID) AS MaxID FROM ' + Table;
  3.       SQLQuery8.Open;
  4.       Query.Locate('ID', SQLQuery8.FieldValues['MaxID'], []);
  5.       SQLQuery8.Close;

What I still can see is when I do an insert into a DBGrid the ID is always Null in events like AfterPost, AfterInsert, etc, so I'm not sure when to catch the record to get the latest ID just inserted, would it be BefortPost?

A quick Off-topic for you to help me.
I'm interested in your use of:
Code: Pascal  [Select]
  1.     mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);

I'd been trying to use
Code: Pascal  [Select]
  1. mySQL.SQL.Text := 'SELECT :FieldName FROM :TableName GROUP BY :FieldName';
  2. mySQL.Param.ParamByName ('TableName').Value := 'Name';
  3. mySQL.Param.ParamByName ('FieldName').Value := 'Name';

But I always get an error. I guess you can't do this, but if you need to do it then your way is the way :D

Thanks for the learning
Dave
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Goto last record entered on a sorted dataset
« Reply #7 on: December 31, 2018, 12:08:44 am »
I never insert it into the DBGrid, but i insert it into the query that belongs to the DBGrid.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave

daveinhull

  • Full Member
  • ***
  • Posts: 234
  • 1 divided by nothing must still be 1!
Re: Goto last record entered on a sorted dataset
« Reply #8 on: December 31, 2018, 01:40:11 am »
I understand, but when you enter stuff into a grid and do something that ends up doing a Post you end up doing the same.
Version #:1.8.4 Date 2019-01-08 FPC Version: 3.0.4 and SVN Revision 57972 for x86_64-win64-win32/win64

madref

  • Hero Member
  • *****
  • Posts: 565
  • ..... A day not Laughed is a day not Lived !!
    • Nursing With Humour
Re: Goto last record entered on a sorted dataset
« Reply #9 on: December 31, 2018, 11:14:59 am »
I never put anything directly into the dbgrid, i always use a new form for that.
But that is just me.
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 2.0.0 / FPC 3.0.4
Lazarus Trunc / FPC 3.0.4
Mac OS X Mojave