The parameterized query expects the parameter , which was not supplied

Question

in my MVC application there is a method

public void insertAddress(AddressModel address)
{
        var connection = OpenConnection();
        var command = connection.CreateCommand();
        command.CommandText = "insert into Adres (AddressLine_1,AddressLine_2,Postcode,Town,DateMovedIn,Id) values (@AddressLine_1, @AddressLine_2, @Postcode, @Town,@DateMovedIn,@Id)";
        AddParameterString(command, "@AddressLine_1", address.AddressLine_1);
        AddParameterString(command, "@AddressLine_2", address.AddressLine_2);
        AddParameterString(command, "@Postcode", address.Postcode);
        AddParameterString(command, "@Town", address.Town);
        AddParameterString(command, "@DateMovedIn", address.DateMovedIn.ToString("yyyyMMdd"));
        AddParameterInt(command, "@Id", address.Id);

        command.ExecuteNonQuery();
}

AddressLine2 in model is not required. When user is not submitting AddressLine2 I get error:

The parameterized query '(@AddressLine_1 nvarchar(3),@AddressLine_2 nvarchar(4000),@Postc' expects the parameter '@AddressLine_2', which was not supplied.

How can I modify this method to work in both cases - user submitting AddressLine2 and user not submitting AddressLine2?

Can you please show the definition of `AddParameterString` method? Why not just use `ParameterCollection` instead? And don't store your `DateTime` values as a string for your `DateMovedIn` column. Change it to `datetime2` type instead. — Soner Gönül, Nov 30 '15 at 07:18
Pass in `null` when not supplied (assuming the underlying database has a column that supports `null`: which it should do if it is optional). — Richard, Nov 30 '15 at 07:18
try address.AddressLine_2 ?? DBNull.Value, or something like this — Gene R, Nov 30 '15 at 07:19
you may use ternary operator to solve this problem.. [link](https://msdn.microsoft.com/en-us/library/ty67wk28.aspx) — Smit Patel, Nov 30 '15 at 07:31

score 2 · Accepted Answer · answered Nov 30 '15 at 07:33

public void insertAddress(AddressModel address)
{
    var connection = OpenConnection();
    var command = connection.CreateCommand();
    command.CommandText = "insert into Adres (AddressLine_1,AddressLine_2,Postcode,Town,DateMovedIn,Id) values (@AddressLine_1, @AddressLine_2, @Postcode, @Town,@DateMovedIn,@Id)";
    command.Parameters.Add(new SqlParameter { ParameterName = "@AddressLine_1", Value = address.AddressLine_1 });
    if (address.AddressLine_2 == null)
    {
        command.Parameters.Add(new SqlParameter { ParameterName = "@AddressLine_2", Value = DBNull.Value });
    }
    else
    {
        command.Parameters.Add(new SqlParameter { ParameterName = "@AddressLine_2", Value = address.AddressLine_2 });
    }
    command.Parameters.Add(new SqlParameter { ParameterName = "@Postcode", Value = address.Postcode });
    command.Parameters.Add(new SqlParameter { ParameterName = "@Town", Value = address.Town });
    command.Parameters.Add(new SqlParameter { ParameterName = "@DateMovedIn", Value = address.DateMovedIn.ToString("yyyyMMdd") });
    command.Parameters.Add(new SqlParameter { ParameterName = "@Id", Value = address.Id });
    command.ExecuteNonQuery();
}

score 2 · Answer 2 · answered Nov 30 '15 at 07:34

2

This happens when a parameter value is null. To fix it you need to set parameter value to DbNull when the property is null.

void AddParameterString(SqlCommand command, string parameterName, string parameterValue)
{
     var param = command.Parameters.Add(parameterName, SqlDbType.NVarChar, 4000);
     param.Value = String.IsNullOrEmpty(parameterValue) ? (object) DbNull.Value : (object) parameterValue;

}

answered Nov 30 '15 at 07:34

Jesús López

8,338
7
40
66

@ Gene R Many thanks for help, I don't know whether it is the best option but I used Gene R solution and it is working very well. – Zet Nov 30 '15 at 08:18
Set Parameter property `IsNullable = true` would indicate to the parser that the parameter may be null, and would stop it complaining of such – Adam Cox Jun 11 '18 at 20:55

score 1 · Answer 3 · answered Dec 23 '19 at 06:40

1

You can simply use Null-coalescing operator (??):

AddParameterString(command, "@AddressLine_2", address.AddressLine_2 ?? Convert.DBNull);

answered Dec 23 '19 at 06:40

Mohammad Reza Sadreddini

568
4
16

The parameterized query expects the parameter , which was not supplied

3 Answers3

Linked