Saturday, May 09, 2009

Here's a fairly nasty error message with a (sometimes) pretty subtle solution.  Let's say you want to insert a null value into a nullable database column with ADO.NET using parameterized SQL.  The insert statement could look something like this:

INSERT INTO TableName (ColumnName) VALUES (@NullableValue)

Let's say I setup my SqlParameter array as follows:

SqlParameter[] sqlParameterArray = new SqlParameter[1];
SqlParameter oneSqlParameter = new SqlParameter("@NullableValue", SqlDbType.Int);
oneSqlParameter.Value = null; // I want to insert the value null

If I ran the insert using the SqlCommand.ExecuteNonQuery method, I would get this exception:

--
System.Data.SqlClient.SqlException

Prepared statement '(@NullableValue int)INSERT INTO TableName (ColumnName) VALUES (@' expects parameter @NullableValue, which was not supplied.
--

What's wrong?  Apparently, "null" is not a valid value for the Value of a SqlParameter.  You need to do this instead:

oneSqlParameter.Value = DBNull.Value;

Is that error message helpful?  I don't think so.

There are plenty of other reasons why that exception could be thrown, but I've been using ADO.NET for a while and I still stumbled upon this particular under documented problem recently.  Very, very subtle.  Crystal clear error messages from software are so important, yet so uncommon in the real world.

Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):