At what point does a new Primary Key value become available?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

B. Chernick - 05 Dec 2007 22:10 GMT

Ok, I'm probably missing something simple but I need to get this running fast.

I'm writing a demo in ASP.Net 2.0. All Adds and Edits are normally done
through a DetailsView. The Detailsview is bound to an ObjectDateSource (very
much like in those demo videos) and when the page is editing a record, it's
passed the primary key as a query string. Very simple. In this case the
primary key is just that, an identity field. When I want to insert a record,
I pass the screen a -1 and that knocks it into Insert mode.

What I need to know is this. Is there any way to discover what the new
primary key of the new record is while still in the editing screen so I can
basically have the screen redirect to itself with the new primary key?

(I've been messing around with various Inserted events and stuff but I
haven't found anything so far.)

Reply to this Message

Mark Rae [MVP] - 05 Dec 2007 22:36 GMT

> What I need to know is this. Is there any way to discover what the new
> primary key of the new record is while still in the editing screen so I
> can
> basically have the screen redirect to itself with the new primary key?

All you need to do is wrap up your INSERT statement with a SELECT
SCOPE_IDENTITY - this will insert the new record and then return you the
value from its identity column...

Use an ExecuteScalar method of the Command object and then use that to do
the Response.Redirect....

E.g.

INSERT MyTable
(
<field names go here>
)
VALUES
(
<values go here>
)
SELECT SCOPE_IDENTITY()

Signature

Mark Rae
ASP.NET MVP
http://www.markrae.net

Reply to this Message

B. Chernick - 06 Dec 2007 01:35 GMT

Actually I feel like I'm this close to solving it but no luck so far.

This has already been done automatically by the xsd designer. When I go to
the TableAdapter properties of this table, I can see the InsertCommand has
been configured exactly like that. My only apparent problem is getting the
data out. Still working on it.

> > What I need to know is this. Is there any way to discover what the new
> > primary key of the new record is while still in the editing screen so I
[quoted text clipped - 19 lines]
> )
> SELECT SCOPE_IDENTITY()

Reply to this Message

bruce barker - 05 Dec 2007 22:47 GMT

it depends on when you assign it. using autonumber or identity, it done at
sql insert time, but if you use a key assignment table, then you can assign
it when the user say's add or insert.

-- bruce (sqlwork.com)

> Ok, I'm probably missing something simple but I need to get this running fast.
>
[quoted text clipped - 11 lines]
> (I've been messing around with various Inserted events and stuff but I
> haven't found anything so far.)

Reply to this Message

B. Chernick - 06 Dec 2007 03:11 GMT

Thanks, I'll have to look into this assignment table concept, but for right
now, this problem is looking more and more like an xsd configuration problem
(which I have only hours to solve.)

To be more specific, given a wizard-generated InsertCommand of the format:

Insert into table1 (<fields>) Values (<values>);
Select PK, etc from Table1 Where (PK=SCOPE_IDENTITY())

How do I set up an output parameter to return that new PK value? (I'm seeing
fragmentary references on the web that this is possible and that the
parameter should be retrieved in the ObjectDataSources Inserted event, so far
no concrete examples. And my trial-and-errors are mostly error so far.

> it depends on when you assign it. using autonumber or identity, it done at
> sql insert time, but if you use a key assignment table, then you can assign
[quoted text clipped - 17 lines]
> > (I've been messing around with various Inserted events and stuff but I
> > haven't found anything so far.)

Reply to this Message

B. Chernick - 06 Dec 2007 03:33 GMT

Disregard previous message. I think I've gotten the output parameter to
work. Just a matter of fiddling with the SQL and the properties of the
parameter itself. Thanks.

Reply to this Message

Rate this thread:

At what point does a new Primary Key value become available?

Free Magazines