Insert '20-001' into SQL Server database results in an error

-1

I found an error while developing in .NET. I face a problem using a stored procedure. I set variable @A like this:

@A NVARCHAR(50) = ""

After .NET database connect and send to '20-001'

cmd.Parameters.AddWithValue("@a", test.a);

But I get an error

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '22 001' to data type int.

My code

[HttpPost]
public JsonResult test([FromBody]test test)
{
    int result = 0;

    if (ModelState.IsValid)
    {
         SqlConnection conn = new SqlConnection(source());
         conn.Open();

         SqlCommand cmd = new SqlCommand("[test].[test]", conn);
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("@FLAG", test.test);
         cmd.Parameters.AddWithValue("@REQTYPE", "test_INSERT");
         cmd.Parameters.AddWithValue("@test", test.a);

         using(SqlDataReader reader = cmd.ExecuteReader())
         {
             while(reader.Read())
             { 
                 result = (int)reader[0];
             }

             reader.Close();
         }
    }

    if (result != 0)
    {
        return Json(new { success = false });
    }

    return Json(new { success = true });
}

Stored procedure:

@a NVARCHAR(50) = ''
INSERT INTO test(a) VALUES (@a)
.net
sql-server
asked on Stack Overflow Aug 25, 2020 by KIM HYUNG JUN • edited Aug 25, 2020 by marc_s

1 Answer

0

The error

Conversion failed when converting the nvarchar value '22 001' to data type int

is because of

  1. type mismatch between stored procedure parameter and value passed in, or
  2. type mismatch between value passed into insert statement and table datatype

Comments:

  1. cmd.Parameters.AddWithValue accepts all data types, then your code is OK here

  2. In your stored procedure, you must define input parameter @test as nvarchar(50)

  3. In the destination table test, the column a must be nvarchar(50) data type

Like this

CREATE TABLE test_table 
(
     a nvarchar(50)
)
GO

ALTER PROCEDURE [test].[test] 
    (@FLAG nvarchar(200),
     @REQTYPE nvarchar(200),
     @test nvarchar(50))
AS
    INSERT INTO test_table (a) 
    VALUES (@test)
GO
answered on Stack Overflow Aug 25, 2020 by Javad Ghasemi • edited Aug 25, 2020 by marc_s

User contributions licensed under CC BY-SA 3.0