The parameterized query expects'@firstname nvarchar, @lastname nvarchar, @phone nvarchar expects the parameter @email, which was not supplied

Question

Are there any gurus out there that might be able to help me figure this out? I keep throwing this exception in C#, but everything that I can see seems to be correct. Here is the code

  public bool Insert(loginBLL u)
    {
        bool isSuccess = false;
        SqlConnection conn = new SqlConnection(myconnstrng);

        try
        {
            String sql = "INSERT INTO customers (firstname, lastname, phone, email, address, city, state, zipcode, password, username) VALUES (@firstname, @lastname, @phone, @email, @address, @city, @state, @zipcode, @password, @username)";
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@firstname", u.firstname);
            cmd.Parameters.AddWithValue("@lastname", u.lastname);
            cmd.Parameters.AddWithValue("@phone", u.phone);
            cmd.Parameters.AddWithValue("@email", u.email);
            cmd.Parameters.AddWithValue("@address", u.address);
            cmd.Parameters.AddWithValue("@city", u.city);
            cmd.Parameters.AddWithValue("@state", u.state);
            cmd.Parameters.AddWithValue("@zipcode", u.zipcode);
            cmd.Parameters.AddWithValue("@password", u.password);
            cmd.Parameters.AddWithValue("@username", u.username);

         
            conn.Open();

            int rows = cmd.ExecuteNonQuery();

I would recommend avoiding `AddWithValue()` and instead adding the parameters with explicit types. — Gordon Linoff, Jul 20 '20 at 01:05
@GordonLinoff so instead use cmd.Parameters.Add("@firstname", SqlDbType,varchar); — Joe W., Jul 20 '20 at 01:18
And null values? `.AddWithValue("xxx", u.xxx ?? DBNull.Value);` — Jeremy Lakeman, Jul 20 '20 at 01:27
Check for all the parameters, you are passing null to a non-nullable column, I would suggest to use parameters.add — Vivek Nuna, Jul 20 '20 at 01:28
@viveknuna `you are passing null to a non-nullable column` Nullability of the column is not relevant. The OP is passing `null` when they should be passing `DBNull.Value`. — mjwills, Jul 20 '20 at 01:40
@JoeW. I'd consider using Dapper or PetaPoco or similar - using a lightweight ORM like that avoids you having to worry about issues like this. — mjwills, Jul 20 '20 at 01:42

score 0 · Answer 1 · answered Jul 20 '20 at 01:40

I suspect your problem is due to passing (object)null instead of DBNull.Value for null parameters.

These days, I'd avoid all that parameter boilerplate by writing an extension method to bind parameters from a FormattableString.

        public static SqlCommand GetCommandInterpolated(this SqlConnection conn, FormattableString sql)
        {
            var cmd = conn.CreateCommand();
            var replacements = new string[sql.ArgumentCount];
            var args = sql.GetArguments();

            for (int i = 0; i < sql.ArgumentCount; i++)
            {
                var p = cmd.CreateParameter();
                cmd.Parameters.Add(p);
                p.ParameterName = replacements[i] = $"@p_{i}";
                p.Value = args[i] ?? DBNull.Value;
            }
            cmd.CommandText = string.Format(sql.Format, replacements);
            return cmd;
        }


        var cmd = conn.GetCommandInterpolated(
            $@"INSERT INTO customers (firstname, lastname, phone, email, address, city, state, zipcode, password, username)
            VALUES ({u.firstname}, {u.lastname}, {u.phone}, {u.email}, {u.address}, {u.city}, {u.state}, {u.zipcode}, {u.password}, {u.username}"
        );

Thank you all. Problem solved only to create a new one. Appreciate all of the input and help — Joe W., Jul 20 '20 at 02:04

The parameterized query expects'@firstname nvarchar, @lastname nvarchar, @phone nvarchar expects the parameter @email, which was not supplied

1 Answers1