Goto last record entered on a sorted dataset
lazarus / 16-Databases

----------------------------------------------------------------------
daveinhull · 2018-12-23 16:03

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

----------------------------------------------------------------------
madref · 2018-12-24 10:50

I am assuming that you know which Actual_ID it has and then is't
simple.


Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
 SQLQuery.Refresh;   SQLQuery('YourIDTag_In SQLQuery', Actual_ID, []);

----------------------------------------------------------------------
daveinhull · 2018-12-24 10:55

Hi Madrid, No I don't know the actual ID as it is an auto number.

----------------------------------------------------------------------
madref · 2018-12-27 12:23

Thats easy to obtain.
When you add a new record, save the ID and then you have your actual
ID of the new record

----------------------------------------------------------------------
daveinhull · 2018-12-28 15:49

Thanks madref, thought I'd already tried that but was getting null -
will need to check the code again. Thanks

----------------------------------------------------------------------
madref · 2018-12-30 09:02

Take a look at this small code
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
TQ_Divisies.Open;    TQ_Divisies.Insert;    MaxID :=
StrToIntDef(MLookup('MAX(Divisie_ID)','tbl_Divisie','1=1'), 0);   
MaxID := MaxID + 1;    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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //---------
----------------------------------------------------------------------
----------// Search for the value of VELD in the tabel TABEL with a
match to VOORWAARDE.//------------------------------------------------
------------------------------------------function MLookup(Veld,
Tabel, Voorwaarde: string): variant;var mySql: TSQLQuery;begin  mySQl
:= TSQLQuery.Create(nil);  mySQl.DataBase :=
Form_RefereeMain.Connect_RefereeDB;  try    mySql.SQL.Text :=
Format('SELECT %s FROM %s WHERE %s', [veld, tabel, voorwaarde]);   
mySql.Open;//    Result := mySQL.Fields[0].AsVariant;    Result :=
MySQL.Fields[0].Value;  finally    MySql.Free;  end;end;     //
MLookup

----------------------------------------------------------------------
daveinhull · 2018-12-30 11:31

Thanks for the code madref, some useful learning points as well

I'd actually ended up doing this as suggested earlier.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      
Query.Refresh;      SQLQuery8.SQL.Text := 'SELECT MAX(ID) AS MaxID
FROM ' + Table;      SQLQuery8.Open;      Query.Locate('ID',
SQLQuery8.FieldValues['MaxID'], []);      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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
mySql.SQL.Text := Format('SELECT %s FROM %s WHERE %s', [veld, tabel,
voorwaarde]);
I'd been trying to use
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
mySQL.SQL.Text := 'SELECT :FieldName FROM :TableName GROUP BY
:FieldName';mySQL.Param.ParamByName ('TableName').Value :=
'Name';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

Thanks for the learning
Dave

----------------------------------------------------------------------
madref · 2018-12-30 23:08

I never insert it into the DBGrid, but i insert it into the query that
belongs to the DBGrid.

----------------------------------------------------------------------
daveinhull · 2018-12-31 00:40

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.

----------------------------------------------------------------------
madref · 2018-12-31 10:14

I never put anything directly into the dbgrid, i always use a new form
for that.
But that is just me.