Accepted way to grab the ID of a record you have just inserted?

[darrel]

I have a page that inserts a new record into a DB. Upon doing so, I want to
refresh the page and return the data that was just entered (including the
primaryKey for this new record)

I'm using asp.net and MS SQL.

Normally, what I've done in the past, is just requery the DB using a few of
the known fields I just entered and grab the record. This works, but,
obviously, there's the chance that someone may have entered the exact same
set of data and I then pull up the wrong record.

Is there a better way to do this? Perhaps using a SQL query itself?
Something like 'grab the last record entered immediately after inserting
it?'

-Darrel

[Chris In Madison]

Tom Muck has an extension for this at:

[url]http://www.tom-muck.com/extensions/help/insertretrieve/[/url]

But the short story is that

SELECT @@identity

after your insert should retrieve that ID, if memory serves.

Best regards,
Chris

[Chris In Madison]

Sorry, that's only VBScript...

This should be helpful:

[url]http://www.windowsitpro.com/Article/ArticleID/7134/7134.html?Ad=1[/url]

Best regards,
Chris

[darrel]

> Sorry, that's only VBScript...

>
> This should be helpful:
>
> [url]http://www.windowsitpro.com/Article/ArticleID/7134/7134.html?Ad=1[/url]
>
> Best regards,
> Chris

Chris...I think that's exactly what I need. Thanks!

By any chance are you a .netter? I'll toss this follow-up question to anyone
that can maybe answer it:

Normally, when I do an insert/update, I used a "
objCommand.ExecuteNonQuery()" command. However, when I do a select, I use a
"
objOleDbAdapter.Fill" command. What do I use if I am using a compound query
that both selects AND insert/updates?

-Darrel

[Lionstone]

Stored procedure.
CREATE PROCEDURE foo
@p1 TYPE,
@P2 TYPE,
etc
AS
SET NOCOUNT ON
INSERT INTO Table()
VALUES()
RETURN @@IDENTITY
SET NOCOUNT OFF

The key is the return value of the SP. No need for a whole recordset just
to get a single value.


"darrel" <notreal@hotmail.com> wrote in message
news:d7506o$9dj$1@forums.macromedia.com...

>I have a page that inserts a new record into a DB. Upon doing so, I want to
> refresh the page and return the data that was just entered (including the
> primaryKey for this new record)
>
> I'm using asp.net and MS SQL.
>
> Normally, what I've done in the past, is just requery the DB using a few
> of
> the known fields I just entered and grab the record. This works, but,
> obviously, there's the chance that someone may have entered the exact same
> set of data and I then pull up the wrong record.
>
> Is there a better way to do this? Perhaps using a SQL query itself?
> Something like 'grab the last record entered immediately after inserting
> it?'
>
> -Darrel
>
>

[Chris In Madison]

Well, in classic ASP, I'd use the Recordset object since there's a SELECT
involved. It's been a couple of years since I did anything really
complicated, but you can execute complex SQL with the Recordset object, and
if there are multiple SELECTS that happen, there is a Recordset collection
that's returned that you can iterate through.

The SET NOCOUNT OFF shuts that off, if I recall, so you can probably do
something like this in the .NET equivalent of the Recordset (dataset?):

SET NOCOUNT ON;
' do insert thing
' do update thing
' do another insert thing
SET NOCOUNT OFF;
' do select thing

I believe that will return only one resultset that you can use to do your
databinding. But like I said, it's been a while :-)

~Chris

[darrel]

> Stored procedure.

> CREATE PROCEDURE foo
> @p1 TYPE,
> @P2 TYPE,
> etc
> AS
> SET NOCOUNT ON
> INSERT INTO Table()
> VALUES()
> RETURN @@IDENTITY
> SET NOCOUNT OFF
>
> The key is the return value of the SP. No need for a whole recordset just
> to get a single value.

Just to confirm, the ONLY way to use @@IDENTITY is to use a SP? If so,
that's what I'll use. We've been avoiding that on the admin side of our
application, but I'll use what makes the most sense.

-Darrel

[Lionstone]

Only way? No.
But it's much more efficient to return it as a return value from a SP than
to construct an entire result set to get a scalar value. Whether it's a
classic ASP recordset or a .NET datareader, there's no point in making
what's a rather expensive object.

Whatever you do, don't break up the query. That is, don't execute the query
in two steps. You just lost your guarantee that @@IDENTITY will do its job.
So use a SP, or good luck figuring out the compound query business.

In classic ASP, the NextRecordset method will do the trick.
Set newID = commandName.Execute().NextRecordset()

Or, if you're using NOCOUNT (SET NOCOUNT ON), just
Set newID = commandName.Execute()

Then, newID.Fields.Item(0).Value is what you want.
Maybe that can jump-start your .NET investigation.


"darrel" <notreal@hotmail.com> wrote in message
news:d75b6c$ocf$1@forums.macromedia.com...

>> Stored procedure.
>> CREATE PROCEDURE foo
>> @p1 TYPE,
>> @P2 TYPE,
>> etc
>> AS
>> SET NOCOUNT ON
>> INSERT INTO Table()
>> VALUES()
>> RETURN @@IDENTITY
>> SET NOCOUNT OFF
>>
>> The key is the return value of the SP. No need for a whole recordset
>> just
>> to get a single value.

>
> Just to confirm, the ONLY way to use @@IDENTITY is to use a SP? If so,
> that's what I'll use. We've been avoiding that on the admin side of our
> application, but I'll use what makes the most sense.
>
> -Darrel
>
>

[Lionstone]

Absolutely not. SET NOCOUNT OFF doesn't affect @@IDENTITY, multiple
recordsets from a single query or query batch, or even @@ROWCOUNT for that
matter. Its one purpose is to stop the return of the "(x rows affected)"
message to the client. Saves data transfer, and in some cases, an extra
object creation.

"Chris In Madison" <cowens@cnwGOAWAYDIRTYRATSPAMMERS.com> wrote in message
news:d758gf$kib$1@forums.macromedia.com...

> Well, in classic ASP, I'd use the Recordset object since there's a SELECT
> involved. It's been a couple of years since I did anything really
> complicated, but you can execute complex SQL with the Recordset object,
> and
> if there are multiple SELECTS that happen, there is a Recordset collection
> that's returned that you can iterate through.
>
> The SET NOCOUNT OFF shuts that off, if I recall, so you can probably do
> something like this in the .NET equivalent of the Recordset (dataset?):
>
> SET NOCOUNT ON;
> ' do insert thing
> ' do update thing
> ' do another insert thing
> SET NOCOUNT OFF;
> ' do select thing
>
> I believe that will return only one resultset that you can use to do your
> databinding. But like I said, it's been a while :-)
>
> ~Chris
>
>

[Chris In Madison]

Yeah, it's been a while... Like about three years. Wouldn't surprise me if
I was wrong :-)

~Chris