vb.net 命令执行期间的致命错误（MySQL、VBNET）

Fatal error during command execution (MySQL, VBNET)

提问by Fvcundo

I have a form that have multiple fields. And this form can Add,Edit,Save and Update records. But the Update button gives me error of "Fatal error during command execution" which is I dont know how to find the error in my codes. Here is my codes.

我有一个包含多个字段的表单。这个表单可以添加、编辑、保存和更新记录。但是更新按钮给了我“命令执行期间致命错误”的错误，这是我不知道如何在我的代码中找到错误。这是我的代码。

 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As    System.EventArgs) Handles btnUpdate.Click
    conn = New MySqlConnection
    conn.ConnectionString = "server=localhost; userid=root; password=root;   database=dbase"

    Try

        conn.Open()
        Sql = "UPDATE dbase.tblfacultyinfo SET firstname = @firstname, " & _
                                        "middlename = @middlename," & _
                                        "lastname = @lastname," & _
                                        "gender = @gender," & _
                                        "birthdate = @birthdate," & _
                                        "age = @age," & _
                                        "emailAdd = @emailAdd," & _
                                        "contact_mobileno = @contact_mobileno," & _
                                        "contact_telno = @contact_telno," & _
                                        "homeadd_houseno = @homeadd_houseno," & _
                                        "homeadd_street = @homeadd_street," & _
                                        "homeadd_brgy = @homeadd_brgy," & _
                                        "homeadd_town = @homeadd_town," & _
                                        "tersiary_schoolname = @tersiary_schoolname," & _
                                        "tersiary_address = @tersiary_address," & _
                                        "tersiary_degree = @tersiary_degree," & _
                                        "tersiary_batch = @tersiary_batch," & _
                                        "secondary_schoolname = @secondary_schoolname," & _
                                        "secondary_address = @secondary_address," & _
                                        "secondary_batch = @secondary_batch" & _
                                        "WHERE facultyNo = @facultyNo"
        With cmd
            .Connection = conn
            .CommandText = Sql
            .CommandType = CommandType.Text
            .Parameters.AddWithValue("@facultyNo", txtFacultyNo.Text)
            .Parameters.AddWithValue("@firstname", txtFirstname.Text)
            .Parameters.AddWithValue("@middlename", txtMiddlename.Text)
            .Parameters.AddWithValue("@lastname", txtLastname.Text)
            .Parameters.AddWithValue("@gender", cbGender.Text)
            .Parameters.AddWithValue("@birthdate", dtpBirthdate.Value)
            .Parameters.AddWithValue("@age", txtAge.Text)
            .Parameters.AddWithValue("@emailAdd", txtEmailAdd.Text)
            .Parameters.AddWithValue("@contact_mobileno", txtContact_Mobile.Text)
            .Parameters.AddWithValue("@contact_telno", txtContact_Tel.Text)
            .Parameters.AddWithValue("@homeadd_houseno", txtHomeAdd_HouseNo.Text)
            .Parameters.AddWithValue("@homeadd_street", txtHomeAdd_Street.Text)
            .Parameters.AddWithValue("@homeadd_brgy", txtHomeAdd_Brgy.Text)
            .Parameters.AddWithValue("@homeadd_town", txtHomeAdd_Town.Text)
            .Parameters.AddWithValue("@tersiary_schoolname", txtTersiary_Schoolname.Text)
            .Parameters.AddWithValue("@tersiary_address", txtTersiary_Add.Text)
            .Parameters.AddWithValue("@tersiary_degree", txtTersiary_Degree.Text)
            .Parameters.AddWithValue("@tersiary_batch", cbBatchTer.Text)
            .Parameters.AddWithValue("@secondary_schoolname", txtSecondary_Schoolname.Text)
            .Parameters.AddWithValue("@secondary_address", txtSecondary_Add.Text)
            .Parameters.AddWithValue("@secondary_batch", cbBatchSec.Text)
        End With
        dr = cmd.ExecuteReader
        MsgBox("Data Updated", vbInformation, "Successfully Update Record")
        conn.Close()


    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        conn.Dispose()
        buttons("ResetButtons")
        DataGridView1.Enabled = True
    End Try
End Sub

any help is appreciated. Thx in advance.

任何帮助表示赞赏。提前谢谢。

回答by Steve

In your syntax there is an error. Between the last parameter and the WHERE clause a space is missing.

在您的语法中有一个错误。在最后一个参数和 WHERE 子句之间缺少一个空格。

 "secondary_batch = @secondary_batch" & _
 " WHERE facultyNo = @facultyNo"
  ^

This is a simple typo but I would also suggest to avoid the use of AddWithValue.

这是一个简单的错字，但我也建议避免使用 AddWithValue。

AddWithValue decides the DataType of the parameter passed looking at the datatype of the value passed in. Because all your parameters are created using strings then all the fields of your table should be able to receive strings as value or someone between your code and the database should be able to convert from string to the expected datatype .

AddWithValue 决定传递参数的数据类型，查看传入值的数据类型。因为所有参数都是使用字符串创建的，所以表的所有字段都应该能够接收字符串作为值，或者代码和数据库之间的某个人应该能够从字符串转换为预期的数据类型。

In this article 'Can we stop using AddWithValue() already?'is explained why AddWithValue should be avoided (or at least used with extreme care).

在本文中“我们可以停止使用 AddWithValue() 了吗？” 解释了为什么应该避免使用 AddWithValue（或至少要极其小心地使用）。

For example, your field Age is probably a numeric datafield. So, instead of AddWithValue I would use

例如，您的字段 Age 可能是一个数字数据字段。所以，我会使用而不是 AddWithValue

Dim age as Integer
if Int32.TryParse(txtAge.Text, age) = False Then
    // Error message and exit from the save
....

cmd.Parameters.Add(New SqlParameter("@age", SqlDbType.Int)).Value = age

In this way you could specify exactly the datatype of the parameter and you will pass a correct value to your datatable field

通过这种方式，您可以准确指定参数的数据类型，并将正确的值传递给数据表字段

回答by Momodu Deen Swarray

This error is mostly caused by a missing or incorrectly spelled parameter declarations, eg. @FirstNamemistakenly spelled as @FirtName.

此错误主要是由缺少或拼写错误的参数声明引起的，例如。@FirstName错误地拼写为@FirtName。

Make sure that all the parameters that are declared in the SQL query are all declared in the AddwithValue Parameter declaration. (It helps to count the query versus the Addwithvalues).

确保在 SQL 查询中声明的所有参数都在 AddwithValue 参数声明中声明。（它有助于计算查询与 Addwithvalues）。

The best solution is for Visual Studio to provide information about the missing parameter. Use a Try-Catch block. In the catch block use Messagebox.show(ex.Innerexception.Message)instead of Messagebox.show(ex.message). This will show the exactparameter that is missing, eg. below.

最好的解决方案是让 Visual Studio 提供有关缺失参数的信息。使用 Try-Catch 块。在 catch 块中使用Messagebox.show(ex.Innerexception.Message)而不是Messagebox.show(ex.message). 这将显示缺少的确切参数，例如。以下。

Try
      conCommand.Parameters.Addwithvalue("@FirstName", txtFirstName.text)
      conCommand.Parameters.Addwithvalue("@MiddleName", txtMiddleName.text)
      conCommand.Parameters.Addwithvalue("@LastName", txtLastName.text)
      conCommand.Parameters.Addwithvalue("@PhoneNo", txtPhoneno.text) 
catch ex as exception          
Messagebox.show(ex.innerexception.Message)
End Try