NodeJS - ID of the last created user returning Null

Hi,

Wappler v4.4.5, NodeJS, MSSQL

When creating a new user, I’m inserting record for the user_id, user_login and user_password in the User table. user_id in the database has been setup as AutoIncrement Identity field.

In the next step, I’m inserting a new record in another table (UserData table) for the same user_id. To do this I’m binding identity from the previous step via dynamic picker. But the identity field is returning Null value and the second insert record step is failing with an error 500.

These steps had been working okay. I tried creating a new user today and came across this issue. I’m not sure whether this is a bug in the latest version or I’m missing a step to get the last created ID correctly.

Any help in resolving this issue is much appreciated.

Do you know since when it stopped working and what was updated in that time. I checked the insert action and that didn’t have any updates to it’s code for a while.

Hi Patrick,

Thanks for looking in to this issue.

I originally developed the project in .NET and then converted it to NodeJS, finishing the conversion about 2-3 weeks ago. I believe this was the first time I had tried to create a new user in the NodeJS version. This is the only SA where I’m binding identity from an insert step; so I didn’t come across this issue when testing other SAs in the NodeJS framework.

Edit: I have done some more testing by adding a Set Value step for the last added ID and identity is returning a blank value.

Do you have triggers on the users table?

There are no triggers on the users table. I have a foreign key setup for user_id in the users and user_data tables.

NodeJS uses the knex package to build queries, the documentation mentions some problems with MSSQL when triggers are on the table.

Knex.js - A SQL Query Builder for Javascript (knexjs.org)

How does the action file json look like? You can try adding the returning property to the insert action options, like "returning": "user_id". Must be on the sql object.

1 Like

The insert action is returning identity correctly after I added "returning": "user_id" in the sql object.

Would this option need to be always added manually in the JSON code to return an identity? Or is there a possibility for adding this option in the Wappler code for MSSQL databases?

Many thanks for working through this issue with me and providing a solution. Your help and support is always greatly appreciated.

I will check it, I know it is automatically added for PostgreSQL, didn’t know it was also required for MSSQL.

Even though Knex is referring to an issue with Triggers, it seems it’s an issue with Foreign Keys as well when returning values. It would be great if this code can be added automatically to MSSQL queries as well to address this issue.

Thanks again for your help, Patrick.