Parameter missing at ExecuteNonQuery with stored procedure in ASP.NET MVC

Question

Below is my code and I get an error at ExecuteNonQuery:

@Name parameter missing.

I have tried many time and even no error during building of program. The stored procedure contains an insert statement with 4 parameters, 3 of varchar type and one integer type as the primary key.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace CompanyMaster
{
    public class Master
    {
         public IEnumerable<Company> Companies
         {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

                List<Company> companies = new List<Company>();

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spGetAllCompany", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();

                    SqlDataReader rdr = cmd.ExecuteReader();

                    while (rdr.Read())
                    {
                        Company company = new Company();
                        company.CompanyCode = Convert.ToInt32(rdr["CompanyCode"]);
                        company.CompanyName = rdr["CompanyName"].ToString();
                        company.CompanyAddress = rdr["CompanyAddress"].ToString();
                        company.CompanyMail = rdr["CompanyMail"].ToString();

                        companies.Add(company);
                    }
                }
                return companies;
            }
       }

       public void Addcompany(Company company)
       {    
           string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

           using (SqlConnection con = new SqlConnection(connectionString))
           {
               SqlCommand cmd = new SqlCommand("spAddCompany", con);
               cmd.CommandType = CommandType.StoredProcedure;

               cmd.Parameters.Clear();

               SqlParameter paramCode = new SqlParameter();
               paramCode.ParameterName = "@Code";
               paramCode.Value = company.CompanyCode;
               cmd.Parameters.Add(paramCode);

               SqlParameter PName = new SqlParameter("@Name", SqlDbType.VarChar, 50);
               //PName.ParameterName = "@Name";
               PName.Value = company.CompanyName;
               cmd.Parameters.Add(PName);

               SqlParameter paramAddress = new SqlParameter();
               paramAddress.ParameterName = "@Address";
               paramAddress.Value = company.CompanyAddress;
               cmd.Parameters.Add(paramAddress);

               SqlParameter paramMail = new SqlParameter();
               paramMail.ParameterName = "@Mail";
               paramMail.Value = company.CompanyMail;
               cmd.Parameters.Add(paramMail);

               con.Open();
               cmd.ExecuteNonQuery();-- error is occurring here
           }
       }
    }
}

Here is my stored procedure:

CREATE PROCEDURE spAddCompany
    @Code INT,
    @Name NVARCHAR(50),
    @Address NVARCHAR(60),
    @Mail NVARCHAR(50)
AS
BEGIN
    INSERT INTO CompanyMaster (CompanyCode, CompanyName, CompanyAddress, CompanyMail)
    VALUES (@Code, @Name, @Address, @Mail)
END

@Name parameter is missing when the code reaches ExecuteNonQuery.

Side note: The type of the `@Name` parameter in the stored procedure is `NVARCHAR`, but in your C# code it is `SqlDbType.VarChar`. You should change it to `SqlDbType.NVarChar`. You should use the same style for all the other parameters as well. — Racil Hilan, May 17 '18 at 17:53

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

I think your problem has to do with null vs DBNull.Value.
Check if company.CompanyName is null (in c#). If it is, you should pass DBNull.Value instead.
For more information on the difference between the two, read What is the difference between null and System.DBNull.Value?

From Configuring Parameters and Parameter Data Types:

Note

When you send a null parameter value to the server, you must specify DBNull, not null (Nothing in Visual Basic). The null value in the system is an empty object that has no value. DBNull is used to represent null values. For more information about database nulls, see Handling Null Values.

Also, You can add parameters to the command and set their values in a single line of code, like this:

cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = company.CompanyName;

This will make your code much shorter and more readable.

Here are the changes I've made to your code that I think should solve your problem:

public void Addcompany(Company company)
{    
    string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

    using (var con = new SqlConnection(connectionString))
    {
        // SqlCommand also implements the IDisposable interface
        using(var cmd = new SqlCommand("spAddCompany", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Code",  SqlDbType.Int).Value = company.CompanyCode;
            cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = company.CompanyName as object ?? (object)DBNull.Value;
            cmd.Parameters.Add("@Address", SqlDbType.VarChar, 50).Value = company.CompanyAddress as object ?? (object)DBNull.Value;
            cmd.Parameters.Add("@Mail", SqlDbType.VarChar, 50).Value = company.CompanyMail as object ?? (object)DBNull.Value;
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Note the use of the null coalescing operator (??) and the casting to object.

Parameter missing at ExecuteNonQuery with stored procedure in ASP.NET MVC

1 Answers1