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

[B. Chernick]

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.)

 

[Mark Rae [MVP]]

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()

 

[bruce barker]

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)

 

[B. Chernick]

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.

 

[B. Chernick]

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.

 

[B. Chernick]

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.