Getting @@Identity from Tableadapter

Getting @@IDENTITY from TableAdapter

Here's my SQL Code that works.

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()

Then when I create the Table Adapter - I can instantly see the @InsertedID parameter.

Then from code, all I do is:

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

I'm not 100% whether using ref is the best option but this works for me.

Good luck.

MS Access TableAdapter Get Identity after insert

Have to use OleDB Commands:

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

Incorrect ID is returned after insert using TableAdapter

set the execute mode property to Scalar, then you get the ID, otherwise the rows-affected. You set the property properties window of the query not in the Query wizard.

(fig 28)

How to retrieve SQL identity using a TableAdapter Insert Command?

Ok I got this working.

1. Add a new parameter to my insert stored procedure: @job_order as int OUTPUT
2. In the stored procedure, after the insert statement: SET @job_order = SCOPE_IDENTITY
3. In the vb.net project edit your dataset, view the properties of the TableAdapter and ensure GenerateDBDirectMethods is set to True
4. Also in the TableAdapter properties click the dropdown on the InsertCommand and select New.
5. Change the CommandType to StoredProcedure
6. Click the dropdown for CommandText and select your stored procedure
7. In VS 2017 there is an issue where the Parameter list won't populate from the stored procedure. To fix this add dbo. before your procedure name in the CommandText field. e.g. change apAddJob to dbo.apAddJob. Once that change is complete the parameter list should populate.
8. Open the Parameters collection for the InsertCommand (which should now have values) and verify that the new parameter you added in step one not only shows in the list but has a Direction of InputOutput.
9. In the codepage for the relevent form make yourself a module level variable (at the top and not inside any methods) as follows: Dim m_NewJobOrder As Integer
10. Perform the insert using the Insert command for the table adapter adding your new module level variable to the end as follows:
    Me.ApGetSingleJobTableAdapter.Insert(txtbox1.text, txtbox2.text, (all other values), m_NewJobOrder)
11. The identity value should now be stored in m_NewJobOrder after insert. Normally I use it to refill the dataset with the newly created record.

Get id when inserting new row using TableAdapter.Update on a file based database

Try this http://support.microsoft.com/kb/815629 , the sample code is in VB.NET though.

Or if multiline query is accepted in MS Access and it has built-in function/variable for retrieving the last id, use this (the database is SQLite though): anyway see why I get this "Concurrency Violation" in these few lines of code??? Concurrency violation: the UpdateCommand affected 0 of the expected 1 records , try to google for the function

[EDIT: Works on my Machine, I don't have SQL Server Compact, but I didn't use multi-statement]

public Form1()
{
    InitializeComponent();

    var c = Connect();

    var da = new SqlDataAdapter("select emp_id, emp_firstname, emp_lastname from emp where 1 = 0", c);

    var b = new SqlCommandBuilder(da);

    var getIdentity = new SqlCommand("SELECT CAST(@@IDENTITY AS INT)", c);

    da.InsertCommand = b.GetInsertCommand();
    da.UpdateCommand = b.GetUpdateCommand();
    da.DeleteCommand = b.GetDeleteCommand();
    da.RowUpdated += (xsender, xe) =>
    {
        if (xe.Status == UpdateStatus.Continue && xe.StatementType == StatementType.Insert)
        {
            xe.Row["emp_id"] = (int)getIdentity.ExecuteScalar();
        }
    };

    var dt = new DataTable();
    da.Fill(dt);

    var nr = dt.NewRow();
    nr["emp_firstname"] = "john";
    nr["emp_lastname"] = "lennon";

    var nrx = dt.NewRow();
    nrx["emp_firstname"] = "paul";
    nrx["emp_lastname"] = "mccartney";

    dt.Rows.Add(nr);
    dt.Rows.Add(nrx);

    da.Update(dt);

    dt.AcceptChanges();

    nrx["emp_lastname"] = "simon";
    da.Update(dt);

    nr["emp_lastname"] = "valjean";
    da.Update(dt);

}

SqlConnection Connect()
{
    return new SqlConnection(@"data source=.\SQLEXPRESS;Database=Test;uid=sa;pwd=hey");
}

ASP.NET tableadapter insert not returning the correct identity value

ExecuteNonQuery returns the number of rows affected. You will want to use ExecuteScalar() instead.

---

Related Topics