CODEWITHSUNDEEP
sql-serverdelphitadoquery
TheMuyu
TheMuyu

Reputation: 587

Get last row data after insert into using Delphi AdoQuery

In Delphi 2007 using ADOQuery when i add a row into a table using insert into how do i get last row?

i'm adding row with this.

QD_TEMP1.close;
QD_TEMP1.sql.Clear;
QD_TEMP1.SQL.Add('insert into s_hasta_Kabul (Protokol,Muay_ID,Ocak_Kod,Tc_Kimlik_No)  ');
QD_TEMP1.SQL.Add('values (:Protokol,:Muay_ID,:Ocak_Kod,:Tc_Kimlik_No) ');
QD_TEMP1.Parameters.ParamByName('Protokol').Value := 0;
QD_TEMP1.Parameters.ParamByName('Muay_ID').Value := 2;
QD_TEMP1.Parameters.ParamByName('Ocak_Kod').Value := 3;
QD_TEMP1.Parameters.ParamByName('Tc_Kimlik_No').Value := 4;
QD_TEMP1.ExecSQL;

How do i get last added row after execsql ?

Upvotes: 2

Views: 7272

Answers (4)

user12415227
user12415227

Reputation: 1

qryTest.Close;

qryTest.SQL.Clear;
qryTest.SQL.Add(' INSERT INTO TEST_GET_IDX ');
qryTest.SQL.Add(' (SNAME, AGE) ');
qryTest.SQL.Add(' VALUES(:pSNAME, :pAGE) ' );
qryTest.SQL.Add(' SELECT IDX = SCOPE_IDENTITY() ' );   // IDX is IDENTITY

qryTest.Parameters.ParamByName('pSNAME').Value:= edSname.Text;
qryTest.Parameters.ParamByName('pAGE').Value:= edAge.Text;

qryTest.Open;

showMessage(dsTest.DataSet.FieldByName('IDX').AsString);

Upvotes: 0

Tom
Tom

Reputation: 111

It works if you set the parameter direction before "ExeSQL".

q.sql.text := 'insert into TABLE(x) values (:value) set :ID = scope_identity()';
q.Parameters.ParamByName('VALUE').value := 'XXXXX';
q.Parameters.ParamByName('ID').Direction := pdReturnValue;
q.ExecSQL;
ID := q.Parameters.ParamByName('ID').value;

Cheers

Upvotes: 4

kobik
kobik

Reputation: 21252

You could use the OUTPUT clause to return the inserted recordset, whether or not you have an identity column (it seems form your comments you do not have one).

e.g.

QD_TEMP1.Close;
QD_TEMP1.sql.Clear;
QD_TEMP1.SQL.Add('insert into s_hasta_Kabul (Protokol,Muay_ID,Ocak_Kod,Tc_Kimlik_No)  ');

QD_TEMP1.SQL.Add('OUTPUT inserted.*');

QD_TEMP1.SQL.Add('values (:Protokol,:Muay_ID,:Ocak_Kod,:Tc_Kimlik_No) ');
QD_TEMP1.Parameters.ParamByName('Protokol').Value := 0;
QD_TEMP1.Parameters.ParamByName('Muay_ID').Value := 2;
QD_TEMP1.Parameters.ParamByName('Ocak_Kod').Value := 3;
QD_TEMP1.Parameters.ParamByName('Tc_Kimlik_No').Value := 4;

QD_TEMP1.Open; // ExecSQL does NOT return recordset
// QD_TEMP1 now contains the inserted result set
ShowMessage(QD_TEMP1.FieldByName('Tc_Kimlik_No').AsString);

Note we use QD_TEMP1.Open (not ExecSQL) to return the inserted recordset.

Upvotes: 5

GuidoG
GuidoG

Reputation: 12014

have not tested it but it should be something like this

QD_TEMP1.close;
QD_TEMP1.sql.Clear;
QD_TEMP1.SQL.Add('insert into s_hasta_Kabul (Protokol,Muay_ID,Ocak_Kod,Tc_Kimlik_No)  ');
QD_TEMP1.SQL.Add('values (:Protokol,:Muay_ID,:Ocak_Kod,:Tc_Kimlik_No) SELECT :test = SCOPE_IDENTITY() ');
QD_TEMP1.Parameters.ParamByName('Protokol').Value := 0;
QD_TEMP1.Parameters.ParamByName('Muay_ID').Value := 2;
QD_TEMP1.Parameters.ParamByName('Ocak_Kod').Value := 3;
QD_TEMP1.Parameters.ParamByName('Tc_Kimlik_No').Value := 4;
QD_TEMP1.ExecSQL;

QD_TEMP1.Parameters.ParamByName('test').Value is your new id

Upvotes: 0

Related Questions