bonny bonny - 1 year ago 84 Question

Failed to convert parameter value from string to decimal(when field is empty)

I got a problem which is half done.

Now the problem is I wrote

Using and mssql 2014 ent as backend

This is my Query I wrote

cmd = New SqlCommand(("insert into FAMPAR(Open_Bal, Curr_Bal, Disc_Perc, Stop_Days, Fix_Days) values(@Open_Bal , @Curr_Bal , @Disc_Perc , @Stop_Days ,@Fix_Days)"), con1)
cmd.Parameters.Add("Open_Bal", SqlDbType.Decimal).Value = txtOpeningBal.Text
cmd.Parameters.Add("Curr_Bal", SqlDbType.Decimal).Value = txtCurrBal.Text
cmd.Parameters.Add("Disc_Perc", SqlDbType.Decimal).Value = txtDisc.Text
cmd.Parameters.Add("Stop_Days", SqlDbType.Decimal).Value = txtStopDays.Text
cmd.Parameters.Add("Fix_Days", SqlDbType.Decimal).Value = txtBillOutstdg.Text

Now everything works fine when I write in every field in vbform but when I left one field empty then it creates exception.

SystemformatException: Failed to convert parameter value from a string
to a decimal...

Now it shows
so is it error in system side or is it error sql side...???

And I have to keep boxes empty sometimes and sometimes filled(no problem when text boxes are filled, query executes successfully), so what can I do with this???

Answer Source

You should put a validation in your code to check if the value is empty or not. You can use the Decimal.TryParse Method See link. If the value is empty, assign '0' value in the text. From the example in the link:

If Decimal.TryParse(txtOpeningBal.Text, number) Then cmd.Parameters.Add("Open_Bal", SqlDbType.Decimal).Value = number Else cmd.Parameters.Add("Open_Bal", SqlDbType.Decimal).Value = DBNull.value End If

Note: The above was just an example.