Inserting null values into date fields?
asp.net, ms-access, sql, vb.net
Solution
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)`
Problem
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 in`date_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!