Invalid Use of Default Parameter

For some unknown reason I’m connecting to a Oracle 10g database via ODBC (from .Net! don’t ask). I was adding some new code to a existing project which I’d manage to get to use IDataReader and such so that if one day I decided to switch from ODBC it would be easy.

However all the old code uses SQL strings for updates and inserts and I decided it would be much better to use IDbCommand and IDbDataParameter.

So currently we had this:

Dim sql as String = “INSERT INTO TABLE1 (VAL1, VAL2, VAL3, VAL4, VAL5) VALUES (‘” & Val1 & “‘, ” & Val2 & “, ” & Val3 & “, 0, Null)”

Using comm As IDbCommand = connection.CreateCommand()

  comm.CommandText = sql

  comm.ExecuteNonQuery()

End Using

which quite frankly is just impossible to read once you’ve got more than about 5 parameters.

So I changed it to look like this instead:

Dim sql as String = “INSERT INTO TABLE1 (VAL1, VAL2, VAL3, VAL4, VAL5) VALUES (?, ?, ?, ?, ?)”

Using comm As IDbCommand = connection.CreateCommand()

  comm.CommandText = sql

  Dim param as IDbDataParameter = comm.CreateParameter()

  param.DbType = DbType.String

  param.Value = Val1

  comm.Parameters.Add(param)

  param = comm.CreateParameter()

  param.DbType = DbType.Int32

  param.Value = Val2

  comm.Parameters.Add(param)

  comm.ExecuteNonQuery()

End Using

Much more verbose obviously, but much easier to read. And if you add lines to set ParameterName to something that actually means something then you can easily find the line you want and edit it. (Oh, and it also protects from the likes of Sql Injection attacks, not something I’m actually worried about on this app, but good practices are good for a reason).

Then I came to trying to set a particular field to Null, or rather a particular field value is null (a string in this instance) and therefore null should be passed to the database.

Dim param as IDbDataParameter = comm.CreateParameter()

param.DbType = DbType.String

param.Value = StringProperty

 If StringProperty is null then this falls over with the “Invalid Use Of Default Parameter” error that is the title of this post. So I found this blog post that says its cause I’m trying to set a field to null that can’t be null:

Link to SteveX Compiled » Blog Archive » Invalid Use of Default Parameter

Well, this field CAN be null, so ner! So its obviously a problem with the parameter.

Now IDbDataParameter has a property called IsNullable, which is returning false. However on the IDbDataParameter interface its a readonly property, so can’t be set. I tried casting it to a OdbcParameter and then setting it to True, which it allowed, but still fell over with the error above (plus it removed my nice DB independent code).

Turns out when you’re talking to a database you need to use the DbNull.Value property. So the following works:

Dim param as IDbDataParameter = comm.CreateParameter()

param.DbType = DbType.String

param.Value = IIf(StringProperty Is Nothing, DbNull.Value, StringProperty)

Personally I hate to use IIf, as its just not a very nice language construct (unlike the C# ? operator) but in this instance it works fine. You don’t need to use the IsNullable property at all.

[tags]sql, .net, programming, database, parameters, odbc[/tags]