I am trying to complete a seemingly simple task that has turned into a several hour adventure: Getting @@Identity
from TableAdapter.Insert()
.
Here's my code:
protected void submitBtn_Click(object sender, EventArgs e)
{
AssetsDataSetTableAdapters.SitesTableAdapter sta = new AssetsDataSetTableAdapters.SitesTableAdapter();
int insertedID = sta.Insert(siteTxt.Text,descTxt.Text);
AssetsDataSetTableAdapters.NotesTableAdapter nta = new AssetsDataSetTableAdapters.NotesTableAdapter();
nta.Insert(notesTxt.Text, insertedID, null,null,null,null,null,null);
Response.Redirect("~/Default.aspx");
}
One answer suggests all I may have to do is change the ExecuteMode
. I tried that. This makes GetData()
quit working (because I'm returning a scalar now instead of rowdata) (I need to keep GetData()). It also does not solve the issue in that the insertedID variable is still set to 1.
I tried creating a second TableAdapter
in the TypedDataSet.XSD
and setting the property for that adapter to "scalar", but it still fails with the variable getting a value of 1.
The generated insert command is
INSERT INTO [dbo].[Sites] ([Name], [Description]) VALUES (@Name, @Description);
SELECT Id, Name, Description FROM Sites WHERE (Id = SCOPE_IDENTITY())
And the "Refresh the Data Table" (adds a select statement after Insert and Update statements to retrieve Identity" is also set.
Environment
SQL Server 2008 R2, Visual Studio 2010, .NET 4, Windows XP, all local same machine.
What's causing this?
EDIT/UPDATE
I want to clarify that I am using auto-generated code within Visual Studio. I don't know what the "tool" that generated the code is, but if you double click the *.XSD file it displays a UI of the SQL Table Schema's and associated TableAdapter's. I want to keep using the auto-generated code and somehow enable getting the Identity. I don't want to write this all by hand with stored procedures.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…