Solved

Assign null to a Double

Posted on 2009-04-07
15
782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 24091742
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
ID: 24091887
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
ID: 24091941
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:tamu111981
ID: 24091985
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
ID: 24092045
Like this:

dim x as double?

0
 

Author Comment

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

Expert Comment

by:VBRocks
ID: 24092366
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
 

Author Comment

by:tamu111981
ID: 24092426
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
ID: 24092442
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
ID: 24092447
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
ID: 24092559
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
ID: 24092646
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
ID: 24093405
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
ID: 24097450
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
ID: 24098568
Looks Great!!!!!! Thank you very Much.......
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

751 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