Node-mssql: Problem with getting last inserted ID

[ocpuso]

Anybody have issues with retrieving the last inserted ID from db after an insert statement?

Assume a table student contains 3 fields (id, age, name).
Here's my code:

        var query = "INSERT INTO student(age, tagname) values (6, 'Bob') ";
        db = new sql.Request();
        db.query(query, function(err, data) {
        if (!err)
        {
            var str = "SELECT @@IDENTITY AS 'identity'";    
            db = new sql.Request();
            db.query(str, function(suberr, subdata)
            {
                console.log(" this is LAST inserted id : ");
                console.log(subdata);   // -> RETURNED NOTHING
            });
        }

The above code returned nothing as the ID eventhough the student record was captured correctly into the db.

OUTPUT

 this is last LAST inserted id :
[ { identity: null } ]

I am wondering if there's something wrong with my code, or if this is a bug.
Any feedbacks are appreciated.

PS. If I do another query in between the INSERT and SELECT @@IDENTITY, be it a select query, or insert query, then it would give me the ID that I am looking for.

[ocpuso]

Is anyone else having the same problem? Or is this not a bug?
Any response is appreciated.

Cheers.

[Dalorzo]

I would like to recommend POST these type of questions in StackOverflow, there is tag for this project:
http://stackoverflow.com/questions/tagged/node-mssql

I did this I created this table:

CREATE TABLE demoTable(
    id INT IDENTITY(1,1) NOT NULL,
    someValue   VARCHAR(20) NOT NULL,
)

Then I created this demo code and it worked fine:

var connection = new self.sql.Connection(DBConfig, function (err) {
        console.log("Demo Query");
        var query = "INSERT INTO demoTable(someValue) values ('someValue') ";
        var request = new sql.Request(connection);
        request.query(query, function(err, data) {
            console.log("Request Query 1 - Insert");
            if (!err)
            {
                var str = "SELECT @@IDENTITY AS 'identity'";
                request = new sql.Request(connection);
                request.query(str, function(suberr, subdata)
                {
                    console.log("Querying @@Indentity : ");
                    console.log(subdata);   // -> RETURNED NOTHING
                });
            }
            else{
                console.log(err);
            }
        });
    });

[patriksimek]

Closing due to inactivity.

[Loksly]

Just for the record, as you cannot warranty you are using the same connection of the pool, for both the insert and the select statement, you may choose to use a PreparedStatement or simply use the same query for both of them. Use it like this:

var query = "INSERT INTO demoTable(someValue) values ('someValue')";
query = query + ';select @@IDENTITY AS \'identity\''; // ---- this is what worked

request.query(str, function(suberr, subdata){
    console.log("Querying @@IDENTITY : ");
    console.log(subdata[0].identity);   // -> RETURNED VALUE
});

[raztor]

Hi guys,

I know this issue is closed but still decided to ask. I currently need the same thing as the author of the thread. I need to insert a row and as a result get back its id.

Now, I see that the answers here would do the job but is it really true that this functionality is not part of the mssql library? I mean I kind of expected when I insert a row to get back what's been inserted in the result along with any fields that were set upon inserting (like id).

@ocpuso , you probably did research before posting this so what do you think?

[itdpong]

I am having the same question. there are no ways to return the last insert id
nomatter the select statement is execute in the same connection with insert query, it always return nothing. So how can your guys get the last insert id????

        var dbConn = new sql.Connection(dbIntra,function (err) {
            var tran = new sql.Transaction(dbConn);
            tran.begin(function (error) {
                var rollBack = false;
                tran.on('rollback',function (aborted) {
                    rollBack = true;
                });
                var query="INSERT INTO [dbo].[voices] ([userid],[type],[number],[recording_date],[remark],[create_time]) VALUES (" + req.user.user_id + ", '" + req.body.type + "','" + req.body.number+ "',convert(datetime, '" + req.body.recording_date + " 00:00:00', 120) ,'" + req.body.remark + "', GETDATE());";
                //query+="SELECT SCOPE_IDENTITY() AS id;";
                new sql.Request(tran).query(query,function (err, recordset) {
                    //console.log(recordset);
                    if (err) {
                        if (!rollBack) {
                            tran.rollback(function (err) {
                                res.json({"success": false, "msg":"Rollback:" + err});
                            });
                        }
                    } else {
                        var str = "SELECT SCOPE_IDENTITY() AS id";
                        request = new sql.Request(dbConn);
                        request.query(str, function(suberr, subdata){
                            console.log(subdata);   // -> RETURNED NOTHING
                        });

                        tran.commit().then(function (recordset) {
                            console.log(recordset);
                            res.json({"success": true, "msg":"Data is inserted successfully!"});
                        }).catch(function (err) {
                            res.json({"success": false, "msg":'Error in transaction commit ' + err});
                        });
                    }
                });
            });
        });

[amdp]

https://github.com/sequelize/sequelize/issues/5499#issuecomment-480528090

[dhensby]

This was answered 3 years ago https://github.com/tediousjs/node-mssql/issues/32#issuecomment-223278558