使用 SQL 更新语句更新 VB.Net 数据库

Updating VB.Net Database With SQL Update Statement

提问by ItsSimplyEddie

so I got the update statement working and the record in the database is being updated. However the program is then throwing an error after the record is updated.

所以我得到了更新语句，并且正在更新数据库中的记录。然而，程序在记录更新后抛出错误。

Error

错误

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

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

Additional information: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

附加信息：您请求对表进行的更改未成功，因为它们会在索引、主键或关系中创建重复值。更改包含重复数据的一个或多个字段中的数据，删除索引，或重新定义索引以允许重复条目并重试。

Code

代码

        Dim variable As String
        dsConnectionM.Open()
        variable = "UPDATE Member SET MemberID = '" & Form3.tbid.Text & "', Forename = '" & Form3.tbfn.Text & "' "
        Dim cmd As OleDbCommand = New OleDbCommand(variable, dsConnectionM)
        cmd.ExecuteNonQuery()
        dsConnectionM.Close()

I don't know if this is normal but when ran a second Access file for the database is created

我不知道这是否正常但是当运行数据库的第二个 Access 文件时

This shows the contents of "Member" in the databse

这显示了数据库中“Member”的内容

EDITED

已编辑

I started using Parameters as I was advised now I get another error.

我开始使用参数，因为我现在得到了另一个错误。

ERROR

错误

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

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

Additional information: Syntax error in UPDATE statement.

附加信息：UPDATE 语句中的语法错误。

CODE

代码

Dim variable As String
dsConnectionM.Open()
variable = "UPDATE Member 
            SET Forename = @Forename,
                Surname = @Surname, 
                DOB = @DOB, 
                Section = @Section, 
                Postcode = @Postcode,
                HomeTel = @HomeTel, 
                MobileTel = @MobileTel, 
                AddressLine1 = @AddressLine1, 
                AddressLine2 = @AddressLine2, 
                City = @City, 
                EmailAddress = @EmailAddress, 
                P/GForename = @P/GForename, 
                P/GSurname = @P/GSurname
           WHERE MemberID = @MemberID"
        Dim cmd As OleDbCommand = New OleDbCommand(variable, dsConnectionM)
        cmd.Parameters.AddWithValue("@MemberID", Form3.tbid.Text)
        cmd.Parameters.AddWithValue("@Forename", Form3.tbfn.Text)
        cmd.Parameters.AddWithValue("@Surname", Form3.tbsn.Text)
        cmd.Parameters.AddWithValue("@DOB", Form3.dtpdob.Value)
        cmd.Parameters.AddWithValue("@Section", Form3.tbsr.Text)
        cmd.Parameters.AddWithValue("@Postcode", Form3.tbpc.Text)
        cmd.Parameters.AddWithValue("@HomeTel", Form3.tbht.Text)
        cmd.Parameters.AddWithValue("@MobileTel", Form3.tbmt.Text)
        cmd.Parameters.AddWithValue("@AddressLine1", Form3.tbal1.Text)
        cmd.Parameters.AddWithValue("@AddressLine2", Form3.tbal2.Text)
        cmd.Parameters.AddWithValue("@City", Form3.tbc.Text)
        cmd.Parameters.AddWithValue("@EmailAddress", Form3.tbea.Text)
        cmd.Parameters.AddWithValue("@P/GForename", Form3.tbpgfn.Text)
        cmd.Parameters.AddWithValue("@P/GSurname", Form3.tbpgsn.Text)
        cmd.ExecuteNonQuery()
        dsConnectionM.Close()

回答by Juan Carlos Oropeza

Usually you don't UPDATEthe Pkof a table. In your form IDshould be read only. And if someone change some other value, you use the IDto find what row to UPDATE

通常你不会UPDATE在Pk一张桌子上。在您的表单中ID应该是只读的。如果有人更改了其他值，您可以使用ID来查找要更改的行UPDATE

variable = "UPDATE Member " &
           "SET Forename = '" & Form3.tbfn.Text & "' "
           "WHERE MemberID = '" & Form3.tbid.Text & "'

And you need to use paramethers, other wise you are vulnerable to SQL Injection

并且您需要使用参数，否则您很容易受到 SQL 注入的影响

回答by Larsupilami

I assume that your query is meant to change the Forename field of the member having a particular ID.

我假设您的查询旨在更改具有特定 ID 的成员的 Forename 字段。

In this case your query should look like this

在这种情况下，您的查询应如下所示

UPDATE Member SET Forename = '" & Form3.tbfn.Text & "' where MemberID = '" & Form3.tbid.Text & "'