Inserting null values into date fields?

Question

I have a FormView where I pull data from one table (MS Access), and then insert it (plus more data) into another table. I'm having issues with the dates.

The first table has two date fields: date_submitted and date_updated. In some records, date_updated is blank. This causes me to get a data mismatch error when attempting to insert into the second table.

It might be because I'm databinding the date_updated field from the first table into a HiddenField on the FormView. It then takes the value from the HiddenField and attempts to insert it into the second table:

Dim hfDateRequestUpdated As HiddenField = FormView1.FindControl("hfDateRequestUpdated")
myDateRequestUpdated = hfDateRequestUpdated.Value
'... It then attempts to insert myDateRequestUpdated into the database.

It works when there is a value there, but apparently you can't insert nothing into a date/time field in Access. I suppose I could make a second insert statement that does not insert into date_updated (to use when there is no value indate_updated), but is that the only way to do it? Seems like there should be an easier/less redundant way.

EDIT:

Okay. So I've tried inserting SqlDateTime.Null, Nothing, and DBNull.Value. SqlDateTime.Null results in the value 1/1/1900 being inserted into the database. "Nothing" causes it to insert 1/1/2001. And if I try to use DBNull.Value, it tells me that it cannot be converted to a string, so maybe I didn't do something quite right there. At any rate, I was hoping that if there was nothing to insert that the field in Access would remain blank, but it seems that it has to fill it with something...

EDIT:

I got DBNull.Value to work, and it does insert a completely blank value. So this is my final working code:

Dim hfDateRequestUpdated As HiddenField = FormView1.FindControl("hfDateRequestUpdated")
Dim myDateRequestUpdated = Nothing

If hfDateRequestUpdated.Value = Nothing Then
    myDateRequestUpdated = DBNull.Value
Else
    myDateRequestUpdated = DateTime.Parse(hfDateRequestUpdated.Value)
End If

Thanks everyone!

Solution 1

Sara, have you tried casting the date/time before you update it? The data mismatch error likely comes from the fact that the hfDateRequestUpdated.Value you're trying to insert into the database doesn't match the column type.

Try stepping through your code and seeing what the type of that value is. If you find that it's a string (which it seems it might be, since it's coming from a field on a form), then you will need a check first to see if that field is the empty string (VBNullString). If so, you will want to change the value you're inserting into the database to DBNull, which you can get in VB.Net using DBNull.Value.

We can't see your code, so we don't know exactly how you get the value into the database, but it would look something like this

If theDateValueBeingInserted is Nothing Then
    theDateValueBeingInserted = DBNull.Value
EndIf

Keep in mind that the above test only works if the value you get from the HiddenField is a string, which I believe it is according to the documentation. That's probably where all this trouble you're having is coming from. You're implicitly converting your date/time values to a string (which is easy), but implicitly converting them back isn't so easy, especially if the initial value was a DBNull

---

aside

I think what Marshall was trying to suggest was the equivalent of the above code, but in a shortcut expression called the 'ternary operator', which looks like this in VB.Net:

newValue = IF(oldValue is Nothing ? DBNull.Value : oldValue)

I wouldn't recommend it though, since it's confusing to new programmers, and the syntax changed in 2008 from IFF(condition ? trueResult : falseResult)

Solution 2

Your code

Dim myDateRequestUpdated As DateTime
myDateRequestUpdated = DateTime.Parse(hfDateRequestUpdated.Value) : DBNull.Value()

has a couple of problems:

1. When you declare myDateRequestUpdated to be DateTime, you can't put a DbNull.Value in it.
2. I'm not sure you need the () for DbNull.Value: it's a property, not a method (I don't know enough VB to say for sure)
3. VB doesn't know that : operator

What you probably want is a Nullable(Of DateTime) to store a DateTime value that can also be missing.

Then use something like this to store the value:

myDateRequestUpdated = If(String.IsNullOrWhiteSpace(hfDateRequestUpdated.Value),
   Nothing, DateTime.Parse(hfDateRequestUpdated.Value))

If hfDateRequestUpdated.Value is empty, then use Nothing as the result; else parse the value as date (which might fail if it is not a valid date!).

Solution 3

Try this:

  Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim str As String

    If TextBox1.Text.Length <> 0 Then
        str = "'" & TextBox1.Text & "'"
    Else
        str = "NULL"
    End If

    sql = "insert into test(test1) values(" & str & ")"
    dsave_sql(sql)
End Sub


Function save_sql(ByVal strsql As String, Optional ByVal msg As String = "Record Saved Sucessfully") As String
    Dim sqlcon As New SqlConnection(strConn)
    Dim comm As New SqlCommand(strsql, sqlcon)
    Dim i As Integer
    Try
        sqlcon.Open()
        i = CType(comm.ExecuteScalar(), Integer)
        save_sql = msg
    Catch ex As Exception
        save_sql = ex.Message
    End Try
    sqlcon.Close()
    Return i
End Function