Hello there,

I have set a master-detail relation between two tables.  These are shown on
the same screen in my application.  When I am adding a new record, I first
fill in some values in the master and then move on to the detail, which is
displayed in a grid.  The primary key of the master, which is an
auto-increment field(Identity) is not available when I enter the details
grid, even if I use the post method on the master. Am I missing something?

Thanks for your attention and help.

with regards,
Vinod

Vinod,
   When you do a post, the value of the auto-incrementing field will
not be returned to you. The best solution is to insert the master
record using a stored procedure. Then you use the @@IDENTITY to get
the last identity value inserted, as in below example
CREATE PROCEDURE SP_INS_MASTER
@PARAM1 INT,
@PARAM2 INT,
.....
@VALUE INT OUTPUT
AS
BEGIN
  INSERT INTO MASTER(FIELD1, FIELD2, ....)
  VALUES (@PARAM1, @PARAM2, ....)
  SELECT @VALUE = @@IDENTITY
END
GO

After you call StoredProc1.ExecProc, the @VALUE parameter will have
the value of the auto-inc field, which you can now use for the detail
tables. It's a bit messy, but not all solutions things in life are
pretty.

Regards,
Jason.

No spam, unless requested. Thanks.

Hello Jason,

Thanks, for you help.  It worked.  Though I didn't use a stored procedure
for posting the record,  I used a query to get the @@IDENTITY value and used
it in my program.  Is there any other alternative too?  You have mentioned
that this is the best way.

And another small question from this, will @@IDENTITY return the last
identity value for the current session, I am using Delphi session component.
What happens when multiple users post records? Will I get the current value
on each machine?

with regards,
Vinod

news:8kekcf$srr9@bornews.borland.com...

Hi Vinod,
  Another method of getting the last value that I have seen is to
simply do a SELECT MAX(<FIELDNAME>) FROM <TABLENAME> within another
transaction. If you have alot of users, you can imagine the problems
with this. Another method is to close and reopen the table and goto
the last record. Again same problems here.

   As far as I understand @@IDENTITY is within the current
transaction, which is why it is recommended to use a stored procedure.
Where you could get problems is where the table you are inserting into
has a trigger that inserts into other tables that have auto-inc
fields. In this case the @@IDENTITY does not return the last identity
value of the master table but of one of the other tables.

  I have always used the stored procedure way and haven't had any
problems, however it does require a bit more coding.

Regards,
Jason.

No spam, unless requested. Thanks.

Hello Jason,

Thanks for all that info.

I have a new problem now.  As I have told earlier I am using SQL Server as
backend.  I have a master-detail form. The detail info. is shown in a
dbGrid.  To add a new set of records, I am following the following sequence
1. I start a transaction
2. I am entering the master info. in dbEdits
3. Post this info. and get the @@IDENTITY for use when adding detail records
4. Enter detail info.in the grid
5. Commit / Rollback

While entering new records in the grid for the first time, my first record
goes missing when I move from first row to next.
This record is present in the backend, when I check it.

But if I add only one record in the detail and commit the transaction, I
don't have any problem.  I can then edit the details without any problems.

This seems to be a known problem.  Are you aware of any workarounds for
this?

with regards,
Vinod