VB.NET组件保存/编辑报错：Input string was not a correct format 求助

Hey there, let's break down why you're hitting that frustrating format error and walk through how to fix it for good.

问题根源分析 #

Your hunch is totally right—this error is definitely coming from those Convert.ToDecimal(txtComMiniW.Text) lines. Here's the breakdown:

• Convert.ToDecimal is strict: If the text box is empty, has non-numeric characters, or uses a decimal separator that doesn't match your system's default culture (like a period when your system expects a comma, or vice versa), it throws that format error immediately.
• Users often accidentally type extra spaces, special characters, or leave fields blank without noticing—all of which break the conversion.
• Unlike more forgiving methods, Convert.ToDecimal doesn't give you a chance to catch invalid input before it blows up.

解决方案：用安全的Decimal.TryParse替代 #

The best fix here is to switch to Decimal.TryParse. This method checks if the input can be converted to a Decimal, returns a boolean success flag, and only populates the Decimal value if the conversion works. We'll also add user-friendly validation to let people know when they enter bad data.

步骤1：重构转换逻辑 #

Replace all your Convert.ToDecimal lines with this pattern. We'll also use CultureInfo.InvariantCulture to avoid issues with regional decimal separators (adjust this if your app uses a specific region, like en-US or fr-FR):

' First define variables to hold converted values
Dim miniW, miniD, miniH, miniU, miniQ, localCost, lastCost, profitMargin, price As Decimal

' Validate and convert each field—fail fast with user feedback
If Not Decimal.TryParse(txtComMiniW.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniW) Then
    MsgBox("Please enter a valid number for Minimum Width", MsgBoxStyle.Exclamation, "Invalid Input")
    txtComMiniW.Focus()
    Return
End If

If Not Decimal.TryParse(txtComMiniD.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniD) Then
    MsgBox("Please enter a valid number for Minimum Depth", MsgBoxStyle.Exclamation, "Invalid Input")
    txtComMiniD.Focus()
    Return
End If

' Repeat this pattern for all remaining Decimal fields: miniH, miniU, miniQ, localCost, lastCost, profitMargin, price

' Then assign the converted values to your parameters
cmdCompro.Parameters.Add(New OleDbParameter("@MiniW", OleDbType.Decimal)).Value = miniW
cmdCompro.Parameters.Add(New OleDbParameter("@MiniD", OleDbType.Decimal)).Value = miniD
' ... and so on for the rest

步骤2：额外优化建议 #

• Trim input always: Use .Trim() on text box values to eliminate accidental leading/trailing spaces before conversion.
• Fix SQL Injection Risk: In your Update statements, you're concatenating txtComCod.Text directly into the SQL string (like Where Com_Code = '" & txtComCod.Text & "'). This is a major security hole—replace it with a parameter just like your other values:

  ' Update your SQL string
  ComNamDis = "Update ComNamDis set Com_Code = @Com_Code, Com_Name = @Com_Name, Com_Discreption = @Com_Discreption Where Com_Code = @OriginalComCode"
  ' Add the new parameter
  cmdComNamDis.Parameters.Add(New OleDbParameter("@OriginalComCode", OleDbType.VarChar)).Value = txtComCod.Text
  

• Optional default values: If some fields are allowed to be empty, you can set a default value (like 0) instead of failing, but make sure this aligns with your business rules.

完整修改后的关键代码片段 #

Here's how the updated ButtonComSave_Click event will look with all the fixes:

If msg = MsgBoxResult.Yes Then
    Try
        Dim cmdComNamDis As New OleDbCommand(ComNamDis, Conn)
        cmdComNamDis.Parameters.Add(New OleDbParameter("@Com_Code", OleDbType.VarChar)).Value = txtComCod.Text
        cmdComNamDis.Parameters.Add(New OleDbParameter("@Com_Name", OleDbType.VarChar)).Value = txtComNam.Text
        cmdComNamDis.Parameters.Add(New OleDbParameter("@Com_Discreption", OleDbType.VarChar)).Value = txtComDis.Text
        
        ' Initialize Decimal variables for conversion
        Dim miniW, miniD, miniH, miniU, miniQ, localCost, lastCost, profitMargin, price As Decimal
        
        ' Validate each Decimal field
        If Not Decimal.TryParse(txtComMiniW.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniW) Then
            MsgBox("Invalid value for Minimum Width. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComMiniW.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComMiniD.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniD) Then
            MsgBox("Invalid value for Minimum Depth. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComMiniD.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComMiniH.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniH) Then
            MsgBox("Invalid value for Minimum Height. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComMiniH.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComMiniU.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniU) Then
            MsgBox("Invalid value for Minimum Unit. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComMiniU.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComMiniQ.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, miniQ) Then
            MsgBox("Invalid value for Minimum Quantity. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComMiniQ.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComLocalCost.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, localCost) Then
            MsgBox("Invalid value for Local Cost. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComLocalCost.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComLastCost.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, lastCost) Then
            MsgBox("Invalid value for Last Cost. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComLastCost.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComProfitMargin.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, profitMargin) Then
            MsgBox("Invalid value for Profit Margin. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComProfitMargin.Focus()
            Return
        End If
        
        If Not Decimal.TryParse(txtComPrice.Text.Trim(), Globalization.NumberStyles.Any, Globalization.CultureInfo.InvariantCulture, price) Then
            MsgBox("Invalid value for Price. Please enter a valid number.", MsgBoxStyle.Exclamation, "Input Error")
            txtComPrice.Focus()
            Return
        End If
        
        Conn.Open()
        cmdComNamDis.ExecuteNonQuery()
        
        Dim cmdCompro As New OleDbCommand(ComPro1 & ComPro2 & ComPro3, Conn)
        cmdCompro.Parameters.Add(New OleDbParameter("@Com_Code", OleDbType.VarChar)).Value = txtComCod.Text
        cmdCompro.Parameters.Add(New OleDbParameter("@ComTyp", OleDbType.VarChar)).Value = ComboBoxComTyp.Text
        cmdCompro.Parameters.Add(New OleDbParameter("@PriCat_Code", OleDbType.VarChar)).Value = ComboBoxPriCat.ValueMember
        cmdCompro.Parameters.Add(New OleDbParameter("@SubCat_Code", OleDbType.VarChar)).Value = ComboBoxSubCat.ValueMember
        cmdCompro.Parameters.Add(New OleDbParameter("@StoUni_Code", OleDbType.VarChar)).Value = ComboBoxComStoUni.ValueMember
        cmdCompro.Parameters.Add(New OleDbParameter("@CalTyp_Code", OleDbType.VarChar)).Value = ComboBoxComCalcolation.ValueMember
        cmdCompro.Parameters.Add(New OleDbParameter("@CosTyp_Code", OleDbType.VarChar)).Value = ComboBoxComCostTyp.ValueMember
        
        ' Assign converted values to parameters
        cmdCompro.Parameters.Add(New OleDbParameter("@MiniW", OleDbType.Decimal)).Value = miniW
        cmdCompro.Parameters.Add(New OleDbParameter("@MiniD", OleDbType.Decimal)).Value = miniD
        cmdCompro.Parameters.Add(New OleDbParameter("@MiniH", OleDbType.Decimal)).Value = miniH
        cmdCompro.Parameters.Add(New OleDbParameter("@MiniU", OleDbType.Decimal)).Value = miniU
        cmdCompro.Parameters.Add(New OleDbParameter("@MiniQ", OleDbType.Decimal)).Value = miniQ
        cmdCompro.Parameters.Add(New OleDbParameter("@Local_Cost", OleDbType.Decimal)).Value = localCost
        cmdCompro.Parameters.Add(New OleDbParameter("@Last_Cost", OleDbType.Decimal)).Value = lastCost
        cmdCompro.Parameters.Add(New OleDbParameter("@Profit_Margin", OleDbType.Decimal)).Value = profitMargin
        cmdCompro.Parameters.Add(New OleDbParameter("@Price", OleDbType.Decimal)).Value = price
        
        cmdCompro.ExecuteNonQuery()
        
        If ButtonComSave.Text = "Save" Then
            MsgBox("The component has been saved successfully :) ", MsgBoxStyle.Information, "Confirm")
        Else
            MsgBox("The component has been Edited successfully :) ", MsgBoxStyle.Information, "Confirm")
        End If
        ButtonComNew_Click(sender, e)
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        Conn.Close()
    End Try
Else
    Exit Sub
End If

内容的提问来源于stack exchange，提问作者Rahhal