SqlParameter - Setting to DEFAULT Value

Question

How do I set a SqlParameter's value to the equivalent of a SQL INSERT statement's DEFAULT keyword?

MSDN documentation says to "use null or do not set Value to use the default value for the parameter." However, doing either results in a SqlException complaining that an expected parameter was not supplied. I also tried setting the parameter to DBNull.Value but that results in a complaint about null values not being allowed.

What's the correct way to do this?

-- table schema
CREATE TABLE SomeTable (Field INT NOT NULL DEFAULT(1));


using (var command = this.conn.CreateCommand())
{
    command.CommandText = "INSERT [dbo].[SomeTable] ([Field]) VALUES (@p0);";

    var param = new SqlParameter();
    param.ParameterName = "@p0";
    //param.Value = ??;
    command.Parameters.Add(param);

    command.ExecuteNonQuery();
}

Lukasz M · Accepted Answer · 2015-07-13 21:48:58Z

5

According to the information I've found, the trick with assigning the null to the value works, but when used for stored procedure parameters.

I guess You want to use the code You've posted to insert some custom values to the table as well as the default. If so, I suppose the easiest way to make Your code work is to call a different query when the value is not specified:

...
int? myValue = null; // I assumed You use a nullable int to pass the value to the method
...
if (!myValue.HasValue)
{
    command.CommandText = "INSERT [dbo].[SomeTable] ([Field]) VALUES (DEFAULT);";
}
else
{
    command.CommandText = "INSERT [dbo].[SomeTable] ([Field]) VALUES (@p0);";

    var param = new SqlParameter();
    param.ParameterName = "@p0";
    param.Value = myValue.Value;
    param.DbType = System.Data.DbType.Int32;
    command.Parameters.Add(param);
}

command.ExecuteNonQuery();

This should allow You to handle both default and non-default cases.

edited Jul 13, 2015 at 21:48

answered Jul 13, 2015 at 21:43

Lukasz M

5,7532 gold badges24 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Gribaudo Over a year ago

Thanks! This complicates pre-compiling then reusing the command with different parameter sets but it is what it is.

Collectives™ on Stack Overflow

SqlParameter - Setting to DEFAULT Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related