dcsimg
Results 1 to 4 of 4
  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2016
    Posts
    60

    avoid duplicate row in my database

    Hello guys i would like to avoid duplicate row data in my SQL database i'd like to know how to do so. I'd like to receive an error message if i add a data that already exists.. Thanx for your help

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,932

    Re: avoid duplicate row in my database

    We will need to know a lot more details to be able to help, such as:
    • What method(s) you are using to work with the database (eg: Dataset, or Command objects, etc).
    • What you consider to be a duplicate, as people mean different things when they say that (as well as a clear explanation, it helps to show us some rows of similar data, including a duplicate - preferably with "other" fields being different).
    • Whether your database table has a primary key.
    • It would help to see some of the code you are using to work with the database.

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,141

    Re: avoid duplicate row in my database

    If you want to do it at the database level, which you probably should even if you do it at the application level too, then you can add a unique index to a column or combination of columns in SQL Server. If you then try to insert a duplicate value or set of values then an exception will be thrown, which can be caught in your app.

    If you want to do it at the application level then you would need to perform a query to see if there is already a record with that value or set of values. If there's no match then you can do the insert.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Member
    Join Date
    Dec 2016
    Posts
    60

    Re: avoid duplicate row in my database

    My database table has a primary unique key, there is a sample of code how i add data row in my DB.

    Code:
      Private Sub BtnAjouter_Click(sender As Object, e As EventArgs) Handles BtnAjouter.Click
            Dim strCat As String = Replace(TextCat.Text, "'", "'")
            Dim strDesc As String = Replace(TextDesc.Text, "'", "'")
    
            If TextCat.Text = "" Then
                MsgBox("Ce champ est vide, veuillez saisir le nom de la catégorie", MsgBoxStyle.Critical, "Ajout nom catégorie")
                TextCat.Focus()
                Exit Sub
            End If
    
            If TextDesc.Text = "" Then
                MsgBox("Ce champ est vide, veuillez entrer la description", MsgBoxStyle.Critical, "Ajout description")
                TextDesc.Focus()
                Exit Sub
            End If
    
            Dim query As String = String.Empty
            query &= "INSERT INTO Categorie (NomCategorie,Description)"
            query &= "VALUES (@NomCategorie,@Description)"
    
            'Using conn As New SqlConnection("Data Source=OKOUELE\SQLEXPRESS;Initial Catalog=Gestock;Integrated Security=True")
            Using conn As New SqlConnection("Data Source=192.168.1.2;Database=Gestock1;Uid=sa;Pwd=Admin123")
                Using comm As New SqlCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = query
                        .Parameters.AddWithValue("@NomCategorie", strCat)
                        .Parameters.AddWithValue("@Description", strDesc)
                    End With
                    Try
                        conn.Open()
                        comm.ExecuteNonQuery()
                    Catch ex As Exception
                        MessageBox.Show(ex.Message.ToString(), "Error Message")
                    End Try
    
                End Using
            End Using
            MsgBox("Enregistrement effectué avec succès", MsgBoxStyle.Critical, "Message")
            SetUpDataGridView1()
            TextCat.Text = ""
            TextDesc.Text = ""
            TextCat.Focus()
        End Sub
    Ahd there is how i charge my Datagrid

    Code:
     Private Sub SetUpDataGridView1(Optional ByVal TheFilter As String = "")
    
            TheSql = "Select IDcategorie as ID,NomCategorie as Categorie,Description From Categorie"
    
            'MyConnexion = New SqlConnection("Data Source=OKOUELE\SQLEXPRESS;Initial Catalog=Gestock;Integrated Security=True")
            MyConnexion = New SqlConnection("Data Source=192.168.1.2;Database=Gestock1;Uid=sa;Pwd=Admin123")
            'definition du DataAdapter
            AdapT = New SqlDataAdapter(TheSql, MyConnexion)
            'on vide le dataset
            DtSet.Clear()
            ' remplissage du dataAdapter 
            AdapT.Fill(DtSet, Thetable)
            'Remplissage de la grille
            MaTable = DtSet.Tables(Thetable)
    
            With DataGridCat.ColumnHeadersDefaultCellStyle
                .BackColor = Color.DarkOrange
                .ForeColor = Color.Black
                .Font = New Font(DataGridCat.Font, FontStyle.Bold)
            End With
    
            With DataGridCat
    
                .DataSource = Nothing
                .DataSource = MaTable
                .BackgroundColor = Color.AliceBlue
                .Columns("Categorie").Width = 100
                .Columns("Description").Width = 350
                .DefaultCellStyle.Font = New Font("Tahoma", 8)
            End With
        End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width