Prepared Statements and the “Parameter X has no default value” error

Posted January 27th, 2010 by Wesley

When I stumbled upon this error “parameter @columnName has no default value”, first thing I tried was Google, of course.  When googling, I found lots of answers for this problem, but they all resulted in the following solutions:

• Parameter name is different in the query and when declaring the parameter (wrong columnName, or wrong parameter assigned – the copy/paste error)

• The parameters are not in the right order (especially true when querying Microsoft Access databases)

 
Because you’re reading this, you can assume by now that I hadn’t found a solution for my problem.  Well, that’s not entirely true, but I still needed another solution to completely solve my problem.

First issue: the order of parameters was incorrect. But when you google around for query expression in Access, you’ll see two ways for defining parameters: using question marks, or a parameter name.  I was using parameter names, and expected that the DbCommand object (actually a OleDbCommand object) would map the parameters correctly.

It appears that I was wrong.

Whether you use names or question marks doesn’t matter when the command object is mapping the values, it just fills up the parameters in the query one by one.

Second issue: I’m using Nullable’s in my model class.  For those who don’t know what Nullable is: ever had a DateTime in a model class and a “datetime” field in a database table with NULL as its value?  What value did you give your DateTime field? DateTime.MinValue? Or did you keep track whether the value was set or not with a boolean?
Nullable was added to the .NET Framework 2.0 for this problem.  You can use it to wrap basic data types and structures, like int (Integer), decimal (Decimal), DateTime, …

In C#:

Nullable<int> nValueOrNull;
OR
int? nValueOrNull;

In VB.Net:

Dim nValueOrNothing As Nullable(Of Integer)
OR
Dim nValueOrNothing? As Integer
OR
Dim nValueOrNothing As Integer?

Note that the last two ways of defining a Nullable in VB.Net are only available from .NET Framework 3.5 and higher. More information: http://msdn.microsoft.com/en-us/library/ms235245.aspx

Now, when I assign a field (which is a Nullable<int>) to a parameter object, and that field contains no value (I deliberately didn’t say: that field is null), I expected that the command or even the parameter itself would translate that to NULL in the database, especially because the IsNullable property of the parameter was set to True.  Of course, I was wrong about my assumptions.  After trying some things, eventually I tried passing DBNull.Value when my field contained no value: that did the trick.

So, in short: null (or Nothing) is NOT the same as DBNull.Value when assigning values to query parameters.  Nullable can help you matching a model class to a database table, but doesn’t help when translating DBNull from a query result or to a query parameter.

Tags: ado.net, DbCommand, nullable, OleDbCommand, prepared statement