How to handle null field when inserting in access

Question

I'm trying to Insert values from a form to an Access database but I get an error when the field Value is Null. I still get the error even if I use = DBNull.Value.

The first 8 value can't be Null because those are mandatory fields but once it get to Cost if the field is empty I get this error.

Line 577 is da.Update(ds, "EquipList")

This is my code:

 Try
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        Dim sql As String
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\equip_full.mdb;Jet OLEDB:Database Password=matt"
        con.Open()

        sql = "SELECT * FROM EquipList"
        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "EquipList")
        Dim cb As New OleDb.OleDbCommandBuilder(da)
        Dim dsNewRow As DataRow
        dsNewRow = ds.Tables("EquipList").NewRow()

        ' Equipment Information fields

        dsNewRow.Item(0) = UniqueID
        dsNewRow.Item(1) = InvNumber
        dsNewRow.Item(2) = Item

        dsNewRow.Item(3) = Type

        dsNewRow.Item(4) = Description

        dsNewRow.Item(5) = Manufacturer
        dsNewRow.Item(6) = Model_No
        dsNewRow.Item(7) = Serial_No
        If Cost = "" Then
            dsNewRow.Item(8) = DBNull.Value
        Else
            dsNewRow.Item(8) = Cost
        End If
        If Cost_Centre = "" Then
            dsNewRow.Item(9) = DBNull.Value
        Else
            dsNewRow.Item(9) = Cost_Centre
        End If

        'dsNewRow 10 to 39

        ds.Tables("EquipList").Rows.Add(dsNewRow)
        da.Update(ds, "EquipList")
        con.Close()

        MsgBox(Description & " has successfully been added to the system.")

 Catch ex As Exception
        MsgBox("Unable to connect to the database " & ex.ToString)
        Exit Sub
 End Try

Update: My code was fine, it was just a problem with the MsgBox. The MsgBox could not show a value that is Null

---

Removing the MsgBox fixed the problem. It was just there for testing anyway.

Answer 1

it should leave it null if you don't set it to anything else. Have you tried

   If Cost > "" Then
        dsNewRow.Item(8) = Cost
    End If

?

---

Is the field nullable? You could also try setting it to zero if it can't be null:

   If Cost > "" Then
        dsNewRow.Item(8) = Cost
   Else
        dsNewRow.Item(8) = 0
   End If

---

you shouldn't need to explicitly set a date or string value to null, leaving it unset should default the value to null, if it's nullable, which your monetary value may not be.

Your other value, for example, should work with:

If Cost_Centre > "" Then
    dsNewRow.Item(9) = Cost_Centre
End If

---

this is all assuming your values are nullable, or that they will accept null as a valid value. Have you confirmed that through the Access interface?

If a string value will not accept null, the default is the empty string, which is the same value as an empty text box. You should, then, be able to store the value unconditionally just like you're doing with your required values:

   If Cost > "" Then
        dsNewRow.Item(8) = Cost
   Else
        dsNewRow.Item(8) = 0
   End If
   dsNewRow.Item(9) = Cost_Centre