SQL更新查询中的.ExecuteNonQuery()错误 [英] Error on .ExecuteNonQuery() in SQL Update Query

问题描述

我试图使用SQL查询更新Access数据库，每当我单击保存"按钮时，都会产生错误

I'm trying to update an Access database using a SQL query, whenever I click the save button, it generates an error

System.Data.dll中发生了类型为'System.Data.OleDb.OleDbException'的未处理异常

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

其他信息:没有为一个或多个必需参数提供值.

Additional information: No value given for one or more required parameters.

并突出显示.ExecuteNonQuery().你们可以帮我吗?我是vb.net的新手.

And highlights .ExecuteNonQuery(). Can you guys help me on this? I'm new to vb.net.

谢谢.

 Private Sub SaveButton_Click(sender As Object, e As EventArgs) Handles SaveButton.Click
        Dim empNum As String
        Dim empFname As String
        Dim empLname As String
        Dim empDept As String
        Dim empStat As String
        Dim empYears As String



        empNum = eNumText.Text
        empFname = empFnameText.Text
        empLname = empLnameText.Text
        empDept = DeptText.Text
        empStat = StatText.Text
        empYears = yearstext.Text

        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= c:\Databse\Company_db.accdb"
        con.Open()

        MsgBox(empNum)


        Dim SqlAdapter As New OleDbDataAdapter
        Dim Table As New DataTable

        Dim sqlQuery As String = "UPDATE tbl_empinfo SET EmpID='" & empNum & "', FirstName ='" & empFname & "', LastName='" & empLname & "', Department='" & empDept & "', Status='" & empStat & "', Years='" & empYears & "' WHERE EmpID ='" & empNum & "' "


        Using cmd As New OleDbCommand(sqlQuery, con)
            With cmd
                .CommandText = sqlQuery
                .Connection = con
                .Parameters.AddWithValue("EmpID", empNum)
                .Parameters.AddWithValue("FirstName", empFname)
                .Parameters.AddWithValue("LastName", empLname)
                .Parameters.AddWithValue("Department", empDept)
                .Parameters.AddWithValue("Status", empStat)
                .Parameters.AddWithValue("Years", empYears)
                .ExecuteNonQuery()
            End With
        End Using

        sqlQuery = "SELECT * FROM tbl_empinfo "
        Dim cmd1 As New OleDbCommand
        Dim da As New OleDbDataAdapter


        With cmd1
            .CommandText = sqlQuery
            .Connection = con
            With SqlAdapter
                .SelectCommand = cmd1
                .Fill(Table)
            End With
            With DataGridView1
                .DataSource = Table
            End With
        End With

        con.Close()
    End Sub

推荐答案

您的查询语法错误.由于您正在使用参数，因此请在SQL中使用占位符:(问号不是某些'etc'类型的东西，您可以使用?来标记参数！):

your query syntax is wrong. Since you are using params, use placeholders in the SQL: (the question marks are not some 'etc' type thing, you use ? to mark parameters!):

Dim sqlQuery As String = "UPDATE tbl_empinfo SET FirstName = ?, 
             LastName=?, Department=?,
             Status=?, Years=? WHERE empID = ?"

注意:六个参数

' USING will dispose of the cmd when it is done with it
' ...can also set the SQL and connection props in the constructor:
Using cmd As New OleDbCommand(sqlQuery, con)
   With cmd
       ' no reason to move Textboxes to a variable either:
       .Parameters.AddWithValue("@p1", empFnameText.Text)
       .Parameters.AddWithValue("@p2", empLnameText.Text)
       .Parameters.AddWithValue("@p3", DeptText.Text)
       .Parameters.AddWithValue("@p4", StatText.Text)
       .Parameters.AddWithValue("@p5", yearstext.Text)

您缺少的第六个参数:

      .Parameters.AddWithValue("@p6", eNumText.Text)        
      .ExecuteNonQuery()
   End With
 End Using

我不认为Access支持命名参数，因此您使用伪参数，但请确保按SQL字符串中指定的顺序AddWithValue.

I dont think Access supports named params, so you use dummy ones but be sure to AddWithValue in the order specified in the SQL string.

编辑

您可以只创建一个带有嵌入值的SQL字符串，而不使用参数来对SQL字符串进行 sort 排序.参数要好得多(研究SQL注入攻击)，但是您的字符串方法是错误的(并且您不能混合使用方法).应该是:

You can just create a SQL string with the values embedded instead of using params which is sort of what your SQL string does. Params are much better (research SQL injection attacks), but your string method is wrong (and you cant mix methods). It should be:

Dim sqlQuery As String = "UPDATE tbl_empinfo " & 
       "SET FirstName = " & empFname & ", LastName=" & empLname 

变量必须在引号之外，否则您将FirstName设置为文字"empFname"

The variables have to be outside the quotes or you will be setting FirstName to the literal "empFname"

这篇关于SQL更新查询中的.ExecuteNonQuery()错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！