为什么会出现“数据类型转换错误"?使用 ExecuteNonQuery()?

2021-09-27 00:00:00 sql vb.net sql-server winforms

为什么我在运行此代码时会出现将 varchar 转换为浮点数的错误?

Why do I get an error converting varchar into float conversion when I run this code?

我不明白，请帮忙.

Imports System.Data.SqlClient

Public Class Form1
    Dim selected As Double
    Dim HourItem As Double
    Dim OverTimeRate As Double
    Dim connection As New SqlConnection("Data Source=DESKTOP-F55AVQ6\SQLEXPRESS;Initial Catalog=Db_Suncrest_Financial;Integrated Security=True")
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'Db_Suncrest_FinancialDataSet.Tb_Suncrest_Financial_Payroll' table. You can move, or remove it, as needed.
    Me.Tb_Suncrest_Financial_PayrollTableAdapter.Fill(Me.Db_Suncrest_FinancialDataSet.Tb_Suncrest_Financial_Payroll)

End Sub

Private Sub Employee_PositionComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Employee_PositionComboBox.SelectedIndexChanged
    Select Case Employee_PositionComboBox.SelectedItem
        Case "Banker"
            selected = 14.75
        Case "Bank Teller"
            selected = 10
        Case "Loan Processor"
            selected = 17.1
        Case "Mortgage Consultant"
            selected = 19.22
        Case "Investment Representative"
            selected = 19.31
        Case "Credit Analyst"
            selected = 19.88
        Case "Investment Banker"
            selected = 22.75
        Case "Relationship Manager"
            selected = 23.85
        Case "Financial Adviser"
            selected = 23.99
        Case "Financial Analyst"
            selected = 25.84
        Case "Asset Manager"
            selected = 26.86
        Case "Under Writer"
            selected = 27.56
        Case "Internal Auditor"
            selected = 28.79
        Case "Investment Banking Analyst"
            selected = 39.37
        Case "Loan Officer"
            selected = 43.18
    End Select

    Rate_Per_HourTextBox.Text = selected
End Sub

Private Sub Hours_WorkedComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles Hours_WorkedComboBox.SelectedIndexChanged
    Select Case Hours_WorkedComboBox.SelectedItem
        Case "8"
            HourItem = 8
        Case "7"
            HourItem = 7
        Case "6"
            HourItem = 6
        Case "5"
            HourItem = 5
        Case "4"
            HourItem = 4
    End Select
End Sub

Private Sub OverTime_RateComboBox_SelectedIndexChanged(sender As Object, e As EventArgs) Handles OverTime_RateComboBox.SelectedIndexChanged
    Select Case OverTime_RateComboBox.SelectedItem
        Case "Normal Day"
            OverTimeRate = 1.25
        Case "Rest Day"
            OverTimeRate = 1.69
        Case "Special Non-Working Hoiday"
            OverTimeRate = 1.69
        Case "Special Non-Working Hoiday + Rest Day"
            OverTimeRate = 1.95
        Case "Regular Holliday"
            OverTimeRate = 2.6
        Case "Regular Holliday + Rest Day"
            OverTimeRate = 3.38
    End Select
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Basic_PayTextBox.Text = HourItem * selected * 30
    OverTime_PayTextBox.Text = OverTimeRate * selected * OverTime_HoursTextBox.Text
    Gross_SalaryTextBox.Text = OverTime_PayTextBox.Text + Basic_PayTextBox.Text



    Total_Salary_W_TaxTextBox.Text = Gross_SalaryTextBox.Text * (0.0034 + 0.0363 + 0.01375)

    Date_TimeTextBox.Text = Format(Now, "General Date")


End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

    connection.Open()
    Dim command As New SqlCommand("Insert into Tb_Suncrest_Financial_Payroll(Employee_Id_No, Employee_Name, Employee_Position, Residence, Date_Time, Hours_Worked, Rate_Per_Hour, 
                                   Basic_Pay, OverTime_Hours, OverTime_Rate, OverTime_Pay, Gross_Salary, SSS, Pagibig, Philhealth, Total_Salary_W_Tax) 
                                   Values(
                                   '" & Employee_Id_NoTextBox.Text & "', '" & Employee_NameTextBox.Text & "', '" & Employee_PositionComboBox.Text & "', '" & ResidenceTextBox.Text & "', '" & Date_TimeTextBox.Text & "',
                                   '" & Hours_WorkedComboBox.Text & "', '" & Rate_Per_HourTextBox.Text & "', '" & Basic_PayTextBox.Text & "', '" & OverTime_HoursTextBox.Text & "', '" & OverTime_RateComboBox.Text & "',
                                   '" & OverTime_PayTextBox.Text & "', '" & Gross_SalaryTextBox.Text & "', '" & SSSTextBox.Text & "', '" & PagibigTextBox.Text & "', '" & PhilhealthTextBox.Text & "', '" & Total_Salary_W_TaxTextBox.Text & "')", connection)



    command.Parameters.AddWithValue("@Employee_Id_No", SqlDbType.Int)
    command.Parameters.AddWithValue("@Employee_Name", SqlDbType.VarChar)
    command.Parameters.AddWithValue("@Employee_Position", SqlDbType.VarChar)
    command.Parameters.AddWithValue("@Residence", SqlDbType.VarChar)
    command.Parameters.AddWithValue("@Date_Time", SqlDbType.DateTime)
    command.Parameters.AddWithValue("@Hours_Worked", SqlDbType.Int)
    command.Parameters.AddWithValue("@Rate_Per_Hour", SqlDbType.Float)
    command.Parameters.AddWithValue("@Basic_Pay", SqlDbType.Float)
    command.Parameters.AddWithValue("@OverTime_Hours", SqlDbType.Float)
    command.Parameters.AddWithValue("@OverTime_Rate", SqlDbType.Float)
    command.Parameters.AddWithValue("@OverTime_Pay", SqlDbType.Float)
    command.Parameters.AddWithValue("@Gross_Salary", SqlDbType.Float)
    command.Parameters.AddWithValue("@SSS", SqlDbType.Float)
    command.Parameters.AddWithValue("@Pagibig", SqlDbType.Float)
    command.Parameters.AddWithValue("@Philhealth", SqlDbType.Float)
    command.Parameters.AddWithValue("@Total_Salary_W_Tax", SqlDbType.Float)


    If command.ExecuteNonQuery() = 1 Then
        MessageBox.Show("Added")
    Else
        MessageBox.Show("Not Added")
    End If
    connection.Close()
End Sub

End Class

推荐答案

以下是@Charlieface 出色回答提出的想法的示例.

Here is an example of the ideas presented by @Charlieface excellent answer.

另外几点.

ID 字段是身份字段是很常见的(自动编号) 检查您的数据库.如果是这种情况，那么您将不会在字段列表中包含 Employee_ID，值列表将任何内容添加到该字段的参数集合中.在您的情况下，这似乎是一个工资表，因此 Employee_ID 可能是其他地方员工表的外键.在这种情况下，代码没问题.

It is very common for ID fields to be identity fields (auto-number) Check your database. If this is the case then you would not include Employee_ID in the fields list, Values list of add anything to the parameters collection for that field. In your case this appears to be a payroll table so perhaps Employee_ID is a foreign key to an employees table elsewhere. In this case, the code is fine.

用户很容易输入任何TextBox.我强烈建议您在尝试插入之前使用 .TryParse 验证数字和日期的所有输入.

Users are apt to enter anything is a TextBox. I strongly suggest that you validate all the input for numbers and dates with .TryParse before attempting the insert.

不要在连接打开时显示消息框.连接是宝贵的资源，应该在 .Execute... 之前直接打开，并通过 End Using 尽快关闭.

Never present a message box while a connection is open. Connections are precious resources and should be opened directly before the .Execute... and closed as soon as possible with End Using.

似乎有几个计算字段根本不需要存储，但存储很便宜，而且可能会使其他查询更简单.

There appears to be several calculated fields that do not need to be stored at all but storage is cheap and it will probably make other queries simpler.

Private ConStr As String = "Your connection string"

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim RetVal As Integer
    Dim Sql = "Insert into Tb_Suncrest_Financial_Payroll(Employee_Id_No, Employee_Name, Employee_Position, Residence, Date_Time, Hours_Worked, Rate_Per_Hour, 
                               Basic_Pay, OverTime_Hours, OverTime_Rate, OverTime_Pay, Gross_Salary, SSS, 
                               Pagibig, Philhealth, Total_Salary_W_Tax) 
                               Values(
                               @Employee_Id_No, @Employee_Name, @Employee_Position, @Residence, @Hours_Worked,@Rate_Per_Hour, 
                               @Basic_PayTextBox, @OverTime_Hours, @OverTime_Rate, @Gross_Salary, @SSS,
                               @Pagibig, @Philhealth, @Total_Salary_W_Tax);"
    Using connection As New SqlConnection(ConStr),
            command As New SqlCommand(Sql, connection)
        With command.Parameters
            .Add("@Employee_Id_No", SqlDbType.Int).Value = CInt(Employee_Id_NoTextBox.Text)
            .Add("@Employee_Name", SqlDbType.VarChar).Value = Employee_NameTextBox.Text
            .Add("@Employee_Position", SqlDbType.VarChar).Value = Employee_Position.Text
            .Add("@Residence", SqlDbType.VarChar).Value = ResidenceTextBox.Text
            .Add("@Date_Time", SqlDbType.DateTime).Value = CDate(Date_TimeTextBox.Text)
            .Add("@Hours_Worked", SqlDbType.Int).Value = CInt(Hours_WorkedComboBox.Text)
            .Add("@Rate_Per_Hour", SqlDbType.Float).Value = CDbl(Rate_Per_HourTextBox.Text)
            .Add("@Basic_Pay", SqlDbType.Float).Value = CDbl(Basic_PayTextBox.Text)
            .Add("@OverTime_Hours", SqlDbType.Float).Value = CDbl(OverTime_HoursTextBox.Text)
            .Add("@OverTime_Rate", SqlDbType.Float).Value = CDbl(OverTime_RateComboBox.Text)
            .Add("@OverTime_Pay", SqlDbType.Float).Value = CDbl(OverTime_PayTextBox.Text)
            .Add("@Gross_Salary", SqlDbType.Float).Value = CDbl(Gross_SalaryTextBox.Text)
            .Add("@SSS", SqlDbType.Float).Value = CDbl(SSSTextBox.Text)
            .Add("@Pagibig", SqlDbType.Float).Value = CDbl(PagibigTextBox.Text)
            .Add("@Philhealth", SqlDbType.Float).Value = CDbl(PhilhealthTextBox.Text)
            .Add("@Total_Salary_W_Tax", SqlDbType.Float).Value = CDbl(Total_Salary_W_TaxTextBox.Text)
        End With
        connection.Open()
        RetVal = command.ExecuteNonQuery
    End Using
    If RetVal = 1 Then
        MessageBox.Show("Added")
    Else
        MessageBox.Show("Not Added")
    End If
End Sub

相关文章