Input string was not in correct format | decimal/integer to "NULL"-CodePudding

I've recently started learning .net and I wrote a basic sql connected data entry form. Almost all of my data types are integer or decimals. Sometimes I need to remain empty the textboxes and enter "NULL" data and I receive with this error "Input string was not in correct format".

How I can fix this error ? Key point; I don't want to enter "0" it must be "NULL" in sql server because I use some charts to track all of these data, so when there is data equal the "0" its ruined my charts. As far as I understand from my researches Tryparse is fit for it but I couldn't find any information to use it properly in .net.

I'll share my transformation code below.

Try
    Dim command As New SqlCommand()

    command.Parameters.Add("strokeTB", SqlDbType.Decimal).Value = Decimal.Parse(strokeTB.Text)
    command.Parameters.Add("kafaCB", SqlDbType.Int).Value = Integer.Parse(kafaCB.Text)
    command.Parameters.Add("kaynakboyTB", SqlDbType.Decimal).Value = Decimal.Parse(kaynakboyTB.Text)
    command.Parameters.Add("kkbTB", SqlDbType.Decimal).Value = Decimal.Parse(kkbTB.Text)
    command.Parameters.Add("kkbitTB", SqlDbType.Decimal).Value = Decimal.Parse(kkbitTB.Text)
    command.Parameters.Add("op20TB", SqlDbType.Decimal).Value = Decimal.Parse(op20TB.Text)
    command.Parameters.Add("rf1TB", SqlDbType.Int).Value = Integer.Parse(rf1TB.Text)
    command.Parameters.Add("nihaiTB", SqlDbType.Int).Value = Integer.Parse(nihaiTB.Text)

    c.CUD(command, sql)
    list()
    clear()
    MessageBox.Show("Data Successfully Saved")

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

CodePudding user response：

command.Parameters.Add("strokeTB", SqlDbType.Decimal).Value = If(strokeTB.TextLength = 0, CObj(DBNull.Value), Decimal.Parse(strokeTB.Text))

This assumes that you have already validated the contents of the TextBox and it is guaranteed to either be empty or contain a valid Decimal value.

This If operator is similar to the old IIf function but it's better because it short-circuits, i.e. the third argument operand is only evaluated if the first operand is False. Because IIf is a function, it evaluates all three arguments first, so Decimal.Parse would still be called and it would fail. Using If, Decimal.Parse will only be called if there is text to parse.

CodePudding user response：

Why you have to enter "NULL" in the TextBoxes at all? If it's empty it should be enough. That's horrible user experience. When you send it to the database you have to assign DBNull.Value. It's easy with Decimal.TryParse or Int32.TryParse to validate the user input.

Dim stroke As Decimal? = Nothing
Dim kafa As Int32? = Nothing
Dim kaynakboy As Decimal? = Nothing
Dim kkb As Decimal? = Nothing
Dim kkbit As Decimal? = Nothing
Dim op20 As Decimal? = Nothing
Dim rf1 As Int32? = Nothing
Dim nihai As Int32? = Nothing

Decimal.TryParse(strokeTB.Text, stroke)
Int32.TryParse(kafaCB.Text, kafa)
Decimal.TryParse(kaynakboyTB.Text, kaynakboy)
Decimal.TryParse(kkbTB.Text, kkb)
Decimal.TryParse(kkbitTB.Text, kkbit)
Int32.TryParse(op20TB.Text, op20)
Int32.TryParse(rf1TB.Text, rf1)
Decimal.TryParse(nihaiTB.Text, nihai)

' empty spaces are valid, means the user does not want to enter a value, so it must be saved as null
If Not stroke.HasValue AndAlso Not String.IsNullOrWhiteSpace(strokeTB.Text) Then
    MessageBox.Show("Enter a valid stroke")
    Return
End If
' and so on with other values ... '

Try
    Dim command As New SqlCommand()

    command.Parameters.Add("strokeTB", SqlDbType.Decimal).Value = If(stroke.HasValue, stroke.Value, CObj(DBNull.Value))
    command.Parameters.Add("kafaCB", SqlDbType.Int).Value = If(kafa.HasValue, kafa.Value, CObj(DBNull.Value))
    command.Parameters.Add("kaynakboyTB", SqlDbType.Decimal).Value = If(kaynakboy.HasValue, kaynakboy.Value, CObj(DBNull.Value))
    command.Parameters.Add("kkbTB", SqlDbType.Decimal).Value = If(kkb.HasValue, kkb.Value, CObj(DBNull.Value))
    command.Parameters.Add("kkbitTB", SqlDbType.Decimal).Value = If(kkbit.HasValue, kkbit.Value, CObj(DBNull.Value))
    command.Parameters.Add("op20TB", SqlDbType.Decimal).Value = If(op20.HasValue, op20.Value, CObj(DBNull.Value))
    command.Parameters.Add("rf1TB", SqlDbType.Int).Value = If(rf1.HasValue, rf1.Value, CObj(DBNull.Value))
    command.Parameters.Add("nihaiTB", SqlDbType.Int).Value = If(nihai.HasValue, nihai.Value, CObj(DBNull.Value))
    ' ... '
    MessageBox.Show("Data Successfully Saved")

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

If you want to keep the "NULL"-logic it's simple: Replace the String.IsNullOrWhiteSpace with StringComparer.OrdinalIgnoreCase.Equals("NULL",strokeTB.Text).