Solved

Assign null to a Double

Posted on 2009-04-07
15
773 Views
Last Modified: 2012-05-06
I want to assign a null value to a double.

What does not work

1.
dim x as double

x = DBNull.Value

or

x Is DBNull

Thank You for Your help.
0
Comment
Question by:tamu111981
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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

0
 

Author Comment

by:tamu111981
Comment Utility
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'

0
 
LVL 32

Expert Comment

by:bhess1
Comment Utility
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
0
 

Author Comment

by:tamu111981
Comment Utility
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
0
 
LVL 27

Expert Comment

by:VBRocks
Comment Utility
Like this:

dim x as double?

0
 

Author Comment

by:tamu111981
Comment Utility
I am sorry I could not understand what are you  asking
0
 
LVL 27

Expert Comment

by:VBRocks
Comment Utility
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")

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:tamu111981
Comment Utility
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
0
 
LVL 27

Expert Comment

by:VBRocks
Comment Utility
In VS 2008 it's the same as:

        Dim x As Nullable(Of Double) = Nothing

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

Author Comment

by:tamu111981
Comment Utility
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
0
 
LVL 27

Expert Comment

by:VBRocks
Comment Utility
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?

0
 

Author Comment

by:tamu111981
Comment Utility
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()

Open in new window

0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
Comment Utility
try like this,
i added if conditions for the first two parameters, you have to do it the similar check for other parameters
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 =  Nothing

            Else : DiffrenceMovement = Trim(txtDiffrenceMovement.Text)

            End If

 

            Dim DiffrenceMood As String

            If Trim(txtDiffrenceMood.Text) = "" Then

                DiffrenceMood =  Nothing

            Else : DiffrenceMood = Trim(txtDiffrenceMood.Text)

            End If

 

            Dim EffectLasting As String

            If Trim(cmbEffectLasting.Text) = "" Then

                EffectLasting =  Nothing

            Else : EffectLasting = Trim(cmbEffectLasting.Text)

            End If

 

            Dim TimeToDiscountNews As String

            If Trim(cmbTimeToDiscountNews.Text) = "" Then

                TimeToDiscountNews =  Nothing

            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 ," & _

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

            if ActualEffect is nothing then

	            UpdateActualOutcome.Parameters.AddWithValue( "@ActualEffect", dbnull.value)

            else

	            UpdateActualOutcome.Parameters.AddWithValue( "@ActualEffect", ActualEffect)

            end if

            if ActualLiverageFactor is nothing then

	            UpdateActualOutcome.Parameters.AddWithValue( "@ActualLiverageFactor" , dbnull.value)

            else

	            UpdateActualOutcome.Parameters.AddWithValue( "@ActualLiverageFactor" , ActualLiverageFactor)

            end if

            UpdateActualOutcome.Parameters.AddWithValue( "@mActualMovement" ,mActualMovement)

            UpdateActualOutcome.Parameters.AddWithValue( "@ActualMood" , ActualMood)

            UpdateActualOutcome.Parameters.AddWithValue( "@cmbActualLiverageFactor" ,cmbActualLiverageFactor.text)

            UpdateActualOutcome.Parameters.AddWithValue( "@mActualMood" ,mActualMood)

            UpdateActualOutcome.Parameters.AddWithValue( "@NowTime", NowTime)

            UpdateActualOutcome.Parameters.AddWithValue( "@DiffrenceMovement" ,DiffrenceMovement)

            UpdateActualOutcome.Parameters.AddWithValue( "@DiffrenceMood" ,DiffrenceMood)

            UpdateActualOutcome.Parameters.AddWithValue( "@EffectLasting" ,EffectLasting)

            UpdateActualOutcome.Parameters.AddWithValue( "@TimeToDiscountNews", TimeToDiscountNews)
 
 
 

            mMyConnection.Open()

            UpdateActualOutcome.ExecuteNonQuery()

            mMyConnection.Close()

Open in new window

0
 
LVL 27

Expert Comment

by:VBRocks
Comment Utility
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

0
 

Author Comment

by:tamu111981
Comment Utility
Looks Great!!!!!! Thank you very Much.......
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now