Solved

Assign null to a Double

Posted on 2009-04-07
15
785 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:Brendt Hess
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:Brendt Hess
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

627 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