Jesse Joseph Jesse Joseph - 2 months ago 7
Vb.net Question

Cannot insert Null value into Oracle database

How to add

NULL
value from
datepicker
to db?

strINSERT_INTO_DT = "Insert into BUYER_ENQUIRY_DT (DELIVERY_DATE,REF_DATE)Values"

strINSERT_INTO_DT += " '" & Convert.ToDateTime(dtFromGrid.Rows(i)("DELIVERY_DATE")).ToString("dd/MMM/yyyy") & "'"

strINSERT_INTO_DT += ",'" & Convert.ToDateTime(dtFromGrid.Rows(i)("REF_DATE")).ToString("dd/MMM/yyyy") & "')"

Answer

You should use prepared statements with bind variables. It works much better and NULL values are handled properly. Would be like this:

strINSERT_INTO_DT = "Insert into BUYER_ENQUIRY_DT (DELIVERY_DATE,REF_DATE) Values (:deliveryDate,refDate)"

cmd.Parameters.Add("deliveryDate", OracleDbType.Date, ParameterDirection.Input).Value = _
   CType(Convert.ToDateTime(dtFromGrid.Rows(i)("DELIVERY_DATE")), Oracle.DataAccess.Types.OracleDate)
cmd.Parameters.Add("refDate", OracleDbType.Date, ParameterDirection.Input).Value = _
   CType(Convert.ToDateTime(dtFromGrid.Rows(i)("REF_DATE")), Oracle.DataAccess.Types.OracleDate)
Comments