Hi,

I have the following code which works fine;

Code:
For RowID = 0 To DgvSelectedItems.Rows.Count - 1
                            Dim sql As String = "INSERT INTO dbo.TblSales (TransactionID, TruckType, TruckModel, CatType, ProductName, PriceperItem, Quantity,  FullAmount, Username, SalesDatetime) " & _
                                                                "VALUES (@TransactionID, @TruckType, @TruckModel, @CatType, @ProductName,  @PriceperItem, @Quantity, @FullAmount, @Username, @SalesDatetime)"
                            Dim cmd As New SqlCommand(sql, nextConn)

                            With cmd.Parameters
                                .AddWithValue("@TransactionID", TransactionID)
                                .AddWithValue("@TruckType", Me.DgvSelectedItems.Rows(RowID).Cells(0).Value)
                                .AddWithValue("@TruckModel", Me.DgvSelectedItems.Rows(RowID).Cells(1).Value)
                                .AddWithValue("@CatType", Me.DgvSelectedItems.Rows(RowID).Cells(2).Value)
                                .AddWithValue("@ProductName", Me.DgvSelectedItems.Rows(RowID).Cells(3).Value)
                                .AddWithValue("@PriceperItem", Me.DgvSelectedItems.Rows(RowID).Cells(4).Value)
                                .AddWithValue("@Quantity", Me.DgvSelectedItems.Rows(RowID).Cells(5).Value)
                                .AddWithValue("@FullAmount", Me.DgvSelectedItems.Rows(RowID).Cells(7).Value)
                                .AddWithValue("@Username", Me.ToolStripTextBox2.Text)
                                .AddWithValue("@SalesDatetime", DateTime.Now)
                            End With

                            cmd.ExecuteNonQuery()

                        Next RowID

Now,

I have 3 tables in my database;

dbo.TblBraking (Fields: ProductID, TruckType, Truckmodel, CatType, ProductName, Quantity)
dbo.TblFilters (Fields: ProductID, TruckType, Truckmodel, CatType, ProductName, Quantity)
dbo.TblWheeling (Fields: ProductID, TruckType, Truckmodel, CatType, ProductName, Quantity)

All these tables have a field called QUANTITY which states the quantity left in stock.

In the above INSERT statement - I have the quantity which a customer purchased for a specific product within a datagridview.
Note: A customer can purchase one or more than one product (either a brake/filter/wheel) in one transaction.

Now, I want to update the respective table by deducting the quantity already purchased, e.g if the quantity of filters for a specific product is 4 then a customer bought 2 I want to update the dbo.Tblfilters with 2 filters in the Quantity (4-2). This can also be a combination, e.g., a customer purchases 1 brake and 1 filter - then the quantity on TblFilters and TblBraking should be reduced by 1, respectively.

First, I have to check is the product a brake/filter/wheel - this is in the CatType (Brake, Filters, Wheel).
To know the unique product we have to combine the TruckType, TruckModel and ProductName.

then update the Quantity field in the respective table..

Please help how I can achieve this ?


Thanks