• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

how to get the value of the a specific field in the last record?

Hello,

i think to make the ID field as a sequential number. and i want to give it the last ID+1 , so, how can i get the last record in a specific table?.

i succeeded to get the last record index by using:

table1.RecordCount

but now i want to get the value of the field 'id' for the last record table1.RecordCount !.

would u help me please?
0
moayyad
Asked:
moayyad
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
TheRealLokiSenior DeveloperCommented:
assuming your table has the incrementing value as the key field

table1.last;
lastvalue := table1.Fields[0].AsInteger;
// or Table1ID.Asinteger if you have declared the field defs
table1.insert;
table1.Fields[0].AsInteger := lastvalue + 1;
...
table1.post;

you could even keep a 2nd TTable on the form, just to do the look up
0
 
kretzschmarCommented:
usual most database-systems are providing a like autoincrement-value

what for a database do you use?

meikl ;-)
0
 
TheRealLokiSenior DeveloperCommented:
FYI, someone in this thread is having trouble with autoincrement tables, and was recommended to make their own
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21867176.html

If you want to do it automacially, so you don't have to think about it. click on your TTable, and then events (in the object inspector) and double click in the "OnNewRecord" event
place this code

procedure TForm1.Table1NewRecord(DataSet: TDataSet);
var
    lastvalue: integer;
begin
//Table_Lookup is just a copy of Table1, but it is only used for working out the last ID
    Table_Lookup.Open;
    if Table_Lookup.EOF then lastvalue := 0
    else
    begin
        Table_Lookup.Last;
        lastvalue := Table_Lookup.Fields[0].AsInteger; // could use Table_LookupID.AsInteger if you have assigned field names
    end;
    Dataset.Fields[0].AsInteger := lastvalue + 1; // could use Table1ID.AsInteger if you have assigned field names
end;

on the form, put another TTable, and call it "Table_Lookup".
set it's database and table name to the same as your Table1
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TheRealLokiSenior DeveloperCommented:
0
 
atul_parmarCommented:
The above idea will fail. You shouldn't rely on RecordCount or last record value as you delete a record id will be duplicated and in RDBMS like MSSQL there is nothing like last record. You must use a query that will do this job.

e.g. select isnull(max(id),0)+1 as newid from yourtable


0
 
moayyadAuthor Commented:
atul_parmar,
are you sure from this statment ?
because i had a general error in SQL change it to active?
select isnull(max(id),0)+1 from customer;
0
 
kretzschmarCommented:
isNull is an inBuild sql-function,
which must be supported by the sql-database
(guess its only for interbase/firebird) -> (the oracle dependant is NVL())

so it would be nice, if you could tell us which database u use,
so that we are able to give specific advice

meikl ;-)
0
 
atul_parmarCommented:
what database you are using? The above will work fine with MSSQL.

Or change it to it's base version as

select max(id) as LastID from customer;
then
var
  NewID : Integer;
begin
  NewID := Query1.FieldByName('LastID').AsInteger +1;
  // use NewID
end;
0
 
moayyadAuthor Commented:
my database is ms access. and this work with me thanks alot (f).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now