Converting Empty String to NULL Double

Question

I have spent much of the day trying to find an answer for this one. I figured it would be easy but nothing is specific to what I am trying to do. So I hope someone can help.

I am bringing values over from one system to another. I have a few fields that come over as strings but they need to go into the new system as DOUBLE. The problem occurs when there are empty strings ("") and it is trying to store this as double. I have tried EVERYTHING.

dbnull.value
Double.TryParse
CDBL()
Double?
VAL()

etc...cannot get this to work and I am not sure why.

so here is the code:

Dim specvertclr As String = dt23.Rows(0).ItemArray.GetValue(38).ToString()

then when I insert this into my database I get the error because the field type is NUMERIC and I am trying to insert ""

I did have this working:

 Dim specvertclr As String

                        If dt23.Rows(0).ItemArray.GetValue(38).ToString() = "" Then
                            specvertclr = CStr(0)
                        Else
                            specvertclr = dt23.Rows(0).ItemArray.GetValue(38).ToString()
                        End If

But the problem with this is it inserts a 0 value and 0 is not the same as NULL. I want the NUMERIC field in my destination database (SQLCE) to be EMPTY when the string is empty from the source database.

Any help?

This is what I tried:

 Dim specvertclr As Nullable(Of Double) = CType(dt23.Rows(0).ItemArray.GetValue(38).ToString(), Double?)

and then my insert is basic and the program throws an error before it even gets here. I know this part is correct.

  Dim cmd2 As SqlCeCommand = conn.CreateCommand()
                            cmd2.CommandText = "Insert into [Attr_Bridge] ([VERTCLR])  VALUES (?)"
                            With cmd2.Parameters
  .AddWithValue("P1", specvertclr)
end with

look at Double.TryParse - msdn.microsoft.com/en-us/library/… — snow_FFFFFF
– snow_FFFFFF, Commented Aug 24, 2017 at 21:47
Strings are not numbers. specvertclr is a string. You need to use a nullable double var — Steve
– Steve, Commented Aug 24, 2017 at 21:50
I tried that too and I got the following error: Conversion from string "" to type 'Double' is not valid. — Patrick J Keady
– Patrick J Keady, Commented Aug 24, 2017 at 21:59
Well post the code where you use that nullable double. And the code used to insert it in the database — Steve
– Steve, Commented Aug 24, 2017 at 22:00

Steve · Accepted Answer · 2017-08-24 22:40:45Z

0

You need to check the content of your string before adding it to the database

Dim specvertclr = dt23.Rows(0).ItemArray.GetValue(38).ToString() 
....
Dim cmd2 As SqlCeCommand = conn.CreateCommand()
cmd2.CommandText = "Insert into [Attr_Bridge] ([VERTCLR])  VALUES (@P1)"
With cmd2.Parameters
   .Add("@P1", SqlDbType.Float).Value = If(String.IsNullOrEmpty(specvertclr), DBNull.Value, CType(specvertclr, Object))

edited Aug 24, 2017 at 22:40

answered Aug 24, 2017 at 22:08

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Patrick J Keady Over a year ago

what would I use as the type ... SqlDbtype.Float? There is no double option.

Patrick J Keady Over a year ago

Unfortunately this doesn't look like it will work ...it says it is expecting a parameter that I am not supplying.

Patrick J Keady Over a year ago

My insert statement still has something wrong with it. For the purpose of this example I trimmed it down to 1 parameter because that was the only problem - everything else was going into the database just fine. Now that I fixed that one parameter it says there is something wrong with parameter #1. I will post the whole thing in my original question and tell me if you see anything wrong with it...I am confused.

Patrick J Keady · Accepted Answer · 2017-08-24 23:43:17Z

0

This turned out to be the answer .... Thank You Arminius for your linked article that lead me to right answer ... Thank you Steve for your time and help!

 Dim specvertclr As String = dt23.Rows(0).ItemArray.GetValue(38).ToString()

                    Dim oerrorspec As Object = System.DBNull.Value

                    If specvertclr <> Nothing Then oerrorspec = specvertclr

and then in the parameters section I used this

.Add(New SqlCeParameter("@P41", oerrorspec))

answered Aug 24, 2017 at 23:43

Patrick J Keady

33 bronze badges

Comments

Ojleo · Accepted Answer · 2022-01-19 18:02:15Z

0

I came across this question because I had the same issue. I was creating an app and one of the inputs was optional and I didn't want users to worry about putting zero so they don't receive an error. So my question was how to convert "" string to double? I came up with the below:

Dim Nontaxable As Double

        If txtNontaxable.Text IsNot "" Then

            Nontaxable = CDbl(txtNontaxable.Text)

        Else
            txtNontaxable.Text = 0

        End If

if the string isn't blank, it will be converted fine, if its blank, it will be replaced with zero, and then it will be converted fine. Hope this helps anyone had the same issue.

answered Jan 19, 2022 at 18:02

Ojleo

1

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Converting Empty String to NULL Double

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related