• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

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

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
Cobra967
Asked:
Cobra967
  • 16
  • 13
1 Solution
 
Fernando SotoCommented:
Is that value in the database nullable? Most likely not.
0
 
Cobra967Author Commented:
In SQL database that field data type is Int and it does allow Null Values: ([MyTotal] [int] NULL,)
0
 
Cobra967Author Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Fernando SotoCommented:
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
 
Cobra967Author Commented:
I just did :-) In my last posting it happen on code line #8
0
 
Cobra967Author Commented:
On that line I get: System.InvalidCastException {"Conversion from string """" to type 'Double' is not valid."}
0
 
Fernando SotoCommented:
Well in that case BoxTotal.Text must be empty or spaces in it. Are you placing a numeric value in it?
0
 
Cobra967Author Commented:
It is empty. Here the use has the option to enter a number or leave it empty.
0
 
Fernando SotoCommented:
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
 
Cobra967Author Commented:
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
 
Fernando SotoCommented:
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
 
Cobra967Author Commented:
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
 
Fernando SotoCommented:
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
 
Cobra967Author Commented:
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
 
Cobra967Author Commented:
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
 
Fernando SotoCommented:
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
 
Fernando SotoCommented:
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
 
Cobra967Author Commented:
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
 
Fernando SotoCommented:
Read my last post.
0
 
Cobra967Author Commented:
Are you saying to modify the If Then Else statement or the cmd.Parameters.AddWithValue.....?
0
 
Cobra967Author Commented:
It works!!!!!! I forgot to remove a previously created If then else statement. Fernando YOU are the man! Thank you for your patience.
0
 
Fernando SotoCommented:
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
 
Fernando SotoCommented:
Not a problem Cobra967, glad to help.
0
 
Fernando SotoCommented:
Please don't forget to close the question. Thanks.
0
 
Cobra967Author Commented:
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
 
Cobra967Author Commented:
YOU are the BEST!
0
 
Cobra967Author Commented:
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
 
Fernando SotoCommented:
Please open a new question seeming it would not be a follow up to this specific question and give the particulars.

Thanks.
0
 
Cobra967Author Commented:
Done!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now