472,976 Members | 1,665 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,976 software developers and data experts.

Retrieving Identity Values after DetailsView Insert

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
Mar 2 '07 #1
4 14945
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
Mar 2 '07 #2
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>
<asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE"
Type="Int64" />
<asp:Parameter Name="name" Type="String" />
<asp:Parameter 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/subscripti...ult.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/subscripti...t/default.aspx.

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

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

Mar 5 '07 #3
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
Mar 5 '07 #4
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/feedbac...spx?SiteID=210
Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Mar 7 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Justin | last post by:
I have created a dataset with two tables and an insert command, I need to be able to retreive the Key Identity after inserting into table "A" for use in table "B". Should I use ExecuteScalar()...
0
by: cjbland | last post by:
First off I apologize if this has been discussed before, I'm not certain how to search for what I'm looking for so I figured I'd throw this out there and see what I got. I am working with a...
17
by: Rico | last post by:
Hello, I am in the midst of converting an Access back end to SQL Server Express. The front end program (converted to Access 2003) uses DAO throughout. In Access, when I use recordset.AddNew I...
3
by: Susanne Klemm | last post by:
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long...
8
by: Dave | last post by:
I have a form with a label that should show an invoice number. The invoice number should be generated by sql Server using an autoincremented technique. However, after reading several articles, it...
15
by: gunnar.sigurjonsson | last post by:
Im having some problem retrieving identity value from my newly inserted row into a view. I have two tables T1 and T2 which I define as following CREATE TABLE T1 ( id BIGINT GENERATED ALWAYS...
9
by: vertigo262 | last post by:
Hello All, I am trying to create an insert stored procedure with an @@identity output. need help This didn't seem dificult when I attempted it, but I can't for the life of me get the identity...
2
by: siddu57 | last post by:
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders...
0
by: Frank Swarbrick | last post by:
So we're trying to decide if it's better to use IDENTITY columns or sequences to create a surrogate key as the primary key for our tables. I kind of like the identity column, because it's more...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.