Assign null to a SqlParameter

Question

The following code gives an error - "No implicit conversion from DBnull to int."

SqlParameter[] parameters = new SqlParameter[1]; SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int); planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex; parameters[0] = planIndexParameter;

You need to cast AgeItem.AgeIndex to object I think... stackoverflow.com/questions/202271/… (btw, why the == at the end of the 3rd line?) — Greg
– Greg, Commented Dec 29, 2010 at 16:48

Lee Taylor · Accepted Answer · 2015-11-23 18:19:17Z

413

The problem is that the ?: operator cannot determine the return type because you are either returning an int value or a DBNull type value, which are not compatible.

You can of course cast the instance of AgeIndex to be type object which would satisfy the ?: requirement.

You can use the ?? null-coalescing operator as follows

SqlParameter[] parameters = new SqlParameter[1]; SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int); planIndexParameter.Value = (object)AgeItem.AgeIndex ?? DBNull.Value; parameters[0] = planIndexParameter;

Here is a quote from the MSDN documentation for the ?: operator that explains the problem

Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other.

edited Nov 23, 2015 at 18:19

Lee Taylor

7,99416 gold badges38 silver badges53 bronze badges

answered Dec 29, 2010 at 16:53

Chris Taylor

53.8k10 gold badges81 silver badges92 bronze badges

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

6 Comments

Niels Brinch Over a year ago

Why is there no exception thrown when trying to cast null to object? I would think it should be AgeItem.AgeIndex as object

Chris Taylor Over a year ago

@Niels Brinch, there would not be an exception because null is an object and as long as you do not try dereference it, it is perfectly legal. However, in this example it is not null being cast to object, it is DBNull.Value which is actually a value type. The ?? operator says 'if AgetItem.AgeIndex is null then return DBNull.Value otherwise returen AgeItem.AgeIndex' then the response is cast to object. See null coalescing operator for more details. msdn.microsoft.com/en-us/library/ms173224.aspx

newfurniturey Over a year ago

Technically, your solution using the null-coalescing operator ?? is the same solution as if you were to use the regular ternary ?: - you still need to cast AgeItem.AgeIndex to an object: planIndexParameter.Value = AgeItem.AgeIndex.HasValue ? (object)AgeItem.AgeIndex : DBNull.Value;.

ingredient_15939 Over a year ago

If you were to use the regular ternary ?: to do a type-specific comparison, then casting the entire expression won't work. You have to cast the non-dbnull parameter like so: someID == 0 ? DBNull.Value : (object)someID

QMaster Over a year ago

That is true but if you need using null-able value as an entrance parameter of function that result consume SqlParameter and if it is null you've got error this way is not work and you should use just simple If-Else way. for example: sample.Text.Trim() != "" ? func(sample.Text) : DBNull.Value; will not work as ?: and ??

|

Community · Accepted Answer · 2017-05-23 12:18:15Z

130

The accepted answer suggests making use of a cast. However, most of the SQL types have a special Null field which can be used to avoid this cast.

For example, SqlInt32.Null "Represents a DBNull that can be assigned to this instance of the SqlInt32 class."

int? example = null; object exampleCast = (object) example ?? DBNull.Value; object exampleNoCast = example ?? SqlInt32.Null;

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Dec 27, 2013 at 19:08

Brian

25.9k18 gold badges87 silver badges179 bronze badges

6 Comments

JasDev Over a year ago

The suggestion looked promising so I tried "System.Data.SqlTypes.SqlString.Null" but it doesn't work. It puts actual string of "Null" ('N', 'u', 'l', 'l') into the field instead leaving it blank with true (null). However, the old 2010 "accepted answer" that uses cast with (object) ?? DBNull.Value works correctly. (The ADO.NET provider I used was SQLite but I'm not sure if that makes a difference.) I suggest that others carefully test Brian's tip to make sure null behavior is working as expected.

Brian Over a year ago

@JasDev: I vaguely recall describing this trick in a comment to a high rep user (I think Marc Gravell) and being told it only works on Microsoft SQL Server.

user692942 Over a year ago

@JasDev the provider will be the difference this works in SQL Server as Brain point's out.

Scott Over a year ago

This answer only replaces an explicit cast to object with an implicit.one. In the sample code, exampleNoCast is declared object, so the cast to object still occurs. If, like in the OP's code, the value is assigned directly to SqlParameter.Value which is also of type object, then you still get the cast.

Brian Over a year ago

Even on SQL Server, this seems to fail when using DataTable/SqlBulkCopy.

|

TylerH · Accepted Answer · 2025-08-20 15:36:33Z

You need pass DBNull.Value as a null parameter within SQLCommand, unless a default value is specified within stored procedure (if you are using stored procedure). The best approach is to assign DBNull.Value for any missing parameter before query execution, and following foreach will do the job.

foreach (SqlParameter parameter in sqlCmd.Parameters) { if (parameter.Value == null) { parameter.Value = DBNull.Value; } }

Otherwise change this line:

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex;

As follows:

if (AgeItem.AgeIndex== null) planIndexParameter.Value = DBNull.Value; else planIndexParameter.Value = AgeItem.AgeIndex;

Because you can't use different type of values in conditional statement, as DBNull and int are different from each other.

Adrian · Accepted Answer · 2017-07-28 19:11:36Z

35

With one line of code, try this:

var piParameter = new SqlParameter("@AgeIndex", AgeItem.AgeIndex ?? (object)DBNull.Value);

edited Jul 28, 2017 at 19:11

answered Apr 14, 2017 at 17:18

Adrian

8,6455 gold badges31 silver badges28 bronze badges

1 Comment

Jordec Over a year ago

You can also cast AgeItem.AgeIndex to an object: (object)AgeItem.AgeIndex. But yea also prefer your approach

Tim Schmelter · Accepted Answer · 2016-09-12 08:58:28Z

If you use the conditional(ternary) operator the compiler needs an implicit conversion between both types, otherwise you get an exception.

So you could fix it by casting one of both to System.Object:

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : (object) AgeItem.AgeIndex;

But since the result is not really pretty and you always have to remember this casting, you could use such an extension method instead:

public static object GetDBNullOrValue<T>(this T val) { bool isDbNull = true; Type t = typeof(T); if (Nullable.GetUnderlyingType(t) != null) isDbNull = EqualityComparer<T>.Default.Equals(default(T), val); else if (t.IsValueType) isDbNull = false; else isDbNull = val == null; return isDbNull ? DBNull.Value : (object) val; }

Then you can use this concise code:

planIndexParameter.Value = AgeItem.AgeIndex.GetDBNullOrValue();

decyclone · Accepted Answer · 2010-12-29 16:53:21Z

Try this:

SqlParameter[] parameters = new SqlParameter[1]; SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int); planIndexParameter.IsNullable = true; // Add this line planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex== ; parameters[0] = planIndexParameter;

user432219 · Accepted Answer · 2010-12-29 16:45:52Z

In my opinion the better way is to do this with the Parameters property of the SqlCommand class:

public static void AddCommandParameter(SqlCommand myCommand) { myCommand.Parameters.AddWithValue( "@AgeIndex", (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex); }

But if the value is DBNull.Value, ADO.NET might have somewhat of a hard time guessing what SqlDbType that might be........ this is convenient - but a bit dangerous....

Debashis Chowdhury · Accepted Answer · 2022-06-17 07:50:59Z

you can do something like this. Here startDate and endDate are nullable datetime param

var Statistics= db.Database.SqlQuery<ViewStatistics>("YourStoreProcedure_Or_sqlQuery @startDate,@endDate", new SqlParameter("startDate", startDate?? (object)DBNull.Value), new SqlParameter("endDate", endDate?? (object)DBNull.Value) ).ToList();

Flipster · Accepted Answer · 2011-01-05 06:27:01Z

Try this:

if (AgeItem.AgeIndex != null) { SqlParameter[] parameters = new SqlParameter[1]; SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int); planIndexParameter.Value = AgeItem.AgeIndex; parameters[0] = planIndexParameter; }

In other words, if the parameter is null just don't send it to your stored proc (assuming, of course, that the stored proc accepts null parameters which is implicit in your question).

But now, you're just omitting a parameter - I highly doubt the stored procedure will be happy about this.... most likely, the call will fail stating "no value for parameter @AgeIndex supplied which was expected".....
Wow. Harsh. Just write the stored proc to default to a value if the parameter is not passed (@AgeIndex int = 0). Happens all the time. The client can either accept the default, or override it by passing the parameter. Why the downvote?

Kanwar Singh · Accepted Answer · 2014-05-02 06:13:24Z

Consider using the Nullable(T) structure available. It'll let you only set values if you have them, and your SQL Command objects will recognize the nullable value and process accordingly with no hassle on your end.

Rugmangathan · Accepted Answer · 2014-05-02 06:13:36Z

if (_id_categoria_padre > 0) { objComando.Parameters.Add("id_categoria_padre", SqlDbType.Int).Value = _id_categoria_padre; } else { objComando.Parameters.Add("id_categoria_padre", DBNull.Value).Value = DBNull.Value; }

Ferry To · Accepted Answer · 2025-08-20 21:26:43Z

I use a simple method with a null check.

public SqlParameter GetNullableParameter(string parameterName, object value) { if (value != null) { return new SqlParameter(parameterName, value); } else { return new SqlParameter(parameterName, DBNull.Value); } }

Is that conditional logic backwards? Should DBNull.Value be in the first one?

Ferry To · Accepted Answer · 2025-08-20 21:27:59Z

A simple extension method for this would be:

public static void AddParameter( this SqlCommand sqlCommand, string parameterName, SqlDbType sqlDbType, object item ) { sqlCommand.Parameters .Add(parameterName, sqlDbType) .Value = item ?? DBNull.Value; }

Ferry To · Accepted Answer · 2025-08-20 21:34:09Z

My code, working in real project Look the ternary operator beafore make the sqlparameter this is the best way for me, withou problems:

public bool Key_AddExisting( string clave, int? idHito_FileServer, int? idTipoDocumental_Almacen, string tipoExp_CHJ, int idTipoExp_Verti2, int idMov_Verti2 ) { List<SqlParameter> pars = new List<SqlParameter>() { new SqlParameter { ParameterName = "@Clave", Value = clave }, idHito_FileServer == null // <-- Look ? new SqlParameter { ParameterName = "@IdHito_FileServer", Value = DBNull.Value } : new SqlParameter { ParameterName = "@IdHito_FileServer", Value = idHito_FileServer }, idTipoDocumental_Almacen == null // <-- Look ? new SqlParameter { ParameterName = "@IdTipoDocumental_Almacen", Value = DBNull.Value } : new SqlParameter { ParameterName = "@IdTipoDocumental_Almacen", Value = idTipoDocumental_Almacen }, new SqlParameter { ParameterName = "@TipoExp_CHJ", Value = tipoExp_CHJ }, new SqlParameter { ParameterName = "@IdTipoExp_Verti2", Value = idTipoExp_Verti2 }, new SqlParameter { ParameterName = "@IdMov_Verti2", Value = idMov_Verti2 } }; string sql = "INSERT INTO [dbo].[Enlaces_ClavesCHJ_MovimientosVerti2] " + "( " + " [Clave] " + ", [IdHito_FileServer] " + ", [IdTipoDocumental_Almacen] " + ", [TipoExp_CHJ] " + ", [IdTipoExp_Verti2] " + ", [IdMov_Verti2] " + ") " + "VALUES" + "( " + " @Clave" + ", @IdHito_FileServer" + ", @IdTipoDocumental_Almacen" + ", @TipoExp_CHJ" + ", @IdTipoExp_Verti2" + ", @IdMov_Verti2" + ")"; return DbBasic.ExecNonQuery(ref this.conn, sql, pars); }

Marco · Accepted Answer · 2013-07-11 22:16:08Z

try something like this:

if (_id_categoria_padre > 0) { objComando.Parameters.Add("id_categoria_padre", SqlDbType.Int).Value = _id_categoria_padre; } else { objComando.Parameters.Add("id_categoria_padre", DBNull.Value).Value = DBNull.Value; }

A Sinan Direk · Accepted Answer · 2014-03-01 11:55:27Z

int? nullableValue = null; object nullableValueDB { get{ if(nullableValue==null) return DBNull.Value; else return (int)nullableValue; } }

I'm solving like that.

Anil Kumar · Accepted Answer · 2014-05-02 06:13:49Z

if (AgeItem.AgeIndex== null) cmd.Parameters.Add(new SqlParameter("ParaMeterName", SqlDbType.DateTime).Value = DBNull); else cmd.Parameters.Add(new SqlParameter("ParaMeterName", SqlDbType.DateTime).Value = AgeItem.AgeIndex);

Ferry To · Accepted Answer · 2025-08-20 21:30:11Z

sqlCom.Parameters .Add(new SqlParameter("@qavCode", SqlDbType.Char, 11)) .Value = (object)(string.IsNullOrEmpty(rf.Request.QavCode) ? null : rf.Request.QavCode) ?? DBNull.Value;

To improve the usage of the null-coalescing operator ?? to manage empty strings in my example I mixed the regular ternary ?: to the null-coalescing operator ??.

Hope my suggestion is useful.

Ferry To · Accepted Answer · 2025-08-20 21:31:20Z

This is what I simply do...

var PhoneParam = new SqlParameter("@Phone", DBNull.Value); if (user.User_Info_Phone != null) { PhoneParam.SqlValue = user.User_Info_Phone; } return this.Database.SqlQuery<CustLogonDM>( "UpdateUserInfo @UserName, @NameLast, @NameMiddle, @NameFirst, @Address, @City, @State, @PostalCode, @Phone", UserNameParam, NameLastParam, NameMiddleParam, NameFirstParam, AddressParam, CityParam, StateParam, PostalParam, PhoneParam ).Single();

Ferry To · Accepted Answer · 2025-08-20 21:32:20Z

dynamic psd = DBNull.Value; if (schedule.pushScheduleDate > DateTime.MinValue) { psd = schedule.pushScheduleDate; } sql.DBController.RunGeneralStoredProcedureNonQuery( "SchedulePush", new string[] { "@PushScheduleDate" }, new object[] { psd }, 10, "PushCenter" );

Collectives™ on Stack Overflow

Assign null to a SqlParameter

20 Answers 20

6 Comments

6 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

20 Answers 20

6 Comments

6 Comments

Comments

1 Comment

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related