[RESOLVED] Update Code

[abdallahr]

Hi,

I have this code which works fine but I have an error, I understand the cause of the error but do not know how to resolve it...

Violation of Primary key Constraint 'PK_TblCollections'. Cannot insert duplicate key in object 'gprdsql.TblCollections'. The Statement has been terminated

 Private Sub Update_TblCollections()

        Dim query As String
        Dim cmd As New SqlCommand
        Dim conn As SqlConnection = GetDbConnection()
        Dim conn1 As SqlConnection = GetDbConnection()
        Dim conn2 As SqlConnection = GetDbConnection()

        Dim NewCol_uid As New System.Data.SqlClient.SqlCommand(("Select col_uid From QryNextCol"), conn)

        Try
            Using reader As System.Data.SqlClient.SqlDataReader = NewCol_uid.ExecuteReader()

                While reader.Read()
                    Dim Col_uid As Integer = reader.GetValue(0)



                    Dim Auto_Req As New System.Data.SqlClient.SqlCommand(("Select prac_no, audit_start, media_type From QryAutoRequest"), conn1)

                    Try
                        Using Autoreader As System.Data.SqlClient.SqlDataReader = Auto_Req.ExecuteReader()

                            While Autoreader.Read()

                                Dim prac_no As Integer = Autoreader.GetValue(0)
                                Dim audit_start As Integer = Autoreader.GetValue(1)
                                Dim media_type As String = FixNull(Autoreader.GetValue(2))

                                Try

                                    query = "INSERT INTO gprdsql.TblCollections (Prac_no, col_uid, col_type, col_date, media_type, audit_start, user_name, stage, system_time, current_collection) VALUES ('" & _
                                                            prac_no & "', '" & _
                                                            (Col_uid + 1) & "', '" & _
                                                            "IDC" & "', '" & _
                                                            "01/01/2025" & "', '" & _
                                                            media_type & "', '" & _
                                                            audit_start & "', '" & _
                                                            "VDT" & "', '" & _
                                                            "Request" & "', '" & _
                                                            TimeStamp.Text & "', '" & _
                                                            "Yes" & "')"


                                    cmd = New SqlCommand(query, conn2)
                                    cmd.ExecuteNonQuery()

                                Catch ex As Exception
                                    MsgBox(ex.Message, MsgBoxStyle.Information, "ZipUtility")
                                End Try


                            End While
                        End Using

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Information, "ZipUtility")
                    End Try


                End While
            End Using

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "ZipUtility")


        Finally
        End Try

    End Sub

Explanation:

- I have a table 'gprdsql.TblCollections' which am inserting details such as prac_no, col_uid, IDC etc. On this table the col_uid is set as the PK.

- Now the col_uid is obtained by the code;

    Dim NewCol_uid As New System.Data.SqlClient.SqlCommand(("Select col_uid From QryNextCol"), conn)

            Using reader As System.Data.SqlClient.SqlDataReader = NewCol_uid.ExecuteReader()

                While reader.Read()
                    Dim Col_uid As Integer = reader.GetValue(0)

So if I have ONLY one record to insert to 'gprdsql.TblCollections' it worked fine. However, if I have 3 records then I receive the error. I think it remembers the col_uid and then when it reached the INSERT statement the same col_uid is already entered in the first record and hence violation the PK property.

So if I have 3 records the FIRST record will be added successfully.. But the error will be prompted and the other 2 records will fail to be inserted.

Please any help to make sure that all the three records are added without violation of the col_uid..

Many thanks

[Grimfort]

(Col_uid + 1) & "', '" & _

^^ here you are passing the Col_uid variable + 1. You are not actually adding 1 to the variable itself, the 2nd loop will fail as you are still + 1 to the original number, you need to change it before you use it:

Col_uid += 1
query = "INSERT INTO gprdsql.TblCollections (Prac_no, col_u ...
...

(Col_uid) & "', '" & _

2 things to note.
You must not have turned on Option Strict (see my sig) as you are converting a number into a string, this is bad, m'kay.

Why are you manually getting the next unique ID? Databases often have an autonumber type field that does this counting for you.

[abdallahr]

Thanks - Resolved