Board index » delphi » Problem when posting a new record in SQL Server

Problem when posting a new record in SQL Server

I am using SQL Server 7.0 and am having a problem whe inserting records into
a table where the primary key field is an Auto Update field.

The data is inserted and posted to the tabel with no probelm, but I need to
know the value that is assigned to the AutoUpdate field when the record is
added.

I have tried using the afterpost method to query the field, but it simply
returns an empty field. If it refresh or requery the ado component, it
reloads the data and places the active record at the first record in the
table.

how can i add a new record, and when its posted get access to the Id that is
assigned by SQL to the record.

Is there a method or something that I can call which will update that one
record from the database with out changing the current record so that I can
access the id assigned by SQL.

Thanks

Rodney Lane

 

Re:Problem when posting a new record in SQL Server


one way to do this is to use a stored procedure to perform the insert and
return the value

create procedure SomeTableInsert
    @column1    somedatatype,
    @column2    somedatatype,
    ....
    @columnn     somedatatype

as

    insert into sometable(column1, column2, columnn) values (@column1,
@column2, @columnn)
    return @@identity

I'm at home, so I can't check this code at the moment. That's the spirit, if
not the exact syntax!

Anyway, the return value of @@identity is the last IDENTITY value used by
the current connection. You may experience better performance using stored
procs, too. Sorry about that ;)

Re:Problem when posting a new record in SQL Server


Quote
>I have tried using the afterpost method to query the field, but it simply
>returns an empty field. If it refresh or requery the ado component, it
>reloads the data and places the active record at the first record in the
>table.

>how can i add a new record, and when its posted get access to the Id that is
>assigned by SQL to the record.

Assuming you are using ADOExpress components set the Tfield AutoGenerate
property to arAutoInc

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads