insertion in a database without repetition

[231166]

Hi,

I would like to permit a user to insert in a treeview a new term .This new term will be inserted in a table called 'TERMES'.
the problem is that the same term has not to be inserted twice in the database.
The user types the new term in a inputbox called 'strLibelle' and after, the program compares the text inserted in the input box to all the terms's label of the TERMES table( field Lib_TERME).
The TERMES table created with the dataset is called also TERMES
Here is the code

For Each drTerme As DataRow In objDS.Tables("TERMES").Rows
                If strLibelle.ToLower <> drTerme.Item("Lib_TERME") Then

                     strSQLInsertTerme = "INSERT INTO TERMES(Lib_TERME,EM,ID_THES) VALUES ('" & strLibelle & "','0'," & intNumThes.ToString & ")"
                    objInsertTerme = New SqlCommand(strSQLInsertTerme, objConn)
                    objInsertTerme.ExecuteNonQuery()
                Else
                                        MessageBox.Show("this term already exists in the database", "Ajout d'un nouveau terme", MessageBoxButtons.OK)

                End If

                 Next

the problem is that the insertion in the database is done each time the comparison between the Lib_Terme in the table TERMES and the content of strLibelle gives a result = true(strLibelle<>"Lib_TERME"), that means that if i have 50 terms in the database, ithe insertion of the new term is done 50 times!!!

Could you help me to correct this to make the insertion only one time in the database.

Thanks a lot for all your help.

Best regards

Nathalie

 

[Ruffnekk]

You are taking the wrong approach here. You loop through each existing datarow and check if the current term is different from the user entered term. If this is not so then the term is added. This means that a new term will be added every time an existing term is not the same, that is the reason why a term is added so many times.

What you should do is loop through each record and see if the existing term matches the entered term. If none of them match then you should add the new term.

Some code to illustrate:

Dim termExists As Boolean =  False

For Each drTerme As DataRow In objDS.Tables("TERMES").Rows
  If strLibelle.ToLower = drTerme.Item("Lib_TERME") Then termExists = True
Next

If Not termExists Then
  strSQLInsertTerme = "INSERT INTO TERMES(Lib_TERME,EM,ID_THES) VALUES ('" & strLibelle & "','0'," & intNumThes.ToString & ")"                 objInsertTerme = New SqlCommand(strSQLInsertTerme, objConn)
                    objInsertTerme.ExecuteNonQuery()
Else
  MessageBox.Show("this term already exists in the database", "Ajout d'un nouveau terme", MessageBoxButtons.OK)
End If

I hope that solves your problem.

Regards, Ruffnekk
---
Is it true that cannibals don't eat clowns because they taste funny?