2

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 ?

TheMuyu
  • 579
  • 2
  • 12
  • 31
  • 2
    is there any identity column in your table – wiretext Oct 22 '15 at 12:24
  • i have 5 identity colum yes, and total 30 columns. dont ask why 5 identity. i dont know its a old table belongs to a company – TheMuyu Oct 22 '15 at 12:30
  • 1
    but you have only 1 primary key right :) i don't not know about delphi but SQL point of view after insertion `SELECT @Id = SCOPE_IDENTITY()` whatever comes in this just pass as a `SELECT * FROM s_hasta_Kabul WHERE pkColumn = @Id` – wiretext Oct 22 '15 at 12:34
  • 3
    What do you mean by "5 identity column"? you can have only one identity column per table. – kobik Oct 22 '15 at 13:30
  • Umm....you don't have 5 identity columns in one table. You can only have 1 per table. – Sean Lange Oct 22 '15 at 13:30
  • The "best" way would be to stop using pass through queries and use a stored procedure instead. Then you can easily have the procedure select the row that just inserted. – Sean Lange Oct 22 '15 at 13:31
  • i know but as i said i dont create this db and its old. here pic link you can see: http://i.hizliresim.com/RBQOQR.png – TheMuyu Oct 22 '15 at 13:50
  • 1
    @Jasper - not the image I'm looking at. Not only does the PK comprise 5 values when only 4 are specified in the INSERT statement in the question, but of those 4 only 2 are part of the PK (**Okac_Kod** and **TC_Kimlik_No**). – Deltics Oct 22 '15 at 22:25
  • @Deltics I was overhasty, will delete the comment – Jasper Schellingerhout Oct 23 '15 at 12:25

4 Answers4

5

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.

kobik
  • 21,001
  • 4
  • 61
  • 121
4

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

Tom
  • 103
  • 6
0

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
GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • 1
    That won't work. First, `QD_Temp1.Parameters.ParamByName('test');` before `QD_TEMP1.ExecSQL;` won't compile. Second, you're missing a `;` at the end of the `INSERT` (before the `SELECT`). – Ken White Oct 22 '15 at 20:26
  • If you read my answer very carefully, you will see the text : 'have not tested it' and the text "should be something like this'.This was to indicate that I typed this from the top of my head and did not test it, but it should get the OP in the right direction – GuidoG Oct 23 '15 at 10:02
  • 3
    If you post untested, uncompilable code here as an answer, you can expect it to be downvoted. If you don't know it will work (by testing), you bear the responsibility for what happens to your reputation here. Answers should be correct and accurate, not speculation or made-up things from your keyboard that clearly are incorrect. I gave you a chance to correct it; you chose not to do so. – Ken White Oct 23 '15 at 13:40
  • Really ? So if I dont have a delphi at hand to test the exact syntax than I should not answer ? The answer clearly stated that the code was meant to help the OP on his way. Is all you think about, your reputation here ? Well ok than, be a child than and downvote me, whatever makes you happy – GuidoG Oct 23 '15 at 15:19
  • 1
    You can feel free to post any answer you want at any time, tested or untested. If it's wrong, expecf it to be downvoted. It has nothing to do with my reputation; it's the **specific way this site works**. Answers to questions are expected to be **complete and correct**, and yours fails at least the latter. See the [help] if that's not clear to you. While you're there, visit the section on [behavior](http://stackoverflow.com/help/howtobehave). And as far as *helping people*, wrong answers help no one. – Ken White Oct 23 '15 at 15:46
  • You assume OP has identity column. In any case, I have tested this code setting `test` parameter `Direction` to `pdOutput\ pdInputOutput\ pdReturnValue` which fails with `Parameter object is improperly defined. Inconsistent or incomplete information was provided` – kobik Oct 25 '15 at 08:04
0
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);