“违反 PRIMARY KEY 约束 'PK_Vehicle_Transactions'.无法在对象“dbo.Vehicle_Transactions"中插入重复键

2021-09-16 00:00:00 vb.net sql-server

我设计了 Webservice api，每次我通过 Webservice 推送数据时，这就是我得到的回报MOV = "违反 PRIMARY KEY 约束 'PK_Vehicle_Transactions'.无法在对象 'dbo.Vehicle_Transactions' 中插入重复键.语句已终止."就像 api 不知道它在哪里停止以及在哪里继续！请在下面查看我的源代码，谢谢

There's Webservice api that I design, Each time I push data cross the webservice this is what I get in return MOV = "Violation of PRIMARY KEY constraint 'PK_Vehicle_Transactions'. Cannot insert duplicate key in object 'dbo.Vehicle_Transactions'. The statement has been terminated." is like the api doesn't know where it stopped and where to continue! kindly see my source code below thanks

Public Sub uploadVehicle_Transaction()
        Try
            'do for sync indacator for proper upload in action
            Dim VT As New DataTable
            VT = New Statn_Sync.DataSetTableAdapters.Vehicle_TransactionsTableAdapter().GetData()

        For Each dr As DataRow In VT.Rows
            Dim iCount As Integer = 0
            Dim MOV As String = comT.insertVehicle_Transaction(Convert.ToInt64(dr("TransactionID")), _
                                                               Convert.ToDateTime(dr("Transaction_date")), _
                                                               Convert.ToInt32(dr("Bank")), _
                                                               Convert.ToString(dr("Teller_number")), _
                                                               Convert.ToInt32(dr("Amount")), _
                                                               Convert.ToString(dr("Generated_by")), _
                                                               Convert.ToString(dr("Station")), _
                                                               Convert.ToString(dr("Customer_name")), _
                                                               Convert.ToInt32(dr("Transaction_category")), _
                                                               Convert.ToString(dr("Deposit_slip")), _
                                                               Convert.ToInt32(dr("Sync")), _
                                                               Convert.ToDecimal(dr("Penalty")), _
                                                               Convert.ToDecimal(dr("OGSG")), _
                                                               Convert.ToDecimal(dr("CMR")), _
                                                               Convert.ToDecimal(dr("Goshen")), _
                                                               Convert.ToDecimal(dr("Insurance")), _
                                                               Convert.ToDecimal(dr("OCost")), _
                                                               Convert.ToDecimal(dr("OGSG_Renewal")), _
                                                               Convert.ToDecimal(dr("De_pulse")))

            iCount += 1
            Label1.Text = " Auto Sync: " & iCount
            'update record
            Dim pls As String = dr("TransactionID").ToString

            If (pls Is MOV) Then
                AddToLog((Convert.ToString(": transferred") & MOV.ToString() & Text) + Environment.NewLine)
                vta.UpdateTrans(dr("TransactionID"))
            End If
        Next

    Catch ex As Exception
        AddToLog(ex.Message.ToString)
    End Try
End Sub

推荐答案

异常已经说明了:违反 PRIMARY KEY 约束 'PK_Vehicle_Transactions'.表中已经包含一行，其中给出了主键 (TransactionID).主键在整个表中是唯一的.

The exception already says it: Violation of PRIMARY KEY constraint 'PK_Vehicle_Transactions'. The table already contains a row with the Primary Key (TransactionID) given. A Primary Key is unique throughout the table.

您的问题有多种解决方案:

There are several solutions for your problem:

1) 计算最新的TransactionID

VT = New Statn_Sync.DataSetTableAdapters.Vehicle_TransactionsTableAdapter().GetData()

//Use query to select Max value of TransactionID (something like)
Dim maxPK as Long =  'SELECT MAX(TransactionID) FROM dbo.Vehicle_Transactions'  

//Increase the MaxPK with 1 to avoid duplicate key
 maxPK = maxPK + 1 

  For Each dr As DataRow In VT.Rows
   Dim iCount As Integer = 0

   //Use our variable in the insert
   Dim MOV As String = comT.insertVehicle_Transaction((maxPK  + iCount), _
                       Convert.ToDateTime(dr("Transaction_date")), _

2) 在 dbo.Vehicle_Transactions

为此，我参考以下帖子:Auto Increment.这篇文章是为MSSQL 2012的管理工作室做的.但同样的逻辑适用于早期版本(2008,2005)

For this i refer to the following post: Auto Increment .This post was made for the management studio of MSSQL 2012. But the same logic applies for earlier version (2008,2005)

可以在 StackOverflow 中找到其他解决方案

Other solutions might be found throughout StackOverflow

如果我能提供任何进一步的帮助，请不要犹豫给我签名！

If i can be of any further assistance, don't hesitate to give me sign!

注意:如果之前的数据对你没有用，你总是可以在插入之前清除表使用查询:DELETE FROM dbo.Vehicle_Transactions此查询从表中删除所有行.尽管您必须警惕任何Forgein Keys，因为它们可能会导致数据丢失/异常.

Note: If the previous data are of no use to you, you can always clear the table prior to the insert using the query: DELETE FROM dbo.Vehicle_Transactionsthis query removes all rows from the table. Though you have to wary for any Forgein Keys as they might cause dataloss/exceptions.

相关文章