Getting @@ IDENTITY from TableAdapter

Question

I am trying to complete a seemingly simple task that has turned into a few hours of adventure: Getting @@Identityfrom TableAdapter.Insert().

Here is 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 need to do is change ExecuteMode. I have tried this. This causes it to GetData()stop working (because now I am returning the scalar instead of rowdata) (I need to save GetData ()). It also does not solve the problem that the insertID variable is still set to 1.

I tried to create a second one TableAdapterin TypedDataSet.XSDand set the property for this adapter to "scalar", but it still does not work with a variable that gets a value of 1.

Generated Insert Command

INSERT INTO [dbo].[Sites] ([Name], [Description]) VALUES (@Name, @Description);
SELECT Id, Name, Description FROM Sites WHERE (Id = SCOPE_IDENTITY())

" " ( Insert Update Identity).

SQL Server 2008 R2, Visual Studio 2010,.NET 4, Windows XP, .

?

/

, Visual Studio. , "", , *.XSD, SQL . - Identity. .

Answer 1

SQL, .

CREATE PROCEDURE [dbo].[Branch_Insert]
(
    @UserId uniqueidentifier,
    @OrganisationId int,
    @InsertedID int OUTPUT
)
AS
    SET NOCOUNT OFF;
INSERT INTO [Branch] ([UserId], [OrganisationId]) 
VALUES (@UserId, @OrganisationId);

SELECT Id, UserId, OrganisationId FROM Branch WHERE (Id = SCOPE_IDENTITY())
SELECT @InsertedID = SCOPE_IDENTITY()

, Table Adapter - @InsertedID.

, , :

int? insertedId = 0;
branchTA.Insert(userId, orgId, ref insertedId);

100%, ref, .

.

Answer 2

:

• !

insertadapter

.

, : int , Identity, PK . :

( InsertQuery) SELECT SCOPE_IDENTITY() , ExecuteMode NonQuery Scalar (ta - TableAdapter ):

int id;

try
{
 id = Convert.toInt32(ta.InsertQuery(firstName, lastName, description));
}
catch (SQLException ex)
{
//...
}
finally
{
//...
}

!:) 12 2009 Draško Sarić

: http://quickdeveloperstips.blogspot.nl/2009/03/get-identity-from-tableadapter-insert.html

:

• ExecutMode Scalar . ( F4).

• (Visual Studio 2010 SP1) select .

Answer 3

, , , .

SELECT SCOPE_IDENTITY() , :

INSERT INTO foo(bar) VALUES(@bar);
SELECT SCOPE_IDENTITY()

, ; INSERT, VS .

, , Scalar .

, Convert.ToInt32(), , :

id = Convert.ToInt32( dataTableAdapter.myInsertQuery("bar") )

Convert.ToInt32, .

, , , reset Scalar, VS Non Query.

Answer 4

( )

• " "
• SQL- - Update ( )

• SQL, , , " " , - "" ( , "" ).

  UPDATE YOURTABLE
  SET  YourTable_Column1 = @YourTable_Column1, YourTable_Column2 = @YourTableColumn2
  WHERE (YourTable_ID = @YourTable_ID)
  IF @@ROWCOUNT=0
    INSERT INTO YOURTABLE ([YourTable_Column1], [YourTable_Column2])
    VALUES (@YourTable_Column1, @YourTable_Column2)
  @YourTable_ID = SCOPE_IDENTITY()
  

• / @YourTable_ID / . ID InputOutput, Table Adapter. ( . , , InputOutput, " " , datatable )

.

Wow. , Data Layer SQL . , ...

Answer 5

, , .

:

http://www.akadia.com/services/dotnet_autoincrement.html

http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx

Answer 6

:

• SQL
• , Visual Studio

SQL ExecuteScalar.

INSERT INTO [dbo].[Sites] ([Name], [Description])
OUTPUT INSERTED.ID
VALUES (@Name, @Description);

Answer 7

- . , :

    public int WrapInsert(Parameters)
    {
        .....
        int RowsAffected = this.Insert(..Parameters..);
        if ( RowsAffected > 0)
        {
            try
            {
                SqlCommand cm = this.Connection.CreateCommand();
                cm.CommandText = "SELECT @@IDENTITY";
                identity = Convert.ToInt32(cm.ExecuteScalar());
            }
            finally
            {
                ....
            }
        }
        return RowsAffected;
    }