Solved

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

Posted on 2016-09-18
29
25 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 62

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
 
LVL 62

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 62

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 62

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 62

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 62

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 62

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 62

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 62

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 62

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 62

Expert Comment

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

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 62

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ASP.NET e-commerce website 4 28
asp.net bundle 8 36
Need to Modify a Script I found 5 77
Re-position the objects 7 50
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now