I need help inserting a NaN value in a float column as 0 in SQL Server 2012.
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.
Dim value1 As Double
sql = "INSERT INTO usb_compliance (Date,atc,value1,) VALUES ('" & Now.Date & "','CMB','" & value1 &"' )"
First, Never concatenate strings to create sql statements.
This is a security risk as it's an open door for Sql injection attacks.
Use parameterized queries instead.
Second, I would recommend using
null to represent
NaN values since 0.0 is a valid float value and
NaN stands for Not A Number, and is basically an unknowable value, just like
null in Sql server.
Having said that, you can simply use a condition in your vb.net like this:
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