Retrieving Identity Values after DetailsView Insert

[Mark Olbert]

I am struggling with trying to retrieve the value of an autoincrement identity field after a DetailsView Insert operation. The
DetailsView is bound to an SqlDataSource control.

So far as I can tell, nowhere in the arguments for either the Inserted event for the DetailsView or the Inserted event for the
SqlDataSource control is the value stored. Adding a dummy bound column for the identity field to the DetailsView doesn't work,
either.

All I can think of is to handle the SqlDataSource control's Inserted event and issue a separate SqlCommand to do a SELECT
SCOPE_IDENTITY().

There has to be a better way than that!

And if there isn't...someone at Microsoft needs to be taken to task for not thinking about how Insert operations against SQL
databases really ought to address the identity field issue.

- Mark

 

[Mark Olbert]

Some further information...

Doing a separate SqlCommand to do a SELECT SCOPE_IDENTITY() AS newID doesn't work. It returns null. Same for SELECT @@IDENTITY AS
newID. I suspect that the first approach fails because SqlDataSource controls maintain their own SqlConnection, and so the SELECT
command is not in the same scope. I don't know why the second approach doesn't work.

Appending ;SET @newID = SCOPE_IDENTITY() to the SqlDataSource control's Insert query, and then specifying that @newID is an integer
parameter with an Output direction does work (i.e., you can retrieve the value of @newID from the Parameters collection of the
Command property within an event handler for the data source control's Inserted event.

But that seems like a dumb thing to have to do for every situation where I want to use a datasource control to insert a record into
a Sql database.

So I'd still like to hear of a more elegant answer...and if there isn't one, why in the world MS shipped something with such a huge
flaw.

- Mark

 

[Steven Cheng[MSFT]]

Hello Mark,

From your description, you're using SqlDataSource+DetailsView to insert
record into a certain table and the primary id of it is of autoincrement
column. therefore, you're wondering the proper way to let the DetailsView
or SqlDataSource get the autogenerated newid of the inserted record,
correct?

Based on my understanding, for such scenario, you should use the @@IDENTITY
or SCOPE_IDENTITY function(preferred) to get the new generated autoid after
executing your insert statement. I would suggest you create a stored
procedure for such insert scenario. e.g.

=============================
CREATE PROCEDURE [dbo].[usp_insert_item]
@name varchar(50),
@description varchar(300)
AS
BEGIN
insert items ([name],[description]) values(@name, @description)

declare @autoid bigint

set @autoid = SCOPE_IDENTITY()

return @autoid
END
==============================

Then, in your ASP.NET webpage's SqlDataSource, set insert commandtype as
"StoreProcedure" and statement set to the SP name. The IDE can help you
autogenerated the necessary parameter(include the returnvalue parameter)
e.g.

=====================
............................
<InsertParameters>
<asparameter Direction="ReturnValue" Name="RETURN_VALUE"
Type="Int64" />
<asparameter Name="name" Type="String" />
<asparameter Name="description" Type="String" />
</InsertParameters>

</asp:SqlDataSource>
===================

Thus, you can use the "SqlDataSource.Inserted" event to get the return
value from the called store procedure:

====================================
protected void SqlDataSource1_Inserted(object sender,
SqlDataSourceStatusEventArgs e)
{

foreach (DbParameter param in e.Command.Parameters)
{
Response.Write("<br/>" + param.ParameterName + ": " +
param.Direction);
}

Response.Write("<br/>autoid: " +
e.Command.Parameters["@RETURN_VALUE"].Value);
}
===============

Also, instead of returnvalue, you can explicitly declare an output
parameter for your storeprocedure and use output parameter to get the auto
generated id.

Hope this helps.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Mark Olbert]

Steven,

That's esentially what I figured out: you have to include a "call" to SCOPE_IDENTITY() in the Insert command, either through a sproc
(as in your example) or by manually changing the data source control's insert query (which is what I'm doing, because I don't like
littering my database with dozens of little sprocs).

But the real question is, why do I have to do this in the first place? It shouldn't come as a suprise that when a row gets inserted
into a table with an identity field the caller will want to know the value of the new identity field. I think this is a flaw in the
code generator for the SqlDataSource control.

- Mark

 

[Steven Cheng[MSFT]]

Thanks for your reply Mark,

Yes, so far for those autogenerated ID, SQL Server won't return it
automaticaly since standard insert SQL statement doesn't contains
additional output resultset. We need to use additional query to retrieve
the @@IDENTITY or SCOPE_IDENTITY value. This also helps make the insert
command behave consistent between normal table(without using auto increated
id column) and table that use auto generated id column. Also, for
SqlDataSource control, since it currently generate only single insert
statement and do not automatically call any additional script to retrieve
identity value.

Anyway, you're welcome to submit such request for the SqlDataSource control
or its designer code generate:

http://connect.microsoft.com/feedback/default.aspx?SiteID=210


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.