my code:
My Database:
RPRODUCT PRODUCT
>ID_RPRODUCT >ID_PRODUCT
>ID_PRODUCT >NAME
>DATE_IN >CURRENT_STOCKS
>STOCK_IN
my code is in RPRODUCT form
=====================
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim cmd, cmd2, cmd3 As New SqlCommand
Dim connectionString As String = "Data Source=.\SQLEXPRESS;|MyDB|;Integrated Security=True;Connect Timeout=30;User Instance=True"
con = New SqlConnection(connectionString)
If Not con.State = ConnectionState.Open Then
con.Open()
End If
cmd.Connection = con
Try
cmd.CommandText = "INSERT INTO TR_RPRODUCT(ID_RPRODUCT, ID_PRODUCT, DATE_IN, STOCK_IN) VALUES ('" & Me.txtID_RPRODUCT.Text & "', '" & Me.cmbID_PRODUCT.SelectedValue & "', '" & _
Me.DateTimePicker1.Value.ToString("yyyy-MM-dd") & "', '" & Me.txtSTOCK_IN.Text & "')"
cmd.ExecuteReader()
Catch
MsgBox("ERROR", MsgBoxStyle.OkOnly, "ERROR")
End Try
con.Close()
If Not con.State = ConnectionState.Open Then
con.Open()
End If
cmd2.Connection = con
Try
cmd2.CommandText = "SELECT * FROM MS_PRODUCT WHERE ID_PRODUCT='" & dgvTB.CurrentRow.Cells(1).Value & "'"
stock_in = Convert.ToInt32(dgvTB.CurrentRow.Cells(3).Value)
current_stock = Convert.ToInt32(cmd.ExecuteReader(5).ToString)
qty = stock_in + current_stock
con.Close()
If Not con.State = ConnectionState.Open Then
con.Open()
End If
cmd3.Connection = con
cmd3.CommandText = "UPDATE MS_PRODUCT SET CURRENT_STOCK='" & qty & "'WHERE ID_PRODUCT='" & dgvTB.CurrentRow.Cells(2).Value & "'"
cmd3.ExecuteNonQuery()
MsgBox("Stock increased", MsgBoxStyle.Information)
con.Close()
Catch
MsgBox("Wrong Data", MsgBoxStyle.OkOnly, "Error")
End Try
con.Close()
Me.GetData()
Clear()
=====================
on the first command text looks fine (the data completely added to datagrid)
the issue is on the 2nd and 3rd command text (cmd2 and cmd3)
it wont read the script (i also use cmd2.ExecuteReader, but it contains error)
Violation of PRIMARY KEY constraint 'PK_TR_RPRODUCT'. Cannot insert duplicate key in object 'dbo.TR_RPRODUCT'. The statement has been terminated.
the STOCKS_IN from RPRODUCT should update the CURRENT_STOCKS from PRODUCT table.