Getting the ID of the last inserted record

[kingosticks]

i have the table
TblRound(
RoundID : Autonumber
RoundNo : integer
MatchID : string }

i need to get the RoundID when i insert a new record and assign it to the integer variable RoundID, i have been using the following (after inserting a new row) with no success:
QryMatch.SQL.clear;
QryMatch.Parameters.Clear;
QryMatch.SQL.Add('SELECT MAX(RoundID) FROM TblRound');
QryMatch.Open;
QryMatch.First;
RoundID:=QryMatch.FieldByName('TblRound.RoundID').Value;
QryMatch.close;

when i run this it gives me the error saying "QryMatch: Field 'TblRound.RoundID' not found". can someone please shed some light onto what im doing wrong. many thanks

 

[svanels]

Your query will return only one value in a dataset. This dataset will only know one column, and will call it MAX (you never gave the instruction to call the column RoundID). The name of the dataset also is not TblRound
the dataset will look like this:
MAX
20054

Don't use first and next on queries, this works good with tables.
Since you have only one value, use a parametrized query to store the value, something like ":maxValue"

Regards

 

[kingosticks]

ahhh thank you very much. please forgive my stupidity, i havnt done any sql in ages and never before in delphi. thanks again.

 

[svanels]

You can use the visual query-builder editor in delphi, to make your queries, it just works like access. If you are pleased with the result, you can see the sql generated. Copy-past it in the sql property of the query to get the results.
That is the way I started learning the sql-sintaxe. I tested your code with the sql-explorer on a table, to see what was realy happening.

Hope this helps.

The only stupid question is the one that is never asked.

Steven