Hi All,

I have a few screens which allows the user to create a new item (doctors,
medications etc). These screens are called from screens that contain lists
of whatever the item is. On the list screens are buttons that include New,
Edit etc. When new is clicked the create new item screen shows.

This screen has non-data aware edits and I use an sql update component to
insert the new record once the user clicks "ok". The table has it's primary
key as a generated integer.

What I would like to do is, when the user clicks ok, the record is created
using an insert query, and then the create new screen closes, the focus goes
back to the list screen with the new record highlighted.

My question is, how do I get the newly created primary key field value? This
is auto generated, so I'm not sure how it can be returned so I can use it?

Any ideas ?

Cheers,
Paul

Paul


Since I generally use tables rather than queries I can never remember the answer to this but its been asked and answered loads of times. Check out the thread - How to retrieve an autoinc value after an INSERT - which looks to be a match for what you want. You could also try

SELECT MAX(autoincfield) from table

There's also this link

http://www.elevatesoft.com/newsgrp?action=openmsg&group=17&msg=1433&page=2#msg1433

I hope one of them helps.

Roy Lambert [Team Elevate]

Paul,

<< My question is, how do I get the newly created primary key field value?
This is auto generated, so I'm not sure how it can be returned so I can use
it? >>

Use this:

with MyEDBQuery do
  begin
  SQL.Text:='INSERT INTO MyTable VALUES (:NewKey, Value1, Value2)';
  ParamByName('NewKey').ParamType:=ptInputOutput;
  ExecSQL;
  ShowMessage('New key value is '+ParamByName('NewKey').AsString);
  end;

The above assumes that the TEDBQuery.ParamCheck property is True (the
default).

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi,

I use INSERT to add my record using your instruction below and I got the Primary Key field value, but when I use the query again to add my second record, error Duplicate Primary key is occured. And when I debug the program, I look the value for field primary key at the second Insert is same with the first record.

Please tell me how to make the query so it can check the new value (LASTAUTOINC after my first record insert) .

Thanks.


"Tim Young [Elevate Software]" wrote:

Paul,

<< My question is, how do I get the newly created primary key field value?
This is auto generated, so I'm not sure how it can be returned so I can use
it? >>

Use this:

with MyEDBQuery do
  begin
  SQL.Text:='INSERT INTO MyTable VALUES (:NewKey, Value1, Value2)';
  ParamByName('NewKey').ParamType:=ptInputOutput;
  ExecSQL;
  ShowMessage('New key value is '+ParamByName('NewKey').AsString);
  end;

The above assumes that the TEDBQuery.ParamCheck property is True (the
default).

--
Tim Young
Elevate Software
www.elevatesoft.com

Eddy


Can you post the code you're using please.

Roy Lambert [Team Elevate]

Sorry I already change my code now. But my first code is very simple, I want to get the KeyID for my new record, something like this :

Param NewKey act as my field name KeyID ( field generate Integer ) , used as my Primary Key.

var Test1, Test2 : Integer;

Begin
  MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (:NewKey, Value1, Value2)';
  MyEDBQuery.ParamByName('NewKey').ParamType:=ptInputOutput;
  MyEDBQuery.ExecSQL;
  Test1 := MyEDBQuery.ParamByName('NewKey').Value;   <------- success
  MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (:NewKey, Value3, Value4)';  
  MyEDBQuery.ParamByName('NewKey').ParamType:=ptInputOutput;
  MyEDBQuery.ExecSQL;                      <------------------- error duplicate primary key
  Test2 := MyEDBQuery.ParamByName('NewKey').Value;
end;

I check the Parameter NewKey at second query has the same value with the first one. How to make second query running well and I get the KeyID at Test2 variable.

Thanks.

For now I change my code with :

var Test1, Test2 : Integer;

Begin
 MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (Value1, Value2)';
 MyEDBQuery.ExecSQL;
 MyEDBQuery2.Close; MyEDBQuery2.Open;
 Test1 := MyEDBQuery2.Lookup('Field1',Value1,'KeyID');  <---- MyEDBQuery2.SQL='Select * From Mytable'
 MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (Value3, Value4)';  
 MyEDBQuery.ExecSQL;                     
 MyEDBQuery2.Close; MyEDBQuery2.Open;
 Test2 := MyEDBQuery2.Lookup('Field1',Value3,'KeyID');
end;

I know this code consume more time , but for now it's running well. If my problem above can be solved, it's better from this one

Eddy


I may be wrong but my understanding of what's going on is

First insert statement NewKey isn't set (ie is null) so the engine will create the new key value. second insert statement NewKey is set so the engine thinks you know best and uses the key. Partly its down to the column definition partly its down to your coding. If you alter your code to


Begin
  MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (:NewKey, Value1, Value2)';
  MyEDBQuery.ParamByName('NewKey').ParamType:=ptInputOutput;
  MyEDBQuery.ExecSQL;
  Test1 := MyEDBQuery.ParamByName('NewKey').Value;   <------- success
MyEDBQuery.ParamByName('NewKey').Clear; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (:NewKey, Value3, Value4)';
  MyEDBQuery.ParamByName('NewKey').ParamType:=ptInputOutput;
  MyEDBQuery.ExecSQL;                      <------------------- error duplicate primary key
  Test2 := MyEDBQuery.ParamByName('NewKey').Value;
end;


I think it should work. Haven't tested it though.

Roy Lambert [Team Elevate]

Test, it's running well.

Thanks Roy



Roy Lambert wrote:

Eddy


I may be wrong but my understanding of what's going on is

First insert statement NewKey isn't set (ie is null) so the engine will create the new key value. second insert statement NewKey is set so the engine thinks you know best and uses the key. Partly its down to the column definition partly its down to your coding. If you alter your code to


Begin
  MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (:NewKey, Value1, Value2)';
  MyEDBQuery.ParamByName('NewKey').ParamType:=ptInputOutput;
  MyEDBQuery.ExecSQL;
  Test1 := MyEDBQuery.ParamByName('NewKey').Value;   <------- success
MyEDBQuery.ParamByName('NewKey').Clear; <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
  MyEDBQuery.SQL[0]:='INSERT INTO MyTable VALUES (:NewKey, Value3, Value4)';
  MyEDBQuery.ParamByName('NewKey').ParamType:=ptInputOutput;
  MyEDBQuery.ExecSQL;                      <------------------- error duplicate primary key
  Test2 := MyEDBQuery.ParamByName('NewKey').Value;
end;


I think it should work. Haven't tested it though.

Roy Lambert [Team Elevate]