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]