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?
moayyadAsked:
Who is Participating?
 
atul_parmarConnect With a Mentor Commented:
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
 
TheRealLokiConnect With a Mentor Senior 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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
TheRealLokiConnect With a Mentor Senior 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
 
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
 
moayyadAuthor Commented:
my database is ms access. and this work with me thanks alot (f).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.