Violation of UNIQUE KEY constraint Cannot insert duplicate key in object

Question

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

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)
)

Solution 1

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

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

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

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

Dim count As Integer

    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()

Solution 2

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..

Solution 3

I suppose that your field BatchGrid identifies 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_BatchSteps fields, a rude, but effective way to correctly rewrite this block of records is to remove every entry that refers to the same BatchGrid and then reinsert everything from your ListView items

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

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