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
– Nilesh, Commented Feb 18, 2015 at 9:40
@Nilesh it didn't worked. getting the same error. i don't understand why it says parameter 3... — Michael Malura
– Michael Malura, Commented Feb 18, 2015 at 9:56
Are you able to execute the stored proc directly with these values? from management studio? — Nilesh
– Nilesh, Commented Feb 18, 2015 at 10:02
@Nilesh yes it works without problems: ckd_Configurations_Update 'ReceiptFooter', '' (1 row(s) affected) — Michael Malura
– Michael Malura, Commented Feb 18, 2015 at 10:10

Patrik Simek · Accepted Answer · 2015-02-18 22:55:24Z

1

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

answered Feb 18, 2015 at 22:55

Patrik Simek

1,04810 silver badges14 bronze badges

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

1 Comment

Anh Hoang Over a year ago

What is mssql 2.0? Any document about this issue and how was it fixed in version "2.0"?

Sorrel Vesper · Accepted Answer · 2015-02-18 07:59:17Z

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, 2015 at 7:59

answered Feb 18, 2015 at 7:50

Sorrel Vesper

4144 silver badges18 bronze badges

2 Comments

Michael Malura Over a year ago

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'.

Sorrel Vesper Over a year ago

I see. Your problem is when you're sending the input to the database, then you get this error? Let me edit my answer!

Collectives™ on Stack Overflow

Save empty string in table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related