Solved

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

Posted on 2016-09-18
29
32 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 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