Verify that there are no duplicates before doing INSERT


I am programming in Visual Basic with database in SQL Server, and I can not do that when inserting a new record in the database, do not let me do it if the following conditions are repeated:

  • That the record does not have the same number
  • That the record does not have the same year
  • That does not have the same model
  • For example, if I enter a record with the following data: Numero: 1, Año:2017 y Modelo: A enter it, but do not enter it if all the data are repeated, unless one of them changes ... for example, that the number is still 1 and model A , but that the year is 2018 , if you enter it.

    I have the following code, pro only works for 1 condition, in this case the number, but it does not help me to prevent it from registering with the 3 conditions ( numero , año and modelo ):

    SQL = "SELECT COUNT(*) FROM registros WHERE numerodoc = '" & tbxNumDoc.Text & "''" 
    Com = New SqlCommand(SQL, MiConexion) 
    Rs = Com.ExecuteReader() Rs.Read() 
    lblBuscaNumDoc.Text = Rs(0) 
    If lblBuscaNumDoc.Text = 0 Then 
    asked by Pancho 24.07.2017 в 20:00

    3 answers


    Use the following:

    IF NOT EXISTS (SELECT 1 FROM Tabla WHERE Condiciones)
      -- Código de inserción

    This checks if the registry exists, if it does not exist it inserts it also try with IF EXISTS

    answered by 24.07.2017 в 20:13

    Use the if exist statement, as in the following example,

    IF  EXISTS( SELECT campo FROM tabla WHERE campo2 = 'condición')
    delete from tabla where campo = (select campo from Forma where campo2 = 'condición')
      insert ...

    And thus there will be no duplicates, in this way you delete the previous record in order to insert a new one

    answered by 24.07.2017 в 20:23

    You just have to add the conditions in your SQL query so that the criteria can be met:

    SQL = "SELECT COUNT(1) FROM registros WHERE numerodoc = @NumeroDoc AND Anio = @Anio AND Modelo = @Modelo
    Dim cmd As New SqlCommand(sql, connection)
    cmd.Parameters.AddWithValue("@numerodoc ", tbxNumDoc.Text)
    cmd.Parameters.AddWithValue("@Anio", txtAnio.Text)
    cmd.Parameters.AddWithValue("@Modelo", txtModelo.text)
    Rs = cmd.ExecuteScalar()
    If Rs Is Nothing Then
        'Aquí manejas las condiciones para realizar el INSERT
    End If

    Note that I have changed the form of your query, to have dynamic queries is susceptible to SQL injection, to avoid this, you need to parameterize the query or make use of Stored Procedures .

    answered by 24.07.2017 в 20:24