Passing Parameters on SqlCommand Does not work

Question

I am trying to run a single command to insert rows into my SQL Server 2012 database, however my parameters are not passed to the code.

con.Open();

string query = @"INSERT INTO [dbo].[Users](Username, Password, Role) 
                 VALUES (@u, @p, @r);";
var cmd = new SqlCommand(query, con);

cmd.Parameters.AddWithValue("@u", username);
cmd.Parameters.AddWithValue("@p", password);
cmd.Parameters.AddWithValue("@r", role);

cmd.ExecuteNonQuery();

con.Close();

I get SqlException with the parameterized query

'(@u nvarchar(4000),@p nvarchar(4000),@r nvarchar(1))INSERT INTO ' expects the parameter '@u', which was not supplied.

The way I see it I am passing the parameters before executing the command. Can anyone help me to find out what is missing ?

Answer 1

Your issue is not because of your query, it is because the variable username is NULL. When you variable username is null, it will not throw any error when you use .AddwithValue, however, it is not setting the parameter, that's why when you use that parameter, it returned you exception says the parameter is not supplied.
Use the following code, you can reproduce your issue and verify the reason. If you give username =string.Empty, it will not give you this issue.

string connStr = "Data Source=localhost;Initial Catalog=Adventure;Integrated Security=SSPI";
            SqlConnection con = new SqlConnection(connStr);
            string username = null;
            string password = string.Empty;
            string role = string.Empty;
            try
            {
                con.Open();
                string query = @"INSERT INTO [dbo].[Users]" + @"(Username ,Password , Role) " + @"VALUES (@u,@p,@r);";
                var cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@u", username);
                cmd.Parameters.AddWithValue("@p", password);
                cmd.Parameters.AddWithValue("@r", role);
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace + ex.Message);
                con.Close();
            }

Answer 2

Add : Cmd.CommandType = CommandType.StoredProcedure; OR, Cmd.CommandType = CommandType.Text; as per requeired