Assign null to a Double

There is a minor conceptual error in what you are attempting to do.... double variables (like int and all other numeric variables) cannot be assigned a dbNull value.  Only an object can be assigned a value like that.

So the question becomes:  What are you trying to achieve?  The closest you can come to this type of logic in VB.NET would be a statement like:

Dim x as Nullable(Of Double) = Nothing

Thank you for your comment. It is a very important concept that I did not know.

I want to pass a null value in the database field, as my understanding goes null is different then nothing as nothing is not pointing to anything. While null is a value for no value..... current me if i am wrong.

The update query I wrote is throwing me an error "String to Binary data would be truncated" The statement has been terminated.

Right now the code sets the parameters value to 'nothing' if the textbox is left empty. And I was thinking that rather then nothing the parameter value should be set not 'null'

You are generally correct, but not about where to assign the dbNull value.  When you are filling the parameters of the query (not when assigning the values to the variables), check for a NOTHING value.  If it is Nothing, assign dbNull.Value, otherwise assign the variable's value, something like

X=CASE (MyVar is Nothing)
     WHEN TRUE Then DBNull.Value
     ELSE MyVar
END

This is what i am doing

Dim ActualLiverageFactor As Double
            If Trim(cmbActualLiverageFactor.Text) = "" Then
                ActualLiverageFactor = Nothing                                                   ......I want to assign a null here
            ElseIf Not IsNumeric(cmbActualLiverageFactor.Text) Then
                ErrorProvider1.SetError(cmbActualLiverageFactor, "Please enter a Number")
            Else : ActualLiverageFactor = CDbl(Trim(cmbActualLiverageFactor.Text))
            End If

Like this:

dim x as double?

I am sorry I could not understand what are you  asking

No, I'm sorry...  That wasn't a question.  I was showing you how to declare a variable as double/nullable

        Dim x As Double? = Nothing

        If x Is Nothing Then _
            MsgBox("x is nothing")

if I may ask

what does the question mark do,

actually adding a question mark is causing the blue line  to appear.

.....now i am beginning to wonder is it a question mark or my browser is screwing it up. lol

In VS 2008 it's the same as:

        Dim x As Nullable(Of Double) = Nothing

        If x Is Nothing Then _
            MsgBox("x is nothing")

I am a bit doubtful about if null is the same as nothing...... I have written the code i am using above, let me past it again...

Dim ActualLiverageFactor As Double
            If Trim(cmbActualLiverageFactor.Text) = "" Then
                ActualLiverageFactor = Nothing                                                   ......I want to assign a null here
            ElseIf Not IsNumeric(cmbActualLiverageFactor.Text) Then
                ErrorProvider1.SetError(cmbActualLiverageFactor, "Please enter a Number")
            Else : ActualLiverageFactor = CDbl(Trim(cmbActualLiverageFactor.Text))
            End If

Oh, my apologies bhess1!  I didn't see you already posted that above...

If you want to use "DBNull.Value", then you have to declare you ActualLiverageFactor as Object (like bhess1 said)

Why DBNull.Value?  Are you using a DataReader or DataTable when retrieving your data, or are you using a custom class?

I want to pass a null value in the database field,

The update query I wrote is throwing me an error "String to Binary data would be truncated" The statement has been terminated.

Right now the code sets the parameters value to 'nothing' if the textbox is left empty.
And I was thinking that rather then nothing, the parameter value should be set to 'null'.
I have been struggling with the Update query for a while..trying to find whats wrong
with it, so as a precaution I was thinking rather then passing a nothing let me try 'null'
....... let me past that too.
Thank you very much for your helping attitude.

Dim mActualMovement As Boolean
Dim mActualMood As Boolean
 
Private Sub UpdateActualData()
        Try
            Call FindIndex()
            Call PipValue()
 
            Dim ActualEffect As Double = Nothing
            If Trim(txtActualEffect.Text) = "" Then
                ActualEffect = Nothing
            ElseIf Not IsNumeric(txtActualEffect.Text) Then
                ErrorProvider1.SetError(txtActualEffect, "Please enter a Number")
            Else : ActualEffect = CDbl(Trim(txtActualEffect.Text))
            End If
 
            Dim ActualLiverageFactor As Double
            If Trim(cmbActualLiverageFactor.Text) = "" Then
                ActualLiverageFactor = Nothing
            ElseIf Not IsNumeric(cmbActualLiverageFactor.Text) Then
                ErrorProvider1.SetError(cmbActualLiverageFactor, "Please enter a Number")
            Else : ActualLiverageFactor = CDbl(Trim(cmbActualLiverageFactor.Text))
            End If
 
            Dim ActualMood As String
            If Trim(txtActualMood.Text) = "" Then
                ActualMood = Nothing
            Else : ActualMood = Trim(txtActualMood.Text)
            End If
 
            Dim DiffrenceMovement As String
            If Trim(txtDiffrenceMovement.Text) = "" Then
                DiffrenceMovement = DBNull.Value.ToString
            Else : DiffrenceMovement = Trim(txtDiffrenceMovement.Text)
            End If
 
            Dim DiffrenceMood As String
            If Trim(txtDiffrenceMood.Text) = "" Then
                DiffrenceMood = DBNull.Value.ToString
            Else : DiffrenceMood = Trim(txtDiffrenceMood.Text)
            End If
 
            Dim EffectLasting As String
            If Trim(cmbEffectLasting.Text) = "" Then
                EffectLasting = DBNull.Value.ToString
            Else : EffectLasting = Trim(cmbEffectLasting.Text)
            End If
 
            Dim TimeToDiscountNews As String
            If Trim(cmbTimeToDiscountNews.Text) = "" Then
                TimeToDiscountNews = DBNull.Value.ToString
            Else : TimeToDiscountNews = Trim(cmbTimeToDiscountNews.Text)
            End If
 
 
Dim UpdateActualOutcome As New SqlCommand
            UpdateActualOutcome.Connection = mMyConnection
            UpdateActualOutcome.CommandText = "UPDATE event_outcome_t " & _
            "SET actual_value = ' " & ActualEffect & "'," & _
            "actual_liverage_factor = ' " & ActualLiverageFactor & "'," & _
            "actual_effect_index = '" & mActualMovement & "'," & _
            "actual_mood = ' " & ActualMood & "'," & _
            "actual_mood_liverage_factor = ' " & cmbActualLiverageFactor.Text & "'," & _
            "actual_mood_index = '" & mActualMood & "'," & _
            "time_after_event = ' " & NowTime & "'," & _
            "pip_value = ' " & Trim(txtPipGainLoss.Text) & "'," & _
            "why_the_diffrence_movm = ' " & DiffrenceMovement & "'," & _
            "why_the_diffrence_mood = ' " & DiffrenceMood & "'," & _
            "how_long_effect_lasted = ' " & EffectLasting & "'," & _
            "time_taken_discount_news = ' " & TimeToDiscountNews & "'" & _
            "WHERE event_id = " & EventId & ""
            mMyConnection.Open()
            UpdateActualOutcome.ExecuteNonQuery()
            mMyConnection.Close()

Select allOpen in new window

If you're just doing it that way, then do it like this:

            '*** Declared as Object, set to DBNull.Value as default
            Dim ActualEffect As Object = DBNull.Value          

            'Since the "ActualEffect variable is ALREADY set to null, no need to set it again,
            '  so only change it's value if there is a number
            If txtActualEffect.Text.Trim() <> "" Then

                'Remember that we've already validated the data, so we know that if we've
                '  made it to this line of code, then the value that is in the textbox is a number.
                ActualEffect = CDbl(txtActualEffect.Text.Trim())

            End If


Just as a suggestion, you should perform your validation before "UpdateActualData" method is called.  Because, what if the user enters letters?  Although you have checked for it, and display an error, the update still attempts.  Ideally, perform the error checking when the user leaves a control (such as a TextBox or ComboBox), that way they can fix their errors as they work through the form.  However, you can also check for errors when the user clicks the submit button, and for the sake of simplicity, I'll demonstrate that below.


Here's an example of what I mean:

Dim bValidated As Boolean = ValidateData()

if bValidated = True Then

    'Only attempt the update if the data that has been entered in the form has been validated.
    UpdateActualData()

End If


'This is a function to validate the data that has been entered in the controls on your form.
Private Function ValidateData() As Boolean

    Dim bValidated As Boolean = True

    If Not IsNumeric(txtActualEffect.Text) AndAlso txtActualEffect.Text.Trim() <> "" Then

                'Acceptable values are numbers or "", but not letters
                ErrorProvider1.SetError(txtActualEffect, "Please enter a Number")

                bValidated = False       'Set to False, because Validate failed
    Else
                ErrorProvider1.SetError(txtActualEffect, "")       'Clear error
    End If

    Return bValidated

End Function


'This is the sub to perform the update after the data has been validated
Private Sub UpdateActualData()
        Try
            Call FindIndex()
            Call PipValue()
 
            Dim ActualEffect As Object = DBNull.Value          

            If txtActualEffect.Text.Trim() <> "" Then

                ActualEffect = CDbl(txtActualEffect.Text.Trim())

            End If

            'Remaining code...

End Sub

Looks Great!!!!!! Thank you very Much.......