vb.net 违反唯一键约束无法在对象中插入重复键

Violation of UNIQUE KEY constraint Cannot insert duplicate key in object

提问by kay-B

------------------------here is my sql procedue to update table------------------------

-------------------------这里是我更新表的sql程序----------------- ------

create procedure sp_stepUpdate
@ID int,
@StepOrder int

AS
BEGIN  


IF OBJECT_ID('tempdb.dbo.#UpdateBatch','u') IS NOT NULL
begin
DROP TABLE #UpdateBatch
end

IF OBJECT_ID('tempdb.dbo.#UpdateBatch2','u') IS NOT NULL
begin
DROP TABLE #UpdateBatch2
end

create table #UpdateBatch2
(
ID int,
StepOrder int
)

insert into #UpdateBatch2 values (@ID,@StepOrder)

Select *,ROW_NUMBER() OVER(ORDER BY ID) as newIID into #UpdateBatch
from #UpdateBatch2

set identity_insert [ODM_BatchSteps] ON

Update [ODM_BatchSteps]
set [StepOrder] = newIID
From #UpdateBatch 
where [ODM_BatchSteps].ID = #UpdateBatch.ID

set identity_insert [ODM_BatchSteps] off
END
go

---------------and here is my code in the program to get the new order from the list------

---------------这是我在程序中从列表中获取新订单的代码------

    connection.Open()


    For Each item As ListViewItem In ListView1.Items

        Dim command As SqlCommand = New SqlCommand("sp_stepUpdate", connection)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("@ID", item.SubItems(0).Text)
        command.Parameters.AddWithValue("@StepOrder", item.SubItems(1).Text)
        command.ExecuteNonQuery()
    Next

i get Violation of UNIQUE KEY constraint when tryin to update the table with the new order from my listview

尝试使用列表视图中的新订单更新表时，我收到违反唯一键约束的情况

-----------here is the the table the order iam trying to update-----

-----------这是我试图更新的订单表-----

    create table [dbo].[ODM_BatchSteps]
(
[ID] uniqueidentifier primary key not null default newid(),
[StepOrder]int ,
[StepType]int,
[StepGrid]nvarchar(max),
[BatchGrid]int,
foreign key (BatchGrid) REFERENCES ODM_Batches(ID)
)

采纳答案by kay-B

Ok thanks for the help guys much much appreciated... took the easy way out and added count to add what number row the new order is, replaced it with the old StepOrder in the DB from a new table

好的，感谢您的帮助，非常感谢...采取简单的方法并添加计数以添加新订单的行数，将其替换为新表中数据库中的旧 StepOrder

-------------------- sql update---------------------

-------------------- sql 更新--------------------

Update [ODM_BatchSteps]
set ODM_BatchSteps.StepOrder = UpdateBatch2.StepOrder
From UpdateBatch2 
where [ODM_BatchSteps].ID = UpdateBatch2.ID

---------------- code in program ---------------------

---------------- 程序中的代码 ---------------------

Dim count As Integer

Dim 计数为整数

    For Each item As ListViewItem In ListView1.Items
        count = count + 1
        Dim command As SqlCommand = New SqlCommand("sp_stepUpdate", connection)
        command.CommandType = CommandType.StoredProcedure

        command.Parameters.AddWithValue("@ID", item.SubItems(0).Text)
        command.Parameters.AddWithValue("@StepOrder", count)
        command.Parameters.AddWithValue("@StepType", item.SubItems(2).Text)
        command.Parameters.AddWithValue("@StepGrid", item.SubItems(3).Text)
        command.Parameters.AddWithValue("@BatchGrid", item.SubItems(4).Text)        
        command.ExecuteNonQuery()

    Next


        connection.Close()

回答by PitAttack76

The error means that you're trying to insert a key value (id?) that already exists in the database. I only see one insert statement, so you'de better check what values you pass to it..

该错误意味着您正在尝试插入数据库中已存在的键值（id？）。我只看到一个插入语句，所以你最好检查一下你传递给它的值。

回答by Steve

I suppose that your field BatchGrididentifies a group of records to be kept in a particular order.
If this is the case and there are no foreign keys that refer to your ODM_BatchStepsfields, a rude, but effective way to correctly rewrite this block of records is to remove every entry that refers to the same BatchGridand then reinsert everything from your ListView items

我想您的字段BatchGrid标识了一组要以特定顺序保存的记录。
如果是这种情况并且没有引用您的ODM_BatchSteps字段的外键，正确重写此记录块的粗鲁但有效的方法是删除引用相同的每个条目，BatchGrid然后重新插入 ListView 项目中的所有内容

Dim tran as SqlTransaction
Try
    connection.Open()
    tran = connection.BeginTransaction()
    Dim command As SqlCommand = new SqlCommand("DELETE FROM ODM_BatchSteps WHERE BatchGrid = @grd", connection, tran)
    command.Parameters.AddWithValue("@grd", currentGrid)
    command.ExecuteNonQuery()
    For Each item As ListViewItem In ListView1.Items

        ' Now we INSERT every item in the grid passing the parameters 
        ' required to rebuild the block of records for the same BatchGrid
        command = New SqlCommand("usp_stepInsert", connection, tran)
        command.CommandType = CommandType.StoredProcedure
        command.Parameters.AddWithValue("@ID", item.SubItems(0).Text)
        command.Parameters.AddWithValue("@StepOrder", item.SubItems(1).Text)
        command.Parameters.AddWithValue("add the other parameters to rebuild the record")
        command.ExecuteNonQuery()
    Next
    tran.Commit()
Catch Ex as Exception
    ' Log the exception, message to user ???
    tran.RollBack
End Try

Of course your sp_stepUpdate should be renamed and rewritten (usp_stepInsert?) to accept all the parameters required to INSERT a new record in the correct step order

当然，您的 sp_stepUpdate 应该重命名和重写（usp_stepInsert？）以接受以正确的步骤顺序插入新记录所需的所有参数

If this is a viable approach, then you could try to boost performance using a Table Valued Parameterinstead of making a separate call to the database for every item

如果这是一种可行的方法，那么您可以尝试使用表值参数来提高性能，而不是为每个项目单独调用数据库