Ben Reichelt’s Weblog

This is just a tip about using SqlCommand objects with SqlParameters and null database values. Here’s one way to add a parameter to a command object:

SqlParameter param = sqlCmd.Parameters.Add(”@FirstName”, SqlDbType.VarChar);
param.Value = ‘ben’;

This adds a parameter named ‘FirstName’ with a datatype of ‘VarChar’ and a value of ‘ben’ to my SqlCommand.  Now, if you wanted to make the value of the parameter a null value, this is what I typically do:

SqlParameter param = sqlCmd.Parameters.Add(“@FirstName”, SqlDbType.VarChar);
param.Value = null;

Since the Value property of a parameter is of type System.Object, you can simply set it to null.  This is wrong.  Don’t do it.  You’ll get exceptions about missing parameters.  I ALWAYS forget this and I’m ALWAYS confused by the errors until I remember this.  If you set the parameter value to null, the SqlCommand must simply exclude the parameter when it executes, which would cause the missing parameter error.  Instead, this is how you set the value of a parameter to null:

SqlParameter param = sqlCmd.Parameters.Add(“@FirstName”, SqlDbType.VarChar);
param.IsNullable = true;
param.Value = System.DBNull.Value;

There is a special class ‘DBNull’ that is used to represent NULL values, so if you do it like the example above, you should be all set.