Verify that there are no duplicates before doing INSERT

Question

Verify that there are no duplicates before doing INSERT

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (0 votes)

1

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) 
Rs.Close() 
MiConexion.Close() 
If lblBuscaNumDoc.Text = 0 Then 
    conectar.sqlconecta() 
    conectar.creadocumento()

sql-server vb6

asked by Pancho 24.07.2017 в 20:00

source

3 answers

How can I create an element in the DOM from a php echo Avoid screen lock on Android and VR

score 2 · Answer 1

2

Use the following:

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

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

score 0 · Answer 2

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

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

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

score 0 · Answer 3

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
Else
    '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 .