Load from database to datagridview

Call ConnectAccess()
        Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT ProductName,Quantity,UnitPrice,Extention, InvNomer,ProductId, InvDetId " _
      + " FROM  InvoiceDetail where InvNomer ='" & pbInvNomer & "' ", mConn)

        Dim ds As DataSet = New DataSet()
        da.Fill(ds, "InvoiceDetail")
        DGV.DataSource = ds.DefaultViewManager
        DGV.DataSource = ds.Tables("InvoiceDetail")
        Me.DGV.Columns(0).HeaderText = "Product Name"  'ProductName
        Me.DGV.Columns(1).HeaderText = "Quantity"      'Quantity
        Me.DGV.Columns(2).HeaderText = "Price"        'UnitPrice
        Me.DGV.Columns(3).HeaderText = "Total"         'Extention     
        Me.DGV.Columns(4).HeaderText = "No.Invoice"   'InvNomer
        Me.DGV.Columns(5).HeaderText = "Product ID."   'ProductId 
        Me.DGV.Columns(6).HeaderText = "InvDetail ID"    'InvDetId

now my invoice datagridview, example have 3 rows
no.Rows_Product Name_Quantity_UnitPrice_Extention_InvNomer_ProductId_InDetId
1.______Product01_______2________5______10______SI001_____11_____101
2.______Product02_______4________3______12______SI001_____12_____102
3.______Product03_______3________1_______3______SI001_____13_____103

Then I Edit and add new rows example 2 rows
no.Rows Product Name, Quantity, UnitPrice,Extention,InvNomer,ProductId,InDetId
1.______Product01_______2________5_____10_____SI001____11_____101
2.______Product02_______4________3_____12_____SI001____12_____102
3.______Product03_______3________1______3_____SI001____13_____103
4.______Product04_______1________2______2_____SI001____14
5.______Product05_______1________4______4_____SI001____15

Now please help me, how to insert to database, only new rows, no.4 and 5 only
no.1,2,3 for update quantity,unitprice and extention.

I have tried but there insert all 5 rows to my database, then my datagridview have 8 rows. or cannot insert
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
This my code like from
http://www.daniweb.com/software-development/vbnet/code/217047
please help me to correct or change then I can Insert to my database only new row DGV

Private Sub btnTestSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestSave.Click

        Dim check As Integer
        Call ConnectAccess()
        Dim cmdInv As New OleDbCommand
        Dim daInv As New OleDbDataAdapter
        Dim dsInv As New DataSet
        Dim dtInv As New DataTable
        Dim cmdInv1 As New OleDbCommand
        Dim daInv1 As New OleDbDataAdapter
        Dim dsInv1 As New DataSet
        Dim dtInv1 As New DataTable
        If pbEdit Then
            If txtTotal.Text = "" Then
                MsgBox("Invoice Data is not completed", MsgBoxStyle.OkOnly)
            Else
                If MsgBox("Are you sure to save Invoice data with Invoice Nomer : " & txtInvNomer.Text & " ?", MsgBoxStyle.OkCancel, "Input confirm") = MsgBoxResult.Cancel Then
                Else
                    cmdInv1 = mConn.CreateCommand
                    cmdInv1.CommandText = "SELECT * FROM Invoice WHERE InvNomer='" & Trim(txtInvNomer.Text) & " ' "
                    daInv1.SelectCommand = cmdInv1
                    daInv1.Fill(dsInv1, "Invoice")
                    dtInv1 = dsInv1.Tables("Invoice")
                    cmdInv1 = mConn.CreateCommand
                    cmdInv1.CommandText = "Update Invoice set Customer= '" & Convert.ToInt32(TxtCusKey.Text) & "', total = '" & txtTotal.Text & "' where InvNomer = '" & txtInvNomer.Text & "'"
                    check = cmdInv1.ExecuteReader.RecordsAffected()
                    If check > 0 Then
                        MsgBox("Invoice With Id " & Trim(txtInvNomer.Text) & " succesfully to added", MsgBoxStyle.OkOnly, "Message :")
                    Else
                        MsgBox("Invoice With Id " & Trim(txtInvNomer.Text) & " Failure to added", MsgBoxStyle.OkOnly, "Message :")
                    End If
                End If
                cmdInv = mConn.CreateCommand

                cmdInv.CommandText = "SELECT * FROM InvoiceDetail WHERE InvNomer='" & Trim(txtInvNomer.Text) & " ' "
                daInv.SelectCommand = cmdInv
                daInv.Fill(dsInv, "InvoiceDetail")
                dtInv = dsInv.Tables("InvoiceDetail")
                cmdInv = mConn.CreateCommand
                For i As Integer = 0 To DGV.Rows.Count - 1
                    Dim x As Integer = IsDBNull(DGV.Rows(i).Cells(6).Value)
                
                        If x = 0 Then
                            cmdInv1.CommandText = "Insert into InvoiceDetail (ProductName,Quantity ,UnitPrice,Extention,InvNomer,ProductId) values " & _
                              "('" & DGV.Rows(i).Cells(0).Value) & "','" & DGV.Rows(i).Cells(1).Value & "','" & DGV.Rows(i).Cells(2).Value & "','" & DGV.Rows(i).Cells(3).Value & "','" & txtInvNomer.Text & "','" &
DGV.Rows(i).Cells(5).Value & "')" 

                        Else
                            If x > 0 Then
                                cmdInv.CommandText = "Update InvoiceDetail set  ProductName='" & DGV.Rows(i).Cells(0).Value & "',Quantity = '" & Convert.ToInt32(DGV.Rows(i).Cells(1).Value) & "',UnitPrice = '" & Convert.ToInt32(DGV.Rows(i).Cells(2).Value) & "',Extention = '" & Convert.ToInt32(DGV.Rows(i).Cells(3).Value) & "',ProductId = '" &
Convert.toint32(DGV.Rows(i).Cells(5).Value) & "' where InvDetKey Like '" & Convert.ToInt32(DGV.Rows(i).Cells(6).Value) & "%'"
                            End If
                        End If
                    '   check = cmdInv1.ExecuteReader.RecordsAffected()

                Next

            End If
            Refresh_Form()
            mConn.Close()
        End If
        btnTestSave.Visible = True
    End Sub

this my code something wrong
hope any one can help me
Thanks

Tx Phasma

Just input value "0" to my DGV.Rows(i).Cells(6).Value

this thread solved

tx all

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.