Add New Stock to Current stock

[Kujee]

I am new to VB. Currently i am doing a Stock manage program with VB and SQL 2008.

When Receive new stock from Supplier,I put recive item in a winform with DataGrid view. some thing like this

**Item  Description Received Qty UnitPrice Total**
  1001  Acer Laptop  10          455       4550
  1005  Monitor      20          100       2000

this data insert to purchase details table and I have another table for Stock call Stock.
now I want to do when after enter the data and when click save button ReceiveQty value add to CurrentStock colunm in Stock table.
in this case how can i do this. I hope you could understand what i try to do here.
if you want to see my code I can past here.
Thanks all!

[Jx_Man]

Update CurrentStock after Save data.
Do save and update at the same time.
You can post your code here to give more information.

[Kujee]

Here is my code

I want to Add this value (Dim receivedQty As String = DGGRN.Rows(I).Cells(3).Value) to in Stock table CurrentStock Colunm value with particular itemcode

Thanks!

Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click

        'Connecting to SQL Server using VB code
        Dim MyConnection As SqlConnection = Nothing
        Dim MyTransaction As SqlTransaction = Nothing

        Try
            'Connect to Database Server using connection string

            MyConnection = New SqlConnection("Data Source=my-pc;Initial Catalog=IKStock;Integrated Security=True")
            MyConnection.Open()
            MyTransaction = MyConnection.BeginTransaction

            'Insert inside Good Recived note to Puruchase Table
            Dim SQL As String = "insert into Purchase (SupplierCode,Supplier,SupplerAddress,Date,InvoiceNumber,QuotationNumber,PONumber,ManualGRNno,GRNTotal) values (@0,@1,@2,@3,@4,@5,@6,@7,@8)"
            Dim CMD1 As New SqlCommand
            CMD1.Connection = MyConnection
            CMD1.Transaction = MyTransaction
            CMD1.CommandText = SQL
            CMD1.Parameters.AddWithValue("@0", TextBoxSupplierCode.Text) 'text box data insert in to Purchase Table columns
            CMD1.Parameters.AddWithValue("@1", ComboBoxSupplier.Text)
            CMD1.Parameters.AddWithValue("@2", TextBoxSupplerAddress.Text)
            CMD1.Parameters.AddWithValue("@3", DateTimePickerDate.Text)
            CMD1.Parameters.AddWithValue("@4", TextBoxInvoiceNumber.Text)
            CMD1.Parameters.AddWithValue("@5", TextBoxQuotationNo.Text)
            CMD1.Parameters.AddWithValue("@6", TextBoxPOno.Text)
            CMD1.Parameters.AddWithValue("@7", TextBoxManualGRNno.Text)
            CMD1.Parameters.AddWithValue("@8", TextBoxIncTotal.Text)
            CMD1.ExecuteNonQuery()
            CMD1.Dispose()

            'Get GRN code from Purchase table to purchase table
            SQL = "select max(GRNCode) as MAXID from Purchase"
            Dim CMD2 As New SqlCommand
            CMD2.Connection = MyConnection
            CMD2.Transaction = MyTransaction
            CMD2.CommandText = SQL
            Dim GRNCode As Long = CMD2.ExecuteScalar()
            CMD2.Dispose()

            '---------'
            'insert the datagridview details to PurchaseDetails Table

            Dim I As Integer
            For I = 0 To DGGRN.Rows.Count - 1



                Dim item As String = DGGRN.Rows(I).Cells(0).Value
                Dim description As String = DGGRN.Rows(I).Cells(1).Value
                Dim category As String = DGGRN.Rows(I).Cells(2).Value
                Dim receivedQty As String = DGGRN.Rows(I).Cells(3).Value
                Dim unitprice As Decimal = DGGRN.Rows(I).Cells(4).Value
                Dim nbt As String = DGGRN.Rows(I).Cells(5).Value
                Dim vat As String = DGGRN.Rows(I).Cells(6).Value
                Dim linetotal As Decimal = DGGRN.Rows(I).Cells(7).Value
                Dim remarks As String = DGGRN.Rows(I).Cells(9).Value

                'Insert Above Variable data to Purchase Details table
                Dim CMD3 As New SqlCommand
                SQL = "insert into PurchaseDetails (GRNCode,ItemCode,Description,ReceivedQty,NewUnitPrice,NBT,VAT,LineTotal,Remarks,Category) values (@0 ,@1 ,@2 ,@3 ,@4 ,@5 ,@6 ,@7 ,@8,@9)"
                CMD3.Connection = MyConnection
                CMD3.Transaction = MyTransaction
                CMD3.CommandText = SQL
                CMD3.Parameters.AddWithValue("@0", GRNCode)
                CMD3.Parameters.AddWithValue("@1", item)
                CMD3.Parameters.AddWithValue("@2", description)
                CMD3.Parameters.AddWithValue("@3", receivedQty)
                CMD3.Parameters.AddWithValue("@4", unitprice)
                CMD3.Parameters.AddWithValue("@5", nbt)
                CMD3.Parameters.AddWithValue("@6", vat)
                CMD3.Parameters.AddWithValue("@7", linetotal)
                CMD3.Parameters.AddWithValue("@8", remarks)
                CMD3.Parameters.AddWithValue("@9", category)

                CMD3.ExecuteNonQuery()
                CMD3.Dispose()

            Next

            MyTransaction.Commit()

            'close connection
            MyTransaction.Dispose()
            MyConnection.Close()
            MyConnection.Dispose()

            'Show Message After Save Data
            MsgBox("Successfully Saved New GRN", MsgBoxStyle.OkOnly, "Save Success")

            'After Save Reset all field
            ComboBoxSupplier.SelectedIndex = 0
            TextBoxSupplerAddress.Text = ""
            TextBoxInvoiceNumber.Text = ""
            TextBoxQuotationNo.Text = ""
            TextBoxPOno.Text = ""
            TextBoxManualGRNno.Text = ""
            ComboBoxItem.Text = ""
            ComboBoxItemDis.Text = ""
            TextBoxReciveQty.Text = ""
            TextBoxUnitPrice.Text = ""
            TextBoxNBT.Text = ""
            TextBoxVAT.Text = ""
            TextBoxRemarks.Text = ""
            DGGRN.Rows.Clear()


        Catch ex As Exception
            If MyTransaction IsNot Nothing Then
                MyTransaction.Rollback()

            End If

            If MyTransaction IsNot Nothing Then
                If MyConnection.State = ConnectionState.Open Then
                    MyConnection.Close()

                End If
            End If
            MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly)

        End Try



    End Sub