Solved

{"Conversion from string """" to type 'Double' is not valid."} ASP.NET VB.NET

Posted on 2016-09-18
29
35 Views
Last Modified: 2016-09-18
Hello,

I am trying to save data to a new record from several unbounded Text Fields to my SQL database when pressing a Save button on my web page. However, I only what to save the values of fields where the value is > 0 (if it is 0 or null,  then that column NEED be NULL in the SQL table).

The code below seems to work as long the field has a 0 or > 0 however it fails if it is NULL and I get {"Conversion from string """" to type 'Double' is not valid."} error message. I tried several options but all seem to fail for me. Any suggestion?

Dim MyTotal As Integer

If (BoxTotal.Text) > 0 Then
  MyTotal = BoxTotal.Text
Else
  MyTotal = Nothing
End If

Open in new window

0
Comment
Question by:Cobra967
[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
  • 16
  • 13
29 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803805
Is that value in the database nullable? Most likely not.
0
 

Author Comment

by:Cobra967
ID: 41803830
In SQL database that field data type is Int and it does allow Null Values: ([MyTotal] [int] NULL,)
0
 

Author Comment

by:Cobra967
ID: 41803840
If it helps, this in a nutshell how I save the record:

Protected Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
Dim MyTotal As Integer
Dim Filed1 As Integer
Dim Field2 As Integer

Using con As SqlConnection = GetSqlConnection()

If (BoxTotal.Text) > 0 Then
  MyTotal = BoxTotal.Text
Else
  MyTotal = Nothing
End If

If Field1 ...

If Filed2 ...

Save(con, Field1, Field2, MyTotal)
End Using

Private Sub Save(con As SqlConnection, Field1 As Integer, Field2 As Integer, MyTotal As Integer)
        Using cmd As New System.Data.SqlClient.SqlCommand
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = "INSERT INTO MYTABLE(Field1, Field2 , MyTotal) " &
                "VALUES (@Field1, @Field2, @MyTotal);"
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Field1", Field1)
            cmd.Parameters.AddWithValue("@Field2", Field2)
            cmd.Parameters.AddWithValue("@MyTotal", MyTotal)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using

Private Shared Function GetSqlConnection() As SqlConnection
        Dim constring As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim con As New SqlConnection(constring)
        Return con
    End Function

Open in new window

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803842
Somewhere in your code you are trying to convert from an empty string to a Double data type.  Can you post more of your code and show the declarations for all the variable used in that code. Also on which line the exception happens on.
0
 

Author Comment

by:Cobra967
ID: 41803843
I just did :-) In my last posting it happen on code line #8
0
 

Author Comment

by:Cobra967
ID: 41803849
On that line I get: System.InvalidCastException {"Conversion from string """" to type 'Double' is not valid."}
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803854
Well in that case BoxTotal.Text must be empty or spaces in it. Are you placing a numeric value in it?
0
 

Author Comment

by:Cobra967
ID: 41803861
It is empty. Here the use has the option to enter a number or leave it empty.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803866
Then check to make sure it has a value and that the value is numeric and that should work. Like the below code.
If (Not String.IsNullOrWhiteSpace(BoxTotal.Text)) AndAlso IsNumeric(BoxTotal.Text) AndAlso BoxTotal.Text > 0 Then
    MyTotal = BoxTotal.Text
Else
    MyTotal = Nothing
End If

Open in new window

0
 

Author Comment

by:Cobra967
ID: 41803875
Still not working :-( Regardless of the value in MyTotal.Text (Empty, 0 or 1) I get a 0 in SQL. IT appear that  MyTotal = Nothing actually returns a 0
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803891
Actually the original problem is no longer happening, System.InvalidCastException {"Conversion from string """" to type 'Double' is not valid."}, You now have must likely a run time error or a logic error.

Also you stated this, "MyTotal = Nothing actually returns a 0", that would be correct, from Microsoft documentation,
Nothing represents the default value of any data type. For reference types, the default value is the null reference. For value types, the default value depends on whether the value type is nullable.

For non-nullable value types, Nothing in Visual Basic differs from null in C#. In Visual Basic, if you set a variable of a non-nullable value type to Nothing, the variable is set to the default value for its declared type. In C#, if you assign a variable of a non-nullable value type to null, a compile-time error occurs.

Are you getting a runtime error or is something happening that you were not expecting?
0
 

Author Comment

by:Cobra967
ID: 41803892
No, no error at all, the record is saved in SQL, however it always apply the Else statement so there is something in the IF statement that is never fulfilled regardless of the value... either way I don't really want to save a either. I can't believe how complicated this is becoming...
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803904
Try modifying your code like this also make sure your first line of the if is exactly like below.
Dim MyTotal As Nullable(Of Integer)
Dim Filed1 As Nullable(Of Integer)
Dim Field2 As Nullable(Of Integer)

If (Not String.IsNullOrWhiteSpace(BoxTotal.Text)) AndAlso IsNumeric(BoxTotal.Text) AndAlso BoxTotal.Text > 0 Then
    MyTotal = Integer.Parse(BoxTotal.Text)
Else
    MyTotal = Nothing
End If

'' The same for these
If Field1 ...
             
If Filed2 ...

Open in new window

Also you will need to modify these line of code as follows.
cmd.Parameters.AddWithValue("@Field1", If(Field2.HasValue, Field2.Value, DBNull.Value))
cmd.Parameters.AddWithValue("@Field2", If(Field2.HasValue, Field2.Value, DBNull.Value))
cmd.Parameters.AddWithValue("@MyTotal", If(MyTotal.HasValue, MyTotal.Value, DBNull.Value))

Open in new window

0
 

Author Comment

by:Cobra967
ID: 41803911
On you last posting for all 3 lines, I am getting the squiggly red lines stating that : MyFiled.HasValue and MyFiled.Value 'Is not a member of 'Ineteger'
0
 

Author Comment

by:Cobra967
ID: 41803920
Also, making just the first group modifications from 3 Messages above,  on Line 18 from my full code above (13 messages above): ... Save(con, Field1, Field2, MyTotal)

I am, getting the following error message:

System.InvalidOperationException was unhandled by user code
  HResult=-2146233079
  Message=Nullable object must have a value.
  Source=mscorlib
  StackTrace:
       at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
       at System.Nullable`1.get_Value()
       at Test.MyReport.SaveButton_Click(Object sender, EventArgs e) in
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803921
In the Subroutine Save signature, Private Sub Save(con As SqlConnection, Field1 As Integer, Field2 As Integer, MyTotal As Integer), change all the Integer to Nullable(Of Integer).
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803925
Any time you try to retrieve values from Field1, Field2, MyTotal you will need to be sure that the variable has a value and if it does not have a value to NOT try and retrieve a value from it. So you will need to do something like this, "If(Field2.HasValue, Field2.Value, DBNull.Value)", If(Test Variable, If True do this, If False do this). So to access the value in Field2 use this Field2.Value, note in the False part I do not access the variable.
0
 

Author Comment

by:Cobra967
ID: 41803926
We are making progress Fernando! Now it does not save the 0 anymore in SQL Great! However, it still does not detect when I enter a number in that field and grab it. It always skip to the Else statement. There most be something in the IF statement that disqualify the value
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803927
Read my last post.
0
 

Author Comment

by:Cobra967
ID: 41803931
Are you saying to modify the If Then Else statement or the cmd.Parameters.AddWithValue.....?
0
 

Author Comment

by:Cobra967
ID: 41803934
It works!!!!!! I forgot to remove a previously created If then else statement. Fernando YOU are the man! Thank you for your patience.
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41803935
This is what you should have.
Dim MyTotal As Nullable(Of Integer)
Dim Filed1 As Nullable(Of Integer)
Dim Field2 As Nullable(Of Integer)

If (Not String.IsNullOrWhiteSpace(BoxTotal.Text)) AndAlso IsNumeric(BoxTotal.Text) AndAlso BoxTotal.Text > 0 Then
    MyTotal = Integer.Parse(BoxTotal.Text)
Else
    MyTotal = Nothing
End If

'' The same for these
If Field1 ...
             
If Filed2 ...

Open in new window

and this.
Private Sub Save(con As SqlConnection, Field1 As Nullable(Of Integer), Field2 As Nullable(Of Integer), MyTotal As Nullable(Of Integer))
...
cmd.Parameters.AddWithValue("@Field1", If(Field2.HasValue, Field2.Value, DBNull.Value))
cmd.Parameters.AddWithValue("@Field2", If(Field2.HasValue, Field2.Value, DBNull.Value))
cmd.Parameters.AddWithValue("@MyTotal", If(MyTotal.HasValue, MyTotal.Value, DBNull.Value))

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803937
Not a problem Cobra967, glad to help.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803938
Please don't forget to close the question. Thanks.
0
 

Author Comment

by:Cobra967
ID: 41803944
For everyone's benefit -thanks to Fernando - the working code is:

Protected Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
Dim MyTotal As Nullable(Of Integer)
Dim Filed1 As Nullable(Of Integer)
Dim Field2 As Nullable(Of Integer)

Using con As SqlConnection = GetSqlConnection()

If (Not String.IsNullOrWhiteSpace(BoxTotal.Text)) AndAlso IsNumeric(BoxTotal.Text) AndAlso BoxTotal.Text > 0 Then
    MyTotal = Integer.Parse(BoxTotal.Text)
Else
    MyTotal = Nothing
End If

'' The same for these
If Field1 ...
             
If Filed2 ...

Save(con, Field1, Field2, MyTotal)
End Using

Private Sub Save(con As SqlConnection, Field1 As Nullable(Of Integer), Field2 As Nullable(Of Integer), MyTotal As Nullable(Of Integer))
        Using cmd As New System.Data.SqlClient.SqlCommand
            cmd.CommandType = System.Data.CommandType.Text
            cmd.CommandText = "INSERT INTO MYTABLE(Field1, Field2 , MyTotal) " &
                "VALUES (@Field1, @Field2, @MyTotal);"
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@Field1", If(Field2.HasValue, Field2.Value, DBNull.Value))
            cmd.Parameters.AddWithValue("@Field2", If(Field2.HasValue, Field2.Value, DBNull.Value))
            cmd.Parameters.AddWithValue("@MyTotal", If(MyTotal.HasValue, MyTotal.Value, DBNull.Value))
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        End Using

Private Shared Function GetSqlConnection() As SqlConnection
        Dim constring As String = ConfigurationManager.ConnectionStrings("DefaultConnection").ConnectionString
        Dim con As New SqlConnection(constring)
        Return con
End Function

Open in new window

0
 

Author Closing Comment

by:Cobra967
ID: 41803946
YOU are the BEST!
0
 

Author Comment

by:Cobra967
ID: 41803973
Fernando, I don't know if I am allowed to ask questions after closing a ticket. How do I implement the same concept when dealing with Strings instead of numbers so I don't end up with some black space in SQL?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41803978
Please open a new question seeming it would not be a follow up to this specific question and give the particulars.

Thanks.
0
 

Author Comment

by:Cobra967
ID: 41803990
Done!
0

Featured Post

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

696 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