-1

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
Mych
  • 2,527
  • 4
  • 36
  • 65
olaseun28
  • 5
  • 1
  • 1
  • 3

1 Answers1

3

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) Calculate the latest 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) Use Auto Increment on TransactionID of dbo.Vehicle_Transactions

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)

Other solutions might be found throughout StackOverflow

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

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.

Community
  • 1
  • 1
User999999
  • 2,500
  • 7
  • 37
  • 63
  • Thanks for this solution! However I want to avoid duplicate records with this solution there's no how I can avoid such because maxPK+1 will increament the pry key by 1 and increase duplicate records. I want to eliminate duplicate record while uploading records I have node of records across metropolitan which will be uploaded to the central server for accurate record keeping. Kindly be help. Thanks – olaseun28 Jun 26 '14 at 09:40
  • Both solution avoid duplicate records. The first key will search the maximum key already present (beyond that key (ID) there are no records any more) and increment it with every insert. The second solution is one delivered with SQL Server itself (and my personal preffered way). The determination of the PK is completly handled by MSSQL Server and it will avoid double PK's – User999999 Jun 26 '14 at 10:03
  • 1
    *SELECT MAX(TransactionID) FROM dbo.Vehicle_Transactions* This doesn't take concurrency into account. If two processes run at the same time, they can both read the same `MAX(TransactionID)` value before inserting. So you'll end up with one of the processes succeeding and one of the processes failing. Just use `IDENTITY` or `SEQUENCE` or a natural key. – ta.speot.is Jun 27 '14 at 04:21