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" 'InvDetIdnow 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 Subthis my code something wrong
hope any one can help me
Thanks