Inserting NaN value into a float column

Question

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 &"' )" 

Solution

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