problem with @@IDENTITY

In MSSQL I have auto incrementing PK for the row I'm inserting into. The the
code I use below is to insert into the table. What I need is as soon as I
insert the record, I also need to return back what the PK was of my newly
inserted row. I will need to use this value for another section of the FORM
for use as a FK into another table in which I going insert some other data
based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help is
greatly appreciated. I pretty new at this stuff and just learning. Thank
You.
You forgot to include your code. Are you using a stored procedure or an
insert statement to insert the data?
Whatever option you are using you need to SELECT @.@.identity immediately
after the statement that performs the insert. If you are running SQL Server
2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
create proc foo
@.param type.....
as
INSERT INTO YourTable (column list) values (values)
SELECT @.@.identity
go
Keith
"news.microsoftnews" <sapk81@.yahoo.com> wrote in message
news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> In MSSQL I have auto incrementing PK for the row I'm inserting into. The
the
> code I use below is to insert into the table. What I need is as soon as I
> insert the record, I also need to return back what the PK was of my newly
> inserted row. I will need to use this value for another section of the
FORM
> for use as a FK into another table in which I going insert some other data
> based on a few criterias. I tried to us @.@.IDENTITY to no avail. Any help
is
> greatly appreciated. I pretty new at this stuff and just learning. Thank
> You.
>
|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OcORPnHsEHA.3876@.TK2MSFTNGP15.phx.gbl...
All relevant stuff.
In addition, if the table you are inserting into has an INSERT trigger which
in turn performs a 'cascaded' INSERT into another table that also has an
identity the value of @.@.IDENTITY or scope_identity() will be that of the
the other table. Beware. THere is a workaround though:
You MUST contrive to cache @.@.IDENTITY coming into your trigger
(i.e. set @.myid = @.@.IDENTITY)and reset it before leaving. If you don't do
this, Access will not be able to correctly track the row inserted and you
will get error
messages (like, the row does not satisfy the underlying criteria, or some
such).
Here is an SQL 2000 idiom to reset @.@.IDENTITY to @.myid (should be done as
the last thing before the trigger exits):
EXECUTE (N'SELECT Identity (Int, ' + Cast(@.myid As Varchar(10)) + ',1) AS id
INTO #Tmp'
G'luck
Malcolm Cook - mec@.stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

> You forgot to include your code. Are you using a stored procedure or an
> insert statement to insert the data?
> Whatever option you are using you need to SELECT @.@.identity immediately
> after the statement that performs the insert. If you are running SQL
Server[vbcol=seagreen]
> 2000 or higher you can use SELECT scope_identity() in place of @.@.identity.
>
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> create proc foo
> @.param type.....
> as
> INSERT INTO YourTable (column list) values (values)
> SELECT @.@.identity
> go
> --
> Keith
>
> "news.microsoftnews" <sapk81@.yahoo.com> wrote in message
> news:Oec$paHsEHA.2636@.TK2MSFTNGP09.phx.gbl...
> the
I[vbcol=seagreen]
newly[vbcol=seagreen]
> FORM
data
> is
>
sql