CODEWITHSUNDEEP
sql-servervb.net
bonny
bonny

Reputation: 738

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 vb.net 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
ExecuteQuery1()

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 systemformatException 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???

Upvotes: 0

Views: 1405

Answers (2)

user4267260
user4267260

Reputation:

You simply need to apply Val() function to value before assigning it like bellow

cmd.Parameters.Add("Open_Bal", SqlDbType.Decimal).Value = Val(txtOpeningBal.Text)
cmd.Parameters.Add("Curr_Bal", SqlDbType.Decimal).Value = Val(txtCurrBal.Text)
cmd.Parameters.Add("Disc_Perc", SqlDbType.Decimal).Value = Val(txtDisc.Text)
cmd.Parameters.Add("Stop_Days", SqlDbType.Decimal).Value = Val(txtStopDays.Text)
cmd.Parameters.Add("Fix_Days", SqlDbType.Decimal).Value = Val(txtBillOutstdg.Text)

Upvotes: 1

jelliaes
jelliaes

Reputation: 505

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.

Upvotes: 1

Related Questions