CODEWITHSUNDEEP
vb.netoracle11g
Popplar
Popplar

Reputation: 279

Error while filling oracle parameters (Object cannot be cast from DBNull to other types)

Im trying to add Parameters to the OracleCommand object but an exception is thrown (Object cannot...). What im trying to do is, if a column from the datatable is null, then insert to the database the null value (that column must be empty). The datatable from where it takes the data is formatted something like this:

dt.Columns.Add("NAME", GetType(String))
dt.Columns.Add("AGE", GetType(Integer))

Fill

dt.Rows(0)("NAME") = DBNull.Value
dt.Rows(0)("AGE") = DBNull.Value

Later when inserting the data via procedure:

....
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("NAME", OracleType.VarChar).Value = IIf(IsDBNull(dt.Rows(0)("CVV")), OracleClient.OracleString.Null, dt.Rows(0)("CVV")) <-- Seems to work.
cmd.Parameters.Add("AGE"), OracleType.Number).Value = IIf(dt.Rows(0).IsNull("AGE"), OracleClient.OracleNumber.Null, CInt(dt.Rows(0)("AGE"))) <-- Error.

Any suggestion?

Upvotes: 0

Views: 79

Answers (1)

Steve
Steve

Reputation: 216323

Use the If operator. This short-circuit the evaluation of the expression, while Iif does not.

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("NAME", OracleType.VarChar).Value = If(IsDBNull(dt.Rows(0)("CVV")), 
                  OracleClient.OracleString.Null, dt.Rows(0)("CVV")) 
cmd.Parameters.Add("AGE"), OracleType.Number).Value = If(dt.Rows(0).IsNull("AGE"), 
                  OracleClient.OracleNumber.Null, CInt(dt.Rows(0)("AGE")))

In the IIf function both sides of the expression are evaluated, so you Cint fails because is called even when is true that the row is null, this is not the case with the if operator

Upvotes: 1

Related Questions