none
how to work with duplicated value in database and datagridview RRS feed

  • Question

  • HI everyone

    i have a hard time work with duplicated value in datagridview and in database

    when i want to make update query i can't differe with the first and second value for the specific value

    for the unique key value from database i haven't any idea how to get it from datagridview 

    i have only some column to work with

    this is the column i want to be updated and use only first column wich is 'Numero' and 2nd 'CodeArticle' to target the specifc row in database

    the table in database here is

    For i = 0 To DataGridView1.Rows.Count - 1

    'this is to get data from database and fill it to table(qqs23)' Dim usb As New SqlCommand("select Numseq from DetailReceptionFrs where Numero='" & TextBox1.Text & "' and Codearticle='" & DataGridView1.Rows(i).Cells(1).Value & "'", cn) Dim ttt As New SqlDataAdapter(usb) ttt.Fill(qqs23) If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() 'this where the update methode' Using cmd1 As New SqlCommand("update DetailReceptionFrs set NumeroEnsemble=@NumeroEnsemble,Marge=@Marge where Numero='" & TextBox1.Text & "' and Numseq='" & qqs23.Rows(0).Item("Numseq") & "'", cn) With cmd1.Parameters .Add("@NumeroEnsemble", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(7).Value) .Add("@Marge", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(8).Value) End With cmd1.ExecuteNonQuery() End Using Next

    this is where my problem i spoke for (where Numero='" & TextBox1.Text & "' and Codearticle='" & DataGridView1.Rows(i).Cells(1).Value & "'

    if already exist it will update the first in database not the target one

    please help

    Tuesday, September 17, 2019 4:34 PM

All replies

  • If the identifier is present in the DataGridView all is good else not so good as you can't reference back to the database table.

    I short group by is needed against the columns you are working with either or in the underlying DataSource of the DataGridView or in SQL.

    This is a clear example where I want to get duplications by CompanyName, ContactName,Address,City and PostalCode and return all fields including the primary key against a database table, done in a DataTable you would following the same path but with LINQ or Lambda.

    SELECT  A.* FROM    Customers A 
            INNER JOIN ( SELECT CompanyName , 
                                ContactName , 
                                Address , 
                                City , 
                                PostalCode 
                         FROM   Customers 
                         GROUP BY CompanyName , 
                                ContactName , 
                                Address , 
                                City , 
                                PostalCode 
                         HAVING COUNT(*) > 1 
                       ) B ON A.CompanyName = B.CompanyName 
                              AND A.ContactName = B.ContactName 
                              AND A.Address = B.Address 
                              AND A.City = B.City 
                              AND A.PostalCode = B.PostalCode


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, September 17, 2019 6:06 PM
    Moderator
  • thanks for help

    i have 2 column one called 'BC' and one 'ARTICLE' from those 2 not enough to get the specefic row from the database

    i think there is a column called 'dligne' in database it take the 'BC' on group

    like select row from DOCLIGNE where bc=... and article=... and dligne=...

    like this i can get what i want but

    dligne i haven't and it constituate like specefic bc dligne take 1000 and so on

        bc         article     dligne

      bc15478    aaaaaa      1000

      bc15478     bbbbbb     2000

      bc15478     ccccccc      3000

    if bc changed dligne will return to 1000 like

         bc         article        dligne

      bc15478   dddddd       4000

      bc15479    ffffffffff       1000 

    like this i need to creat those dligne to reach the row i want and bypass problem of duplicated rows

    please help


    • Edited by Houssem12 Wednesday, September 18, 2019 3:12 PM
    Wednesday, September 18, 2019 3:12 PM
  • Houssem, 

    I'm also not an American English speaker and had always trouble finding this.

    The SQL code would use "plain" English, but that is something which does not exist. SQL contains many words not used in the way of SQL for instance in England.

    In SQL is the meaning of "distinct" is one of the duplicates". (Most non Americans use probably the meaning of the French distinct more (distinguée)). 

    However, because it was in SQL server it came also in Linq. 

    Therefore look on Internet for where you can use Distinct in SQL and Linq

    https://www.w3schools.com/sql/sql_distinct.asp


    Success
    Cor

    Wednesday, September 18, 2019 3:44 PM
  • probably distinct used for delete any duplicated value but in my case 

    if its duplicated in database and in datagridview how i manage to update the specific line with the specific value

    now i'am thinking if i can get the unique key from that table i want to reach

    Wednesday, September 18, 2019 4:19 PM
  • probably distinct used for delete any duplicated value but in my case 

    if its duplicated in database and in datagridview how i manage to update the specific line with the specific value

    now i'am thinking if i can get the unique key from that table i want to reach

    Hi Houssem,

    You must make a unique column to perform this mission (i.e make an "ID Column" with PK) so every row in your table will have a specific ID , you will able to update it easily 

    Look at this ( save and update subs )

      Sub SaveFirm()
    
            If txtID.Text = GenerateID("Firm", "ID") Then
                Using cn As New SqlConnection(cs)
                    cn.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = cn
                        cmd.CommandText = "insert into Firm(ID,InvoiceNo,CustomerNo,CustomerName,TotalPayment,SoldDate,PayMethod,PayDate,Payment,Total,TotalDue,Notes_,BCheck,DDate) 
                                           values(@a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8,@a9,@a10,@a11,@a12,@a13,@a14)"
                        With cmd.Parameters
                            .Add("@a1", SqlDbType.Int).Value = Val(txtID.Text)
                            .Add("@a2", SqlDbType.Int).Value = Val(txtInvoiceNo.Text)
                            .Add("@a3", SqlDbType.Int).Value = Val(txtCustomerNo.Text)
                            .Add("@a4", SqlDbType.NVarChar).Value = txtcustomer.Text
                            .Add("@a5", SqlDbType.Float).Value = Val(txtTotalPayment.Text)
                            .Add("@a6", SqlDbType.DateTime).Value = DateTimePicker1.Value
                            .Add("@a7", SqlDbType.NVarChar).Value = cmbMethod.Text
                            .Add("@a8", SqlDbType.DateTime).Value = DateTimePicker4.Value
                            .Add("@a9", SqlDbType.Float).Value = Val(txtPayment.Text)
                            .Add("@a10", SqlDbType.Float).Value = Val(txtTotal.Text)
                            .Add("@a11", SqlDbType.Float).Value = Val(txtTotalDue.Text)
                            .Add("@a12", SqlDbType.NVarChar).Value = txtNotes.Text
                            .Add("@a13", SqlDbType.Float).Value = Val(TextBox1.Text)
                            .Add("@a14", SqlDbType.DateTime).Value = DateTimePicker5.Value
                        End With
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
            End If
    
        End Sub
    
        Sub UpdateFirm()
    
            If txtID.Text <> GenerateID("Firm", "ID") Then
                Using cn As New SqlConnection(cs)
                    cn.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = cn
                        cmd.CommandText = "update Firm 
                                           set  PayMethod=@a6,
                                                PayDate=@a7,
                                                Payment=@a8,
                                                Total=@a9,
                                                TotalDue=@a10,
                                                Notes_=@a11,
                                                BCheck=@a13,
                                                DDate=@a14
                                          where ID=@a1"
    
                        With cmd.Parameters
                            '.Add("@a2", SqlDbType.Int).Value = Val(txtInvoiceNo.Text)
                            '.Add("@a3", SqlDbType.NVarChar).Value = txtcustomer.Text
                            '.Add("@a4", SqlDbType.Int).Value = Val(txtTotalPayment.Text)
                            '.Add("@a5", SqlDbType.DateTime).Value = DateTimePicker1.Value
                            .Add("@a6", SqlDbType.NVarChar).Value = cmbMethod.Text
                            .Add("@a7", SqlDbType.DateTime).Value = DateTimePicker4.Value
                            .Add("@a8", SqlDbType.Float).Value = Val(txtPayment.Text)
                            .Add("@a9", SqlDbType.Float).Value = Val(txtTotal.Text)
                            .Add("@a10", SqlDbType.Float).Value = Val(txtTotalDue.Text)
                            .Add("@a11", SqlDbType.NVarChar).Value = txtNotes.Text
                            .Add("@a13", SqlDbType.Float).Value = Val(TextBox1.Text)
                            .Add("@a14", SqlDbType.DateTime).Value = DateTimePicker5.Value
                            .Add("@a1", SqlDbType.Int).Value = Val(txtID.Text)
                        End With
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
            End If
    
        End Sub
    You must have a unique column to perform this job .... without ID column you will not able to update your table .........
    Hope it helps


    Regards From Egypt

    Wednesday, September 18, 2019 9:11 PM
  • probably distinct used for delete any duplicated value but in my case 

    if its duplicated in database and in datagridview how i manage to update the specific line with the specific value

    now i'am thinking if i can get the unique key from that table i want to reach

    Hi Houssem,

    You must make a unique column to perform this mission (i.e make an "ID Column" with PK) so every row in your table will have a specific ID , you will able to update it easily 

    Look at this ( save and update subs )

      Sub SaveFirm()
    
            If txtID.Text = GenerateID("Firm", "ID") Then
                Using cn As New SqlConnection(cs)
                    cn.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = cn
                        cmd.CommandText = "insert into Firm(ID,InvoiceNo,CustomerNo,CustomerName,TotalPayment,SoldDate,PayMethod,PayDate,Payment,Total,TotalDue,Notes_,BCheck,DDate) 
                                           values(@a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8,@a9,@a10,@a11,@a12,@a13,@a14)"
                        With cmd.Parameters
                            .Add("@a1", SqlDbType.Int).Value = Val(txtID.Text)
                            .Add("@a2", SqlDbType.Int).Value = Val(txtInvoiceNo.Text)
                            .Add("@a3", SqlDbType.Int).Value = Val(txtCustomerNo.Text)
                            .Add("@a4", SqlDbType.NVarChar).Value = txtcustomer.Text
                            .Add("@a5", SqlDbType.Float).Value = Val(txtTotalPayment.Text)
                            .Add("@a6", SqlDbType.DateTime).Value = DateTimePicker1.Value
                            .Add("@a7", SqlDbType.NVarChar).Value = cmbMethod.Text
                            .Add("@a8", SqlDbType.DateTime).Value = DateTimePicker4.Value
                            .Add("@a9", SqlDbType.Float).Value = Val(txtPayment.Text)
                            .Add("@a10", SqlDbType.Float).Value = Val(txtTotal.Text)
                            .Add("@a11", SqlDbType.Float).Value = Val(txtTotalDue.Text)
                            .Add("@a12", SqlDbType.NVarChar).Value = txtNotes.Text
                            .Add("@a13", SqlDbType.Float).Value = Val(TextBox1.Text)
                            .Add("@a14", SqlDbType.DateTime).Value = DateTimePicker5.Value
                        End With
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
            End If
    
        End Sub
    
        Sub UpdateFirm()
    
            If txtID.Text <> GenerateID("Firm", "ID") Then
                Using cn As New SqlConnection(cs)
                    cn.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = cn
                        cmd.CommandText = "update Firm 
                                           set  PayMethod=@a6,
                                                PayDate=@a7,
                                                Payment=@a8,
                                                Total=@a9,
                                                TotalDue=@a10,
                                                Notes_=@a11,
                                                BCheck=@a13,
                                                DDate=@a14
                                          where ID=@a1"
    
                        With cmd.Parameters
                            '.Add("@a2", SqlDbType.Int).Value = Val(txtInvoiceNo.Text)
                            '.Add("@a3", SqlDbType.NVarChar).Value = txtcustomer.Text
                            '.Add("@a4", SqlDbType.Int).Value = Val(txtTotalPayment.Text)
                            '.Add("@a5", SqlDbType.DateTime).Value = DateTimePicker1.Value
                            .Add("@a6", SqlDbType.NVarChar).Value = cmbMethod.Text
                            .Add("@a7", SqlDbType.DateTime).Value = DateTimePicker4.Value
                            .Add("@a8", SqlDbType.Float).Value = Val(txtPayment.Text)
                            .Add("@a9", SqlDbType.Float).Value = Val(txtTotal.Text)
                            .Add("@a10", SqlDbType.Float).Value = Val(txtTotalDue.Text)
                            .Add("@a11", SqlDbType.NVarChar).Value = txtNotes.Text
                            .Add("@a13", SqlDbType.Float).Value = Val(TextBox1.Text)
                            .Add("@a14", SqlDbType.DateTime).Value = DateTimePicker5.Value
                            .Add("@a1", SqlDbType.Int).Value = Val(txtID.Text)
                        End With
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
            End If
    
        End Sub
    You must have a unique column to perform this job .... without ID column you will not able to update your table .........
    Hope it helps


    Regards From Egypt

    I have to ask, why are you using meaningless name for parameters ? Parameter names should reflect the name of the field name e.g. if we have FirstName the parameter name would be @FirstName.

    Also, when performing an insert one should add 

    ; SELECT CAST(scope_identity() AS int)

    Then use ExecuteScalar cast to an integer to get the new primary key.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, September 18, 2019 9:29 PM
    Moderator
  • thanks for help

    but i haven't any unique key to perform the update

    for the fist i was using select bc and article to get the update work 

    and with some test i saw that bc and article can be duplicated in the database

    so now i have hard time to resolve this 

    i will show you what i have column in datagridview that can make me access to datatbase to perform the update

    this is the table in database 

    this DL_Ligne column it can be done in datagridview programaticaly

    it like i said in my fisrt comment

      bc         article     dligne

      bc15478    aaaaaa      1000

      bc15478     bbbbbb     2000

      bc15478     ccccccc      3000

    if bc changed dligne will return to 1000 like

         bc         article        dligne

      bc15478   dddddd       4000

      bc15479    ffffffffff       1000 

    this is my try

     Dim Nligne As Integer = 0
            Dim Jligne As Integer = 0
            For i = 0 To DataGridView1.Rows.Count - 1
                If DataGridView1.Rows(0).Cells(0).Value = DataGridView1.Rows(i).Cells(0).Value Then
                    Nligne += 1000
                    DataGridView1.Rows(i).Cells(1).Value = Nligne
    
    
    
                Else
                    Jligne += 1000
                    DataGridView1.Rows(i).Cells(1).Value = Jligne
                End If
    
    
    
    
            Next
    


    Thursday, September 19, 2019 9:03 AM
  • Also, when performing an insert one should add 

    ; SELECT CAST(scope_identity() AS int)

    Then use ExecuteScalar cast to an integer to get the new primary key.

    i am not understand this 

    please help me how identify a unique key for this database to perforom the update

    Thursday, September 19, 2019 9:08 AM
  • Houssem,

    You're all the time that you have duplicate values in your database. That is normal. 

    But what you want to do with them and that depends completely from what value that is used. 

    If it is an boolean if somebody is married, then you have many duplicates on that column, but that does not mean that it is wrong.

    Therefore tell what you want to achieve. 

    And try to avoid changing things in the datagridview programmatically, that is only a presentation part. 


    Success
    Cor

    Thursday, September 19, 2019 9:55 AM
  • i want to make an update to many column in database "DOCLIGNE" like Qte,Prix etc

    for that i should have a unique key from datagridview to reach same value in datagrid to same value in database

    this is exemple of updating code i use before i noticed the duplication

       Using cmd155 As New SqlCommand("update F_DOCLIGNE set DL_DateBL=@DL_DateBL,DL_Qte=@DL_Qte,DL_QteBC=@DL_QteBC,DL_QteBL=@DL_QteBL,DL_PrixUnitaire=@DL_PrixUnitaire,
    DL_PrixRu=@DL_PrixRu,DL_CMUP=@DL_CMUP,DL_MvtStock=@DL_MvtStock,EU_Qte=@EU_Qte,DL_PUTTC=@DL_PUTTC,DL_MontantHT=@DL_MontantHT,DL_MontantTTC=@DL_MontantTTC where cbMarq='" & dsg3 & "' ", cn)
                                    With cmd155.Parameters
                                        .Add("@DL_DateBL", SqlDbType.SmallDateTime).Value = (dd)
                                        .Add("@DL_Qte", SqlDbType.Real).Value = (var)
                                        .Add("@DL_QteBC", SqlDbType.Real).Value = (var)
                                        .Add("@DL_QteBL", SqlDbType.Real).Value = (var)
                                        .Add("@DL_PrixUnitaire", SqlDbType.Real).Value = (var2)
                                        .Add("@DL_PrixRu", SqlDbType.Real).Value = (var2)
                                        .Add("@DL_CMUP", SqlDbType.Real).Value = (var2)
                                        .Add("@DL_MvtStock", SqlDbType.SmallInt).Value = (0)
                                        .Add("@EU_Qte", SqlDbType.Real).Value = (var)
                                        .Add("@DL_PUTTC", SqlDbType.Real).Value = (var2)
                                        .Add("@DL_MontantHT", SqlDbType.Real).Value = (var3)
                                        .Add("@DL_MontantTTC", SqlDbType.Real).Value = (var3)
                                    End With
                                    Try
                                        cmd155.ExecuteNonQuery()
    
                                    Catch ex As Exception
                                        MessageBox.Show(ex.ToString)
                                    End Try
    
                                End Using

    this is the unique 

    cbMarq='" & dsg3 & "' 

    i used bc and article to get this unique key from the database "DOCLIGNE" after that i put it to another database"DETAILRECEPTION" that i work with it in datagaridview to calculate and so on

    i have a column called Numseq "DETAILRECEPTION"

    when bc and article are the same in "DOCLIGNE" it take that cbmarq value and inserted in "DETAILRECEPTION" in Numseq Column

    like so ,

    after when i want to update i can make it just call numseq and go to the "DOCLIGNE" to that line and make the update

    with duplication problem it will not work

    so what i can do

    please help any suggestion will make my life easier

    Thursday, September 19, 2019 10:12 AM
  • this is the code to get the unique key from docligne 

    and used like normal value in detailreception 

    for used to after to make the update easily in docligne

        cn.Open()
                    For i = 0 To DataGridView1.Rows.Count - 1
                        Dim cmddddd As New SqlCommand("select cbMarq,AR_Ref from F_DOCLIGNE where DO_Piece='" & DataGridView1.Rows(i).Cells(0).Value & "' and AR_Ref='" & DataGridView1.Rows(i).Cells(1).Value & "' ", cn)
                        Dim daz As New SqlDataAdapter(cmddddd)
                        daz.Fill(qqs)
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        Dim usb2 As New SqlCommand("select MAX(NumeroLigneCommandeFrs) from DetailReceptionFrs", cn)
                        Dim ttt2 = usb2.ExecuteScalar
                        numligne = ttt2 + 1
                        Using cmd1 As New SqlCommand("insert into DetailReceptionFrs(Annee,Numero,Codearticle,Numseq,Designation,Qte,PrixUnitaire,PrixRevient,Remise,ReferenceFournisseur,NumeroLigneCommandeFrs,NumeroEnsemble,Marge,Coefficient1,Coefficient2)values ('0',@Numero,@Codearticle,@Numseq,@Designation,@Qte,@PrixUnitaire,@PrixRevient,@Remise,@ReferenceFournisseur,@NumeroLigneCommandeFrs,@NumeroEnsemble,@Marge,@Coefficient1,@Coefficient2)", cn)
                            With cmd1.Parameters
                                .Add("@Numero", SqlDbType.NVarChar).Value = (TextBox1.Text)
                                .Add("@Codearticle", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(1).Value)
                                ' .Add("@Numseq", SqlDbType.NVarChar).Value = qqs.Rows(i).Item("cbMarq")
                                .Add("@Numseq", SqlDbType.NVarChar).Value = qqs.Rows(0).Item(0) this is where the unique storage to DETAILRECEPTION TABLE
                                .Add("@Designation", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(2).Value)
                                .Add("@Qte", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(3).Value)
                                .Add("@PrixUnitaire", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(4).Value)
                                .Add("@PrixRevient", SqlDbType.NVarChar).Value = ("0.00")
                                .Add("@Remise", SqlDbType.NVarChar).Value = ("0.00")
                                .Add("@ReferenceFournisseur", SqlDbType.NVarChar).Value = (TextBox3.Text)
    
                                .Add("@NumeroLigneCommandeFrs", SqlDbType.Int).Value = numligne + i + 1
    
                                .Add("@NumeroEnsemble", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(7).Value)
                                .Add("@Marge", SqlDbType.NVarChar).Value = (DataGridView1.Rows(i).Cells(8).Value)
                                .Add("@Coefficient1", SqlDbType.NVarChar).Value = ("0.000")
                                .Add("@Coefficient2", SqlDbType.NVarChar).Value = ("0.000")
                            End With
                            Try
                                cmd1.ExecuteNonQuery()
                            Catch ex As Exception
                            End Try
                        End Using
                    Next
              

    the hall idea is me who invented to get access to the second table 

    for update and finally is not working i should have another idea

    please help me

    Thursday, September 19, 2019 10:29 AM
  • You could add constraints at the database table level and catch a violation in the DataError event of the DataGridView or create constraints in code.

    Option Infer On
    
    Namespace Demo
    	Partial Public Class Form1
    		Inherits Form
    
    		Public Sub New()
    			InitializeComponent()
    			AddHandler Me.Shown, AddressOf Form1_Shown
    			AddHandler dataGridView1.DataError, AddressOf DataGridView1_DataError
    		End Sub
    		Private Sub DataGridView1_DataError(ByVal sender As Object, ByVal e As DataGridViewDataErrorEventArgs)
    			MessageBox.Show(e.Exception.Message)
    			e.Cancel = True
    		End Sub
    
    		Private Sub Form1_Shown(ByVal sender As Object, ByVal e As EventArgs)
    			dataGridView1.DataSource = GetDataTable()
    		End Sub
    
    		Public Function GetDataTable() As DataTable
    			Dim dt As New DataTable()
    			dt.Columns.Add(New DataColumn() With {.ColumnName = "Id", .DataType = GetType(Integer), .AutoIncrement = True, .AutoIncrementSeed = 1})
    			dt.Columns.Add(New DataColumn() With {.ColumnName = "FirstName", .DataType = GetType(String)})
    			dt.Columns.Add(New DataColumn() With {.ColumnName = "LastName", .DataType = GetType(String)})
    
    			Dim mainConstraint = New UniqueConstraint( { dt.Columns("FirstName"),dt.Columns("LastName") })
    			dt.Constraints.Add(mainConstraint)
    			dt.Rows.Add(Nothing, "Karen", "Payne")
    			dt.Rows.Add(Nothing, "Mary", "Payne")
    
    			Return dt
    		End Function
    	End Class
    End Namespace

    Personally it's better to add constraints in the table of the database.

    ALTER TABLE dbo.yourtablename
      ADD CONSTRAINT uq_yourtablename UNIQUE(FirstName, LastName);
    To deal with existing duplicates use a SELECT as per my former reply.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 19, 2019 10:40 AM
    Moderator
  • its easy and simple to get understand the program that i do

    first i have an agent to fill the datagridview with some data

    like

     BC and ARTICLE and QTE and Prix 

    from this datagrid i insert it to to table in database called detailReception

    this detailReception i make a lot of calculation and in result 

    i got this new value like a result and instert it to a new table called docligne

    the question is how to inserted to this docligne?

    i used bc and article to get the line after that i make the insert and what i want

    the problem i face now is

    that bc and article used to refer the line in docligne not enough because the duplication so i need a help to resolve that duplication


    • Edited by Houssem12 Thursday, September 19, 2019 11:13 AM
    Thursday, September 19, 2019 11:04 AM
  • its easy and simple to get understand the program do

    first i have an agent to fill the datagridview with some data

    like

     BC and ARTICLE and QTE and Prix 

    from this datagrid i insert it to to table in database called detailReception

    this detailReception i make a lot of calculation and in result 

    i got this new value like a result and instert it to a new table called docligne

    the question is how to inserted to this docligne?

    i used bc and article to get the line after that i make the insert and what i want

    the problem i face now is

    that bc and article used to refer the line in docligne not enough because the duplication so i need a help to resolve that duplication

    How do you make the presentation in your datagridview? Do you use the DataSource?

    Success
    Cor

    Thursday, September 19, 2019 11:15 AM
  • yes it is from a text file already created

    its 4 column BC ARTICLE QTE PRIX


    • Edited by Houssem12 Thursday, September 19, 2019 11:37 AM
    Thursday, September 19, 2019 11:30 AM
  • Regards From Egypt

    I have to ask, why are you using meaningless name for parameters ? Parameter names should reflect the name of the field name e.g. if we have FirstName the parameter name would be @FirstName.

    Also, when performing an insert one should add 

    ; SELECT CAST(scope_identity() AS int)

    Then use ExecuteScalar cast to an integer to get the new primary key.



    Thanks for your hint ..... I used to do what you said but with many parameters , 

    confusion occurs so with naming it (@d1, @d2 ,..... and so on) no confusion occurs ....... 

    The GenerateID function enables me to do (insert and update) so easy 

    Private Function GenerateID(TableName As String, ColumnName As String) As String
            conn = New SqlConnection(cs)
            Dim value As String = "0"
            Dim sql As String = String.Format("SELECT TOP 1 {0} 
                                               FROM {1} ORDER BY 
                                               {2} DESC",
                                               ColumnName, 
                                               TableName, 
                                               ColumnName)
            Try
                ' Fetch the latest ID from the database
                conn.Open()
                cmd = New SqlCommand(sql, conn)
                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                If rdr.HasRows Then
                    rdr.Read()
                    value = rdr.Item(ColumnName)
                End If
                rdr.Close()
    
                ' Increase the ID by 1
                value += 1
    
            Catch ex As Exception
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                End If
                conn.Close()
                value = "0"
            End Try
            Return value
        End Function

    hope my perspective is obvious now ..... 

    and thank you for paying attention Karen


    Regards From Egypt

    Thursday, September 19, 2019 11:49 AM
  • Ah you simply have to add a primary key in your SQL database

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-2017


    Success
    Cor

    Thursday, September 19, 2019 12:00 PM
  • no it have the unique key but i can't get it this is my problem

    i should mention that database have 2 table(detailR,docligne)

    one which i insert this textfile data

    other where i should made the update

    Thursday, September 19, 2019 1:00 PM
  • i still not solve the problem please help

    if you know 'Gestion Commercial 100 pour sql server'

    and table 'DOCLIGNE' 

    you will probably understand everything

    Friday, September 20, 2019 4:50 PM
  • i still not solve the problem please help

    if you know 'Gestion Commercial 100 pour sql server'

    and table 'DOCLIGNE' 

    you will probably understand everything

    Look Houssem12, 

    As I said and as everyone here said to you.... you must make a PK ID for every table (i.e: table1 have col1(ID),col2(name),col3(job),col4(address),col5(phone))here in this example we can make a primary key (ID) column because it is unique it will descend like this 1,2,3,4,5,6,7,8,9,10,1,12,13,14,15,.......... and so on for ever no repetitive in its value  or you can make (name) column as a primary key if you make the "name" unique (i.e like number no repetitive) you can make this to names if you use a datatable ,So my perspective is using the ID column is more simple and easy than name because you may have two names or more like each other ,So when the update process occurs a confusion to SQL server will occur and you can't update what you want , because it has two names or more like each other ,,,,, The solution to your issue is following Mr Cor link  

    <<<< Ah you simply have to add a primary key in your SQL database

    https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-primary-keys?view=sql-server-2017 >>>>>> this is what Mr Cor said to you in his last comment 

     Hope this help you to solve your issue


    Regards From Egypt

    Saturday, September 21, 2019 10:09 AM
  • thanks for help

    but this database not me who created also table i work in is not me created also,and every row in this table there a software creat it and make a unique key in it and i know it,and i want just make a requet to update some rows

    the problem is i have only 2 column that i can work with.

    i think this is unresolved problem and will not be resolved

    Tuesday, September 24, 2019 10:08 AM
  • thanks for help

    but this database not me who created also table i work in is not me created also,and every row in this table there a software creat it and make a unique key in it and i know it,and i want just make a requet to update some rows

    the problem is i have only 2 column that i can work with.

    i think this is unresolved problem and will not be resolved

    Yes unresolved issue because you have no a full access to your database , but it has a unique column you can use it to perform the update process


    Regards From Egypt

    Tuesday, September 24, 2019 8:17 PM
  • yes it has a unique key the problem how i can use it

    its not me who creat this table either the unique key

    iit is another softwar called 'gestion commercial 100 pour sql server'


    • Edited by Houssem12 Friday, September 27, 2019 8:38 AM
    Friday, September 27, 2019 8:37 AM
  • yes it has a unique key the problem how i can use it

    its not me who creat this table either the unique key

    iit is another softwar called 'gestion commercial 100 pour sql server'


    It's obvious now that you can use my code to update sql table i will give it to you again , if you have a unique key you can update

    Sub UpdateFirm()
    
            If txtID.Text <> GenerateID("Firm", "ID") Then
                Using cn As New SqlConnection(cs)
                    cn.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = cn
                        cmd.CommandText = "update Firm 
                                            set  PayMethod=@a6,
                                                PayDate=@a7,
                                                Payment=@a8,
                                                Total=@a9,
                                                TotalDue=@a10,
                                                Notes_=@a11,
                                                BCheck=@a13,
                                                DDate=@a14
                                          where ID=@a1"
    
                        With cmd.Parameters
                            '.Add("@a2", SqlDbType.Int).Value = Val(txtInvoiceNo.Text)
                            '.Add("@a3", SqlDbType.NVarChar).Value = txtcustomer.Text
                            '.Add("@a4", SqlDbType.Int).Value = Val(txtTotalPayment.Text)
                            '.Add("@a5", SqlDbType.DateTime).Value = DateTimePicker1.Value
                            .Add("@a6", SqlDbType.NVarChar).Value = cmbMethod.Text
                            .Add("@a7", SqlDbType.DateTime).Value = DateTimePicker4.Value
                            .Add("@a8", SqlDbType.Float).Value = Val(txtPayment.Text)
                            .Add("@a9", SqlDbType.Float).Value = Val(txtTotal.Text)
                            .Add("@a10", SqlDbType.Float).Value = Val(txtTotalDue.Text)
                            .Add("@a11", SqlDbType.NVarChar).Value = txtNotes.Text
                            .Add("@a13", SqlDbType.Float).Value = Val(TextBox1.Text)
                            .Add("@a14", SqlDbType.DateTime).Value = DateTimePicker5.Value
                            .Add("@a1", SqlDbType.Int).Value = Val(txtID.Text)
                        End With
                        If cn.State = ConnectionState.Open Then
                            cn.Close()
                        End If
                        cn.Open()
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
            End If
    
        End Sub

    You can use it at your scenario  and the (SUB GenerateID()) i shared it here in the above this is only to create a new ID then i just check if the ID is old then make update and if it is new then make save ,,,,,,,,,,, hope it helps if you don't want to use generate id you can use a datatable or you can make an update process when the user call an old unique ID to a textbox 

    hope it helps ................. 


    Regards From Egypt

    Saturday, September 28, 2019 10:14 AM