CODEWITHSUNDEEP
.netsqlcommandsqlparameter
Ben Gribaudo
Ben Gribaudo

Reputation: 5147

SqlParameter - Setting to DEFAULT Value

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

Upvotes: 2

Views: 1516

Answers (1)

Lukasz M
Lukasz M

Reputation: 5723

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.

Upvotes: 5

Related Questions