CODEWITHSUNDEEP
sqlvb.nett-sqlinsertprimary-key
steve
steve

Reputation: 123

What is the more efficient way to check if primary key is already used? VB.net SQL

Which is the better way to check if the primary key is already in use?

 Dim sql As String = "Insert into Z_SKm_Funktion_Mapping (Funktion_CSV ,Funktion_Property) values('" + MappingTextbox.Text + "','" + PropertyBox.SelectedItem + "') "
        Console.WriteLine(sql)

        Dim connectionString As String = My.Settings.SLXADRIUMDEVConnectionString

        Dim connection As New SqlConnection(connectionString)
        Dim dataadapter As New SqlDataAdapter(Sql, connection)

        Try
            connection.Open()
            dataadapter.InsertCommand = New SqlCommand(sql, connection)
            dataadapter.InsertCommand.ExecuteNonQuery()
            connection.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

I have two solutions in mind, but I don't know if anyone of them is best.

First one: I could just delete the MsgBox statement in the Catch block. Then it just runs through the code normally and does not insert the Statement.

Second one: I Create a Select Statement which checks if the Primary Key is already in use.

By the way: In this case Funktion_CSV is the PK

Upvotes: 0

Views: 537

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

The best efficient way is that your primary key should be Auto-increment (with increment and seed value), and let SQL decide what will be the next value of insert records. To do this you can use IDENTITY column with Auto-increment PK in your table

If your code decides the PK values then there is the chance that multiple people doing the insert then conflict occurs and that's result in failure of insertion of records.

If you are very specific that your codes needs to decide PK(not recommended) value then better to Check first before insert and do this in Transaction So when multiple user try to insert a single time, then first records insert successfully but other records handled in catch block

Upvotes: 3

Related Questions