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 ?

ljh · Accepted Answer · 2013-03-30 07:19:20Z

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();
            }

Sanjay Chitrakar · Accepted Answer · 2018-11-13 10:12:24Z

1

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

answered Nov 13, 2018 at 10:12

Sanjay Chitrakar

111 bronze badge

2 Comments

Marc Gravell Over a year ago

this is not required; it defaults to CommandType.Text, and this clearly is a CommandType.Text scenario. So: nothing is needed here.

Sanjay Chitrakar Over a year ago

Actually i was using stored procedure and i missed that line because of which i was continuously getting of "parameter expected". Well you are right, in this case its not needed.

Collectives™ on Stack Overflow

Passing Parameters on SqlCommand Does not work

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related