Save empty string in table

Question

I got a website with a form full of settings where each of the inputs save a value in a MS SQL Database.

But when i set a field to '' i got the following error message: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0x00 is unknown."

My node.js service calls a stored procedure which looks like this:

CREATE PROCEDURE [dbo].[ckd_Configurations_Update]
@Key NVARCHAR(256),
@Value NVARCHAR(MAX)
AS
BEGIN
  UPDATE [dbo].[ckd_Configurations] SET [Value]=@Value WHERE [Key]=@Key;
END

My table:

CREATE TABLE [dbo].[ckd_Configurations]
(
    [Key] NVARCHAR(256) NOT NULL PRIMARY KEY,
    [Value] NVARCHAR(MAX)
)

And finally my node.js route to set the configurations:

router.post('/configurations/update', function (req, res) {
    var request = new sql.Request(databaseConnection);
    var configuration = req.body;
    request.input('Key', sql.NVarChar(256), configuration.Key);
    request.input('Value', sql.NVarChar(sql.MAX), configuration.Value);

    request.execute('ckd_Configurations_Update', function (error, recordsets) {
        if (error) {
            res.json(new apiResponse(false, error.message));
        } else {
            res.json(new apiResponse(true, '', recordsets[0]));
        }
    });
});

Try changing `NVarchar(MAX)` to some fixed length like 'NVarchar(4000)' and check if it works. — Nilesh, Feb 18 '15 at 09:40
@Nilesh it didn't worked. getting the same error. i don't understand why it says parameter 3... — Michael Malura, Feb 18 '15 at 09:56
Are you able to execute the stored proc directly with these values? from management studio? — Nilesh, Feb 18 '15 at 10:02
@Nilesh yes it works without problems: ckd_Configurations_Update 'ReceiptFooter', '' (1 row(s) affected) — Michael Malura, Feb 18 '15 at 10:10

score 1 · Accepted Answer · answered Feb 18 '15 at 22:55

1

Just update mssql to 2.0. The issue was fixed in this release.

answered Feb 18 '15 at 22:55

Patrik Šimek

1,038
10
14

What is mssql 2.0? Any document about this issue and how was it fixed in version "2.0"? – Anh Hoang Nov 09 '21 at 07:08

Sorrel Vesper · Answer 2 · 2015-02-18T07:59:17.833

0

If I understand your question correctly, you want to be able to insert empty string in the database?

ALLOW 'NULL' in Table against Value column. So that null value can be set to it. In the stored procedure, put a condition :

IF @Value = ''
BEGIN
    SET @Value = NULL
END

When you want to return the value as empty string, you can use this in the SELECT statement.

Select isnull(Value, '') as Value from [ckd_Configurations]

Set a dummy value to the field as '-1' and then check that in your stored procedure and set it to NULL. I think the problem here is that your service is not accepting the empty string which is causing the problem. In the stored procedure, you can also set the

@Value NVARCHAR(MAX) = NULL

edited Feb 18 '15 at 07:59

answered Feb 18 '15 at 07:50

Sorrel Vesper

414
4
18

Some of these settings are optional. So i want to 'clear' them. To do this i delete everything in the input field. When i press 'save' i get the error message from above. That's what i mean with 'insert empty string'. – Michael Malura Feb 18 '15 at 07:52
I see. Your problem is when you're sending the input to the database, then you get this error? Let me edit my answer! – Sorrel Vesper Feb 18 '15 at 07:54

Save empty string in table

2 Answers2