vb.net 将空值插入数据库中的日期列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25129774/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert null into date column in database
提问by user3642720
I've got a gridview in vb.net and am trying to insert a null value into a database column - unfortunately, i keep seeing 1900-01-01 being inserted. below is my code and i need help.
我在 vb.net 中有一个 gridview 并且正在尝试将空值插入到数据库列中 - 不幸的是，我一直看到 1900-01-01 被插入。下面是我的代码，我需要帮助。
Protected Sub gvPOItems_RowCommand(sender As Object, e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvPOItems.RowCommand
If (e.CommandName = "save") Then
For i As Integer = 0 To gvPOItems.Rows.Count - 1
Dim row As GridViewRow = gvPOItems.Rows(i)
Dim value3 As String = DirectCast(row.Cells(9).FindControl("txtbxExpireDate"), TextBox).Text.Replace("'", "''")
If String.IsNullOrEmpty(value3) Or value3.ToString() Is Nothing Then
value3 = DBNull.Value.ToString()
End If
Next
End Sub
回答by Matthew Haugen
It works when the value isn't null, because you're injecting the value in that string:
当值不为空时它起作用，因为您在该字符串中注入值：
INSERT INTO table (datecolumn) VALUES ('2014-08-04')
However, DBNull.Value.ToString()resolves to an empty string. When you then try and insert said empty string into your database via a SQL-injection-prone approach, you're really running a query something like:
但是，DBNull.Value.ToString()解析为空字符串。然后，当您尝试通过易于 SQL 注入的方法将所述空字符串插入数据库时，您实际上是在运行类似于以下内容的查询：
INSERT INTO table (datecolumn) VALUES ('')
And that puts in the default value of 1900-01-01. What you need is:
这将放入默认值1900-01-01. 你需要的是：
INSERT INTO table (datecolumn) VALUES (NULL)
You need to parameterize your queries. Then you can pass Nothingdirectly to your SQL command, and it will work. Aside from that, you have to shift some abstraction so that you only add 'characters if the argument is notNothing, and if it is, then you need to pass the string NULL.
您需要参数化您的查询。然后你可以Nothing直接传递给你的 SQL 命令，它会起作用。除此之外，您必须转移一些抽象，以便仅'在参数不是时添加字符Nothing，如果是，则需要传递字符串NULL。
As for the code you dohave, there are some bugs here, too.
至于你的代码也有，也有一些bug这里了。
Dim value3 As String = DirectCast(row.Cells(9).FindControl("txtbxExpireDate"), TextBox).Text.Replace("'", "''")
If String.IsNullOrEmpty(value3) Or value3.ToString() Is Nothing Then
value3 = DBNull.Value.ToString()
End If
value3is a string, and as per documentation, string.ToString()returns an unconverted instance of the original value. In other words, String.IsNullOrEmpty(value3)already does what you're checking for in your latter condition, except if the latter condition were ever true it would throw a NullReferenceException(or whatever the equivalent of that is in VB, I don't know whether it uses Nothingbecause I've literally never written anything in VB before).
value3是 a string，根据文档，string.ToString()返回原始值的未转换实例。换句话说，String.IsNullOrEmpty(value3)已经在后一个条件中执行了您要检查的操作，除非后一个条件为真，否则它会抛出一个NullReferenceException（或 VB 中的任何等效项，我不知道它是否使用，Nothing因为我之前从未在 VB 中写过任何东西）。
Next, you should be aware that value3will neverbe Nothing. It comes directly from a TextBox.Textproperty, and will consequently be at mostan empty string. That's fine, because you check for that in IsNullOrEmpty, but since you added on the Is NothingI thought I'd better point that out.
接下来，你应该知道，value3将永远是Nothing。它直接来自一个TextBox.Text属性，因此最多是一个空字符串。那很好，因为您在 中检查了它IsNullOrEmpty，但是既然您添加了 ，Is Nothing我想我最好指出这一点。
So basically, long story short, parameterize your queries.For all our sakes. But if you absolutely aren't going to, this should fix this particular problem:
所以基本上，长话短说，参数化你的查询。为了我们所有人。但是，如果您绝对不打算这样做，这应该可以解决此特定问题：
Dim value3 As String = DirectCast(row.Cells(9).FindControl("txtbxExpireDate"), TextBox).Text
If String.IsNullOrEmpty(value3)
value3 = "NULL"
Else
value3 = "'" + value3.Replace("'", "''") + "'"
End If
You'll then have to adjust your later code to not add in those quotes, itself.
然后，您必须调整以后的代码以不添加这些引号本身。
