'Conversion failed when converting the nvarchar value to data type int sql server

I have a table. There is data in this table and there is a checkbox next to each data. Multiple selection is possible. After the user makes a selection, the id numbers of the selected columns are come in the array. I convert the array to string and send it to the stored procedure and I run the following stored procedure:

Example value for @ResultsIds: 65, 66, 67, 68, 125

@ResultsIds nvarchar(250)

UPDATE MyTable SET [IsVerified] = 1 WHERE Id IN (@ResultsIds)

And I got this error:

Conversion failed when converting the nvarchar value '65, 66, 67, 68, 125' to data type int. Because [Id] column is int data type.

I tried CAST and CONVERT functions of SQL but it didn't work.

sqlasp.net-mvcasp.net-web-apidevextreme


Solution 1:[1]

SQL Server doesn't do that automatically. Assuming you're on a recent version, you can do this:

declare @ResultsIds nvarchar(250) = '65,66,67,68,125'

UPDATE MyTable
SET [IsVerified] = 1
WHERE Id IN (
   select [value] 
   from string_split(@ResultIDs, ',')
)

Solution 2:[2]

declare @ResultsIds nvarchar(250)='65,66,67,68,125'
UPDATE MyTable SET [IsVerified] = 1 WHERE cast(Id as varchar) IN (@ResultsIds)

Solution 3:[3]

I solved problem using foreach. I separated the numbers in the string from commas and transferred each number to the array. Then I updated the array one by one by running foreach loop.

public void Verify(DB db, string rows)
    {

        int[] nums = Array.ConvertAll(rows.Split(','), int.Parse);

        foreach (int value in nums)
        {
            DbCommand cmd = db.GetStoredProcCommand("VerifyProcess");
            db.AddInParameter(cmd, "@ResultId", DbType.Int32, value);
            db.ExecuteNonQuery(cmd);
        }

    }

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ben Thul
Solution 2
Solution 3 Mert POLAT

Related Questions