将空值插入日期字段？

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.

我有一个FormView,我从一个表(MS Access)中提取数据,然后将其(加上更多数据)插入另一个表。我对日期有疑问。

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.

第一个表有两个日期字段:date_submitted和date_updated。在某些记录中,date_updated为空白。这会导致我在尝试插入第二个表时出现数据不匹配错误。

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:

这可能是因为我将第一个表中的date_updated字段数据绑定到FormView上的HiddenField中。然后它从HiddenField获取值并尝试将其插入第二个表:

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.

当有值时,它可以工作,但显然你不能在Access中的日期/时间字段中插入任何内容。我想我可以制作第二个插入语句,不插入date_updated(在没有值indate_updated时使用),但这是唯一的方法吗?似乎应该有一个更简单/更少冗余的方式。

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...

好的。所以我尝试插入SqlDateTime.Null,Nothing和DBNull.Value。 SqlDateTime.Null导致将值1/1/1900插入数据库。 “没什么”导致它插入1/1/2001。如果我尝试使用DBNull.Value,它告诉我它不能转换为字符串,所以也许我没有在那里做一些事情。无论如何,我希望如果没有任何内容可以插入,那么Access中的字段将保持空白,但似乎它必须填充一些东西......

EDIT:

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

我有DBNull.Value工作,它确实插入一个完全空白的值。所以这是我最后的工作代码:

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!

3 个解决方案

#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.

Sara,你有没有尝试过更新它之前的日期/时间?数据不匹配错误可能来自于您尝试插入数据库的hfDateRequestUpdated.Value与列类型不匹配的事实。

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.

尝试单步执行代码并查看该值的类型。如果你发现它是一个字符串(似乎它可能是,因为它来自表单上的字段),那么你需要先检查一下,看看该字段是否为空字符串(VBNullString)。如果是这样,您将需要将要插入数据库的值更改为DBNull,您可以使用DBNull.Value在VB.Net中获取该值。

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

请记住,只有从HiddenField获得的值是一个字符串,上述测试才有效,我相信这是根据文档。这可能就是你所遇到的所有麻烦来自哪里。您隐式将日期/时间值转换为字符串(这很容易),但隐式转换它们并不容易,特别是如果初始值是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:

我认为Marshall试图建议的是相当于上面的代码,但是在一个名为“三元运算符”的快捷表达式中,在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)

我不推荐它,因为它让新程序员感到困惑,而且语法在2008年从IFF改变了(条件?trueResult:falseResult)

#2

Your code

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

has a couple of problems:

有几个问题:

When you declare myDateRequestUpdated to be DateTime, you can't put a DbNull.Value in it.

当您将myDateRequestUpdated声明为DateTime时,您不能在其中放入DbNull.Value。

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)

我不确定你需要DbNull.Value的():它是一个属性,而不是一个方法(我不知道足够的VB来说肯定)

VB doesn't know that : operator

VB不知道:运算符

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

您可能想要的是Nullable(Of DateTime)来存储也可能丢失的DateTime值。

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!).

如果hfDateRequestUpdated.Value为空,则使用Nothing作为结果;否则将值解析为日期(如果它不是有效日期,则可能会失败!)。

#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

#1