node-mssql insert returning undefined recordset

Question

Select statements are working fine, but whenever I try an insert or update the recordset and affected values are undefined. The insert/update works in the DB, I just can't read the returned values.

var sql = require('mssql');
var config = {...};

sql.connect(config).then(function() {
  new sql.Request().query("INSERT INTO MyTable (Name, Age) VALUES ('John', 30)").then(function(recordset, affected) {
    console.log('Recordset: ' + recordset);
    console.log('Affected: ' + affected);
  }).catch(function(err) {
    console.log('Request error: ' + err);
  });
}).catch(function(err) {
  if (err) {
    console.log('SQL Connection Error: ' + err);
  }
});

The output to console is:

Recordset: undefined
Affected: undefined

I feel like I must be missing something really simple here.

I know nothing of js, but: an INSERT statement does not return a recordset. Maybe that sheds some light on your issue? — TT.
– TT., Commented Jan 5, 2016 at 16:22
Maybe you need to add the OUTPUT clause to your INSERT command. — Tab Alleman
– Tab Alleman, Commented Jan 5, 2016 at 16:26

Patrik Simek · Accepted Answer · 2016-01-05 19:20:10Z

3

As mentioned in the comments, INSERT statement doesn't return a recordset so recordset is undefined. Please see this section of the docs to learn more about how to get number of affected rows.

The problem with your code is you're expecting affected as a second argument from the promise, but promises does only support one argument. Because of that you must access number of affected rows this way:

var sql = require('mssql');
var config = {...};

sql.connect(config).then(function() {
  var request = new sql.Request();
  request.query("INSERT INTO MyTable (Name, Age) VALUES ('John', 30)").then(function(recordset) {
    console.log('Recordset: ' + recordset);
    console.log('Affected: ' + request.rowsAffected);
  }).catch(function(err) {
    console.log('Request error: ' + err);
  });
}).catch(function(err) {
  if (err) {
    console.log('SQL Connection Error: ' + err);
  }
});

answered Jan 5, 2016 at 19:20

Patrik Simek

1,04810 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

solydest Over a year ago

Thanks for your reply! That makes sense, but I guess what I'm ultimately after is the value of the ID column from the newly inserted row. What's the best way to get that?

Patrik Simek Over a year ago

Change your TSQL command to: INSERT INTO MyTable (Name, Age) VALUES ('John', 30);SELECT @@IDENTITY AS ID

Stephen Last Over a year ago

Possibly better to use SCOPE_IDENTITY() rather than @@IDENTITY. Would @@IDENTITY run into race conditions if there were many INSERT INTO's happening at the same time..?

Saurabh Singh · Accepted Answer · 2020-11-28 19:08:32Z

1

If you want id to be output parameter

const sql = require("mssql/msnodesqlv8");
const pool = new sql.ConnectionPool(dbConfig);`
const poolConnect = pool.connect();    
let query = `INSERT INTO <table>(fields) VALUES(values);SELECT @id = SCOPE_IDENTITY()`
await poolConnect;
pool.request()
    .output("id", sql.Int)
    .query(query).then((err, result) => {
        console.log(result.output.id)
    }).catch(err => {
        console.log(err)
    })`

answered Nov 28, 2020 at 19:08

Saurabh Singh

637 bronze badges

Collectives™ on Stack Overflow

node-mssql insert returning undefined recordset

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related