Conversion from String "" to type Deicmal is not valid

Question

I'm having an error saying Conversion from String to type Decimal is not Valid, everytime I Save or Update my Data from the Database. I don't know where's the problem

Here's my code:

Private Sub txtmRate_TextChanged(sender As Object, e As EventArgs) Handles txtmRate.TextChanged
    txtmRate.Text = Format(CDbl(txtmRate.Text), "#,##0.00")
End Sub   'For Textbox Change

 Private Sub cmdSave_Click(sender As Object, e As EventArgs) Handles cmdSave.Click

    If txtPos.Text = "" Or txtDept.Text = "" Or txtmRate.Text = "" Then
        MessageBox.Show("Please fill up all the details!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    Else
        If MessageBox.Show("Do you really want to add this new data?", "Save!", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) = vbYes Then
            sqlqry = "INSERT INTO tblPosition(WorkPosition,Department,MonthlyRate) VALUES (@wPos,@Dept,@mRate)"
            Try
                ConnDB()
                cmd = New OleDbCommand(sqlqry, cnn)
                cmd = cnn.CreateCommand
                With cmd
                    .Connection = cnn
                    .CommandText = sqlqry
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@wPos", OleDbType.VarWChar = 50).Value = txtPos.Text
                    .Parameters.AddWithValue("@Dept", OleDbType.VarWChar = 50).Value = txtDept.Text
                    .Parameters.AddWithValue("@mRate", OleDbType.Currency = 10).Value = txtmRate.Text
                    .ExecuteNonQuery()
                End With
                MessageBox.Show("New Data successfully added!", "Save!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                RefreshData()
                cmd.Dispose()
                ctrl_clear()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
            End Try
        End If
    End If
End Sub  'For Saving Record 

Code for Updating Record

Private Sub cmdUpdate_Click(sender As Object, e As EventArgs) Handles cmdUpdate.Click

    If txtPos.Text = "" Or txtDept.Text = "" Or txtmRate.Text = "" Then
        MessageBox.Show("Please fill the required fields!", "Update!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    Else
        If MessageBox.Show("Do you really want to Update Information! Continue?", "Update", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Dim sqlqry As String = "UPDATE tblPosition SET [WorkPosition]=@wPos, [Department]=@Dept, [MonthlyRate]=@mRate WHERE [CounterID]=@cID"
            Try
                ConnDB()
                cmd = cnn.CreateCommand
                With cmd
                    .Connection = cnn
                    .CommandText = sqlqry
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@wPos", OleDbType.VarWChar = 100).Value = txtPos.Text
                    .Parameters.AddWithValue("@Dept", OleDbType.VarWChar = 100).Value = txtDept.Text
                    .Parameters.AddWithValue("@mRate", OleDbType.Decimal = 10).Value = txtmRate.Text
                    .Parameters.AddWithValue("@cID", OleDbType.Guid = 5).Value = txtCountID.Text
                    .ExecuteNonQuery()
                End With
                MessageBox.Show("Position Information has been updated!", "Update!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
                cmd.Dispose()
                RefreshData()
                ctrl_clear()
                btn_eAddSaveCancel()
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
            End Try
        End If
    End If
End Sub  ' For Updating existing Data

Answer 1

I change my textchange event to this:

    Dim aNumber As Double
    If Double.TryParse(txtmRate.Text, aNumber) Then
        txtmRate.Text = String.Format("{0:n2}", aNumber)
    End If

It resolves my problem, Thanks for the help BTW.

Answer 2

Try converting your txtmRate text box valur to decimal before binding it's value in the dB update statement. Something like this should work

Parameters.AddWithValue("@mRate", OleDbType.Decimal = 10).Value = Convert.ToDecimal( txtmRate.Text)

Answer 3

here you have to change only this line..

.Parameters.AddWithValue("@mRate", OleDbType.Currency = 10).Value = Convert.ToDecimal(txtmRate.Text)

And Code For Updating ...

.Parameters.AddWithValue("@mRate", OleDbType.Decimal = 10).Value = Convert.ToDecimal(txtmRate.Text)

In Both cases you pass the parameter with type decimal and textbox returns the varchar. so you have to convert varchar to decimal.

its definitely work for you..