Npgsql parameters with NULL value, vb.net

Question

I'm running through a loop adding parameters, when I get to a NULL, the program punts. My statement looks like this:

mysql = "Insert into TABLE (field1) VALUES (:Col1)"

mycomm = New NpgsqlCommand (mySQL, conn)

myComm.Parameters.Add("Col" & cCnt, NpgsqlTypes.NpgsqlDbType.Double).Value = Obj.value

myComm.ExecuteNonQuery()

This works fine if Obj.value is not empty, but if Obj.value is nothing, my Execute statement fails. I'd like to just insert a NULL value into the DB. Any ideas? Thanks for any help!

score 9 · Accepted Answer · edited May 23 '17 at 11:54

To insert a NULL value in the database field, you need to pass the DBNull.Value to your parameter

You could use the VB.NET ternary operator to check if Obj itself is Nothing or if Obj.Value is nothing and in that case (true) pass the DBNull.Value instead of the Obj.Value

myComm.Parameters.Add("Col" & cCnt, NpgsqlTypes.NpgsqlDbType.Double).Value = 
                      If(Obj Is Nothing OrElse Obj.Value Is Nothing, DBNull.Value, Obj.value))

Npgsql parameters with NULL value, vb.net

1 Answers1