I am using Visual Web Developer 2005 Express Edition, ASP.NET 2.0 and SQL Server 2005 Express Edition.

I'm using a DetailsView control with the default set to "Insert". (DetailsView1)

I'm using a table adapter for the datasource. (ordersDS)
 

I have a table with the first column as an identity integer. When using the DetailsView control (default is set to insert) and I click the insert button to insert a new record, how can I get the identity integers value of the newly inserted record??

Thank you in advance

Check this link http://support.microsoft.com/kb/320141. You should call dataSet.AcceptChanges() and then iterate through rows count.

You can write a SP to insert and return the @@IDENTITY or max(ID) to get back he value of last inserted id.

mattia

There's a great post on scottgu's blog, it's very complete:

http://weblogs.asp.net/scottgu/archive/2006/01/15/Building-a-DAL-using-Strongly-Typed-TableAdapters-and-DataTables-in-VS-2005-and-ASP.NET-2.0.aspx

I have a more programatic approach for those that like to look under the hood:

http://www.gbogea.com/2008/4/9/using-scope_identity-with-tableadapters

Hope it helps.

http://aspnet.4guysfromrolla.com/articles/050207-1.aspx

http://blog.ataye.com.au/pt/blog/default.aspx?id=3&t=How-to-return-identity-after-insert-usin

Thank you for your response, BUT... I've spent 2 and a half days trying to figure this out... and it's just not getting easier. I've diligently tried the methods listed on this post (and many other blogs/posts) with absolutely no success.

Again, I'm using:
Visual Web Developer 2005 (not VisualStudio);
SQL Server 2005 Express Edition
c# codebehinds
TableAdapter ((.xsd) This is my datasource) I'm open to using any datasource scenario I need to, but I prefer to just build my datasources in the designer. Why? should be obvious.
DetailsView control (with the default set to Insert)

this is my SQL Server 2005 Express Edition stored procedure:

CREATE PROCEDURE [dbo].[Insert_ReturnID]
    @company int,
    @newOrderID int output
AS
BEGIN
    insert into tblOrders (tblOrders.company)
    values (@company)

    set @newOrderID = scope_identity()
END

This is my .xsd Insert statement:

INSERT INTO [tblOrders] ([companyID]) VALUES (@companyID);
SELECT @newOrderID = SCOPE_IDENTITY(); 

 I add another insert parameter to the generated insert parameters on my .aspx:

 <asp:Parameter Name="newOrderID" Direction="Output" Type="Int32" />

I believe the codebehind (c#) is where I mess it up... I get error after error. ***Also, it is here that EVERY post I've read gets soft and does not follow through...

I've seen probably 10+ variations on how to retrieve the ID from an insert. My problem is that not one! of those that I've seen gives a complete recipe for success, and each time I try to fill in the blanks on my own ...my builds fail!! I have a project due and this little lesson is costing me dearly.

Your help is greatly appreciated. Thank You. 

Ok, so let's take one of the tutorials as reference and I'll try to get you through it.

http://weblogs.asp.net/scottgu/archive/2006/01/15/Building-a-DAL-using-Strongly-Typed-TableAdapters-and-DataTables-in-VS-2005-and-ASP.NET-2.0.aspx

In the link above follow Tutorial 5. Let me know where you get the error and what is the error (compilation or runtime) and it's message. If your code is not compiling post the section of the code that presents the error.

 gbogea, Thank you for your reply and continued assistance...

These are the steps I took in going through ScottGu's blog:

 
1. create new .xsd (ds_Orders_3.xsd)
2. create primary select statement/method for TableAdapter via TableAdapter Wizard:

ds_Orders_3
 

SELECT     company, orderID
FROM         Orders_Table

 
3. Create Insert Method:

InsertCompanyReturnOrderID
 

INSERT INTO [Orders_Table] ([company]) VALUES (@company);
SELECT SCOPE_IDENTITY()

hi,

  You can use the Scope_Identity function to return the last inserted  row number.

cheers mate

Ok, so first of all we need to check if your new inser method is working correctly. For this you can create an aspx page with a button and a label. On the button click event we will create the TA, insert a record and then return it's value to the label. The code on the button click should be as follows:

ds_Orders_3TableAdapters.Orders_TableTableAdapter ta = new ds_Orders_3TableAdapters.Orders_TableTableAdapter();

int id = ta.InsertCompanyReturnOrderID(3);

Label1.Text = id.ToString();

Just make sure that in your table the OrderID is set to autoincrement (it doesn't hurt to ask :-) )

Compare this value in the label with the value in the most recent record in the db. Let me know how this test works.

1. I created an .aspx (test_4).
2. added the button and the label.
3. double-clicked the button and added your code to the button click event
4. ran in browser

I received this error message:

Compiler Error Message: CS0266: Cannot implicitly convert type 'object' to 'int'. An explicit conversion exists (are you missing a cast?)

Source Error:

Line 21:     {
Line 22:         ds_Orders_3TableAdapters.Orders_TableTableAdapter ta = new ds_Orders_3TableAdapters.Orders_TableTableAdapter();
Line 23:         int id = ta.InsertCompanyReturnOrderID(3);
Line 24:         Label1.Text = id.ToString();
Line 25:     

Source File: c:\Documents and Settings\...\test_4.aspx.cs    Line: 23

what do you think?

 It works...

I added Convert.ToInt32(...) to line 23:
int id = Convert.ToInt32(ta.InsertCompanyReturnOrderID(3));

and now when I click the button the value inserts "3" into the database as the companyID and the value in the label increments properly.

This is the desired result with regard toward the insert method.. but ..unfortunately I'm back to filling in the blanks as I was in previous attempts. Do you have any recommendations now that we know the insert method has been properly implemented.

Ok, my bad about the typecast, I really forgot about it.

Now, what do you want to do with the DetailsView? You are going to insert a record using the Insert method that returns the newly created record, right? After you insert the record what do you want to happen? Where do you want to use new record id?

for now... I just want to return the value of that newly inserted record to a label as we have in the "button/label" scenario.

It just seems to make sense at this point... as I don't know exactly how it is all going to be put together in the end.

My Intent (*I really don't want to complicate things with strategy unless you see a blaring red flag.), I have 5 separate tables in my database that together comprise an "Order". It seems as though using a simple asp.net Wizard control would be the smartest way to go. So that when someone enters the information they have their hand held as they "step" their way through building an order. (*I should note that I understand that the asp.net Wizard control has persistence built into it, but as far as I know persistence is no longer usable when you insert multiple forms)

My ideal scenario: 

 I drop a detailsView into each of the 5 "steps" of the Wizard;
 When a user inserts data in a particular form I'd like the newly inserted record to populate with that newly inserted records data;
 Also, ** and more importantly I need the orderID of the newly inserted record to be the orderID of the following 4 forms.

 
If I was vague or you don't understand any particular part of my strategy ...Please ...let me know
 

But for now... I just want to return the value of that newly inserted row into a label as we have in the "button/label" scenario. I need more than anything else at this point to understand how to do that for any future application of this process.

I haven't had time to test right now but you might try something this: