Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, June 5, 2008 6:38 PM
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
All replies (17)
Thursday, June 5, 2008 9:29 PM ✅Answered
You can write a SP to insert and return the @@IDENTITY or max(ID) to get back he value of last inserted id.
mattia
Thursday, June 5, 2008 9:46 PM ✅Answered
There's a great post on scottgu's blog, it's very complete:
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.
Thursday, June 5, 2008 11:26 PM ✅Answered
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
Monday, June 9, 2008 7:50 PM ✅Answered
Ok, so let's take one of the tutorials as reference and I'll try to get you through it.
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.
Tuesday, June 10, 2008 2:04 PM ✅Answered
I haven't had time to test right now but you might try something this:
protected void ObjectDataSource1_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
int id = (int)e.ReturnValue;
}
In the objectdatasource use the Inserted event to get the return value of the insert. I think it should work.
Let me know how it goes.
Tuesday, June 10, 2008 5:57 PM ✅Answered
my apologies my unfortunate delay in response (excuse goes here)...
This code works...
protected void ObjectDataSource1_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
int id = (int)e.ReturnValue;
}
This inserts to the db;
returns the contents of the inserted record;
unfortunately the detailsView maintains its "insert only" state. That is, that when you insert a record the information repopulates the detailsView yet your still in "insert mode" (which earlier in this post that was partially my intention, although now I realize that won't work in my application), I need the detailsView to repopulate in "Edit Mode".
I have made a few unsuccessful attempts at making this happen.
I can get the detailsView into "Edit Mode" with this code:
protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
DetailsView1.ChangeMode(DetailsViewMode.Edit);
}
}
What I can't seem to do is get the detailsView back into "edit mode" on the inserted records value.
unsuccessful attempt1:
protected void ObjectDataSource1_Inserted(object sender, ObjectDataSourceStatusEventArgs e)
{
//int id = (int)e.ReturnValue;//worked well
String id = Convert.ToString(e.ReturnValue);
MessageLabel.Text = "Insert ID is " + id;
}
protected void DetailsView1_ItemInserted(object sender, DetailsViewInsertedEventArgs e)
{
DetailsView1.PageIndex = Convert.ToInt32(MessageLabel.Text.ToString());
DetailsView1.ChangeMode(DetailsViewMode.Edit);
}
further, the e.ReturnValue toString will give me (I believe) the ability to set paging for the rest of the detailViews in the wizard.
what do you think?
Tuesday, June 10, 2008 9:14 PM ✅Answered
About setting the DetailsView into edit mode try to do this:
DetailsView1.DefaultMode = DetailsViewMode.Edit;
DetailsView1.ChangeMode(DetailsViewMode.Edit);
If it still doesn't work take a look at this post:
http://www.velocityreviews.com/forums/t363325-change-formviewchangemode-to-edit-after-insert.html
further, the e.ReturnValue toString will give me (I believe) the ability to set paging for the rest of the detailViews in the wizard.
what do you think?
This might work while you have the id in a continuous sequence. As soon as you have a gap in it your logic will break because the id won't be equals to the page index. Maybe you can start a new post for each one of this problems, it will increase your chance to get over your problems faster. I can only post few hours a day so I might be a little slow in some responses.
Thursday, June 5, 2008 7:15 PM
Check this link http://support.microsoft.com/kb/320141. You should call dataSet.AcceptChanges() and then iterate through rows count.
Monday, June 9, 2008 6:49 PM
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.
Monday, June 9, 2008 11:07 PM
gbogea, Thank you for your reply and continued assistance...
These are the steps I took in going through ScottGu's blog:
- create new .xsd (ds_Orders_3.xsd)
- 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()
4. change execute mode from NonQuery to Scalar. save.
***This is where ScottGu's blog ends*** Scottgu's blog (allbeit is very helpful in many ways) from this point on takes a different direction than I can follow. He writes out an insert script in VB, and I'm using a detailsView control.
When I first attempted to use ScottGu's blog I tried a handful of variations (found in other posts) to try and complete the process. I was unsuccessful.***
_________________________________________________________________________
I continue with these steps
- Created an .aspx (test_3.aspx)
- dropped a ajax script-manager on the page.
- dropped an ajax update-panel onto the page.
- dropped a table into the update-panel.
- dropped a DetailsView control into the table (DetailsView1).
- selected an object-datasource (ObjectDataSource1).
- selected my TableAdapter ds_Orders_3TableAdapters.Orders_TableTableAdapter(Int32 company),returns Object.
- clicked next and clicked the "Insert" tab and selected my second query in the TableAdapter (InsertCompanyReturnOrderID(Int32 company), returns Int32. (completing the datasource wizard)
- selected "enable-inserting". save.
- "Run in Browser"
- click "New" linkButton (to insert a new record)
- type "3" for some random (companyID) data
- click the "Insert" linkbutton.
The record inserts into the database fine BUT after the postback the OrderID value is always a value of "1" instead of the ID of the my "just" inserted record.
Tuesday, June 10, 2008 2:11 AM
hi,
You can use the Scope_Identity function to return the last inserted row number.
cheers mate
Tuesday, June 10, 2008 9:09 AM
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.
Tuesday, June 10, 2008 12:11 PM
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:
**
|
Source File: c:\Documents and Settings\..\test_4.aspx.cs Line: 23
what do you think?
Tuesday, June 10, 2008 12:52 PM
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.
Tuesday, June 10, 2008 1:09 PM
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?
Tuesday, June 10, 2008 1:37 PM
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.
Tuesday, June 10, 2008 9:33 PM
No need to worry about your response time, I appreciate the concern. I've been working in IT/IS administration for about a year and a half and about 2 months in asp.net and never dreamed that their were people donating their time the way everybody does in these forums... I'm impressed! I made my first post (the first post in my life) about 2 weeks ago, looking for help in building an advanced trigger for my db on the msdn forum and to my shock... somebody answered me within minutes :O seriously... my jaw hit the floor.. I say all that to say THANKS!