Increasing Stocks from another form

[aerandir.miriel]

i got error when making this code. Here is i attach my code and tables:

My Tables are

**PRODUCT**             **TR_RECEIVE_PRODUCT**
>ID_PRODUCT         >ID_RECEIVE_PRODUCT
>NAME               >ID_PRODUCT
>CURRENT_STOCK      >DATE_IN
                    >STOCK_IN

====================================

=============================================================================

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim cmd As New SqlCommand


        Dim connectionString As String = "Data Source=.|dbDIR|;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_RECEIVE_PRODUCT(ID_RECEIVE_PRODUCT, ID_PRODUCT, DATE_IN, STOCK_IN) VALUES ('" & Me.txtID_RECEIVE_PRODUCT.Text & "', '" & Me.cmbID_PRODUCT.SelectedValue & "', '" & _
                                Me.DateTimePicker1.Value.ToString("yyyy-MM-dd") & "', '" & Me.txtSTOCK_IN.Text & "')"

            cmd.ExecuteNonQuery()
        Catch
            MsgBox("ERROR", MsgBoxStyle.Critical, "Error")
        End Try


        cmd.CommandText = "SELECT * FROM PRODUCT WHERE ID_PRODUCT='" & dgvTB.CurrentRow.Cells(1).Value & "'" 
        cmd = New SqlCommand(cmd.CommandText, con)
        cmd.ExecuteReader()
        If cmd.ExecuteReader.Read = True Then
            current_stock = Convert.ToInt32(cmd.ExecuteReader(5).ToString)
            stock_in = Convert.ToInt32(dgvTB.CurrentRow.Cells(3).Value)
            qty = stock_in + current_stock
            con.Close()
            con.Open()
            cmd.CommandText = "UPDATE PRODUCT SET CURRENT_STOCK='" & qty & "' WHERE ID_PRODUCT='" & dgvTB.CurrentRow.Cells(1).Value & "'"
            cmd = New SqlCommand(cmd.CommandText, con)
            cmd.ExecuteNonQuery()
            MsgBox("stock has been updated", MsgBoxStyle.Information)
            con.Close()
        Else
            MsgBox("Error", MsgBoxStyle.OkOnly)
        End If

        Me.GetData()
        Clear()
        con.Close()

===============================

some error include these statement:
cmd.CommandText = "SELECT * FROM PRODUCT WHERE ID_PRODUCT='" & dgvTB.CurrentRow.Cells(1).Value & "'"

If cmd.ExecuteReader.Read = True Then

well, the record is still recorded, but the stocks on PRODUCT table won't increased :((

any help would be appreciate it ^_^

[Reverend Jim]

Your try/Catch is masking the actual error. Use

Catch ex As Exception
    MsgBox(ex.Message)

and post the error message here. If you use

Debug.WriteLine(ex.Message)

then you can just copy/paste the text

[aerandir.miriel]

Error highlighted on cmd.ExecuteReader() it said

Violation of PRIMARY KEY constraint 'PK_TR_RECEIVE_PRODUCT'. Cannot insert duplicate key in object 'dbo.TR_RECEIVE_PRODUCT'. The statement has been terminated.

[Reverend Jim]

There's your problem. A record with that key already exists in the table.

[aerandir.miriel]

of course, i want to update the value of other table which contains PK from other table.
in TR_RECEIVE_PRODUCT it contains FK from PRODUCT.
i need to update CURRENT_STOCK from PRODUCT, which the data came from STOCK_IN from TR_RECEIVE_PRODUCT

sorry for my bad English
i hope u understand what i mean :((

[Reverend Jim]

If you want to modify an existing record then you use an UPDATE query. The format is

UPDATE tablename 
   SET fld1=value, fld2=value, etc
 WHERE fld3=value

You can have one or more SET clauses and the WHERE clause (which can be omitted if you want to update all records) can be used to determine which records will be affected. For example, to change a phone number you could code

UPDATE empTable SET phone='204-555-1212'
 WHERE lastname='Johnson' AND firstname='Fred'