vb.net 将 NaN 值插入浮点列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31286030/
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
Inserting NaN value into a float column
提问by Arzath Areeff
I need help inserting a NaN value in a float column as 0 in SQL Server 2012.
我需要帮助在 SQL Server 2012 中的浮点列中插入一个 NaN 值作为 0。
The data I have inserted into a database is coming from an active directory and sometimes it's generated NaN values, but my database column is defined as a float because I want to get those values from database and do some calculations.
我插入到数据库中的数据来自活动目录，有时它会生成 NaN 值，但我的数据库列被定义为浮点数，因为我想从数据库中获取这些值并进行一些计算。
Dim value1 As Double
sql = "INSERT INTO usb_compliance (Date,atc,value1,) VALUES ('" & Now.Date & "','CMB','" & value1 &"' )"
回答by Zohar Peled
First, Never concatenate strings to create sql statements.
This is a security risk as it's an open door for Sql injectionattacks.
Use parameterized queries instead.
首先，永远不要连接字符串来创建 sql 语句。
这是一个安全风险，因为它为Sql 注入攻击打开了大门。
请改用参数化查询。
Second, I would recommend using nullto represent NaNvalues since 0.0 is a valid float value and NaNstands for Not A Number, and is basically an unknowable value, just like nullin Sql server.
其次，我建议使用null来表示NaN值，因为 0.0 是一个有效的浮点值，NaN代表 Not A Number，并且基本上是一个不可知的值，就像null在 Sql server 中一样。
Having said that, you can simply use a condition in your vb.net like this:
话虽如此，您可以像这样在 vb.net 中简单地使用条件：
Sub InsertValues(ByVal atc As String, ByVal value1 As Double, ByVal d As DateTime)
Dim sql As String = "INSERT INTO usb_compliance (Date,atc,value1) VALUES (@Date, @atc,@value1)"
' SqlConnection con is defined and set elsewhere
Dim cmd As SqlCommand = New SqlCommand(sql, con)
With cmd
.Connection.Open()
.CommandType = CommandType.Text
.Parameters.Add("@Date", SqlDbType.DateTime).Value = d
.Parameters.Add("@atc", SqlDbType.NVarChar).Value = atc
.Parameters.Add("@value1", SqlDbType.Float).Value = IIf(Double.IsNaN(value1), DBNull.Value, value1)
.ExecuteNonQuery()
End With
End Sub
' overload since optional parameter can't be non-constant DateTime.Now
Sub InsertValues(ByVal atc As String, ByVal value1 As Double)
InsertValues(atc, value1, DateTime.Now)
End Sub
and just call it like this:
就这样称呼它：
InsertValues("CMB", value1) '' To use the Now.Date
InsertValues("ERT", value1, DateTime.Now.AddDays(3)) '' to insert the date 3 days from now
