Update Statement - Logic

[dr223]

Hi,

I have the following code which works fine;

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

[techgnome]

By changing your database design... all three of your tables have the same fields... which all do the same thing, so it should be one table, with an additional field that indicates the product type... here's how I'd probably design this:

tblCategory-- This table contains "Brake" "Filters" "Wheeling" etc...
-------------
ID
Name
Description
(plus any other fields as it relates to the product type)


tblTruckType
------------
ID
Name
Description

tblTruckModel
-------------
ID
TruckType (fkeys to the type) -- or this could be reversed by having TruckModel in the tblTruckType table... depends on the data and how you want to use it
Name
Description

tblProducts
-----------
ID
Category (fkey to tblCategory)
TruckModel (fkey to tblTruckModel)
TruckType (fkey to tblTruckType)
Name
Description
Quantity

-tg

[dr223]

Ok - Re-designed the database as you highlighted.

Now, I have all the 4 tables as highlighted;

TblCatType
TblTruckType
TblTruckModel
TblTruckProducts


Now,

When I update TblSales, which contains almost all the details of the product inclusive Quantity. I want to update TblTruckProducts with the quantity left at the same time.

For example,

Product A = Quantity is 5 , then a user bought 2 so remainder 3.

I want to update TblTruckProducts with Quantity = 3. So that when the form is launched it shows that 3 are left.

This is how I do my insert;

For RowID = 0 To DgvSelectedItems.Rows.Count - 1
                            Dim sql As String = "INSERT INTO dbo.TblSales (TransactionID, TruckType, TruckModel, CatType, ProductID, ProductName, PriceperItem, Quantity,  FullAmount, Username, SalesDatetime) " & _
                                                                "VALUES (@TransactionID, @TruckType, @TruckModel, @CatType, @ProductID, @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("@ProductID", Me.DgvSelectedItems.Rows(RowID).Cells(3).Value)
                                .AddWithValue("@ProductName", Me.DgvSelectedItems.Rows(RowID).Cells(4).Value)
                                .AddWithValue("@PriceperItem", Me.DgvSelectedItems.Rows(RowID).Cells(5).Value)
                                .AddWithValue("@Quantity", Me.DgvSelectedItems.Rows(RowID).Cells(6).Value)
                                .AddWithValue("@FullAmount", Me.DgvSelectedItems.Rows(RowID).Cells(8).Value)
                                .AddWithValue("@Username", Me.ToolStripTextBox2.Text)
                                .AddWithValue("@SalesDatetime", DateTime.Now)
                            End With

                            cmd.ExecuteNonQuery()

                        Next RowID

Anyway can I incorporate it here - with an update to the Quantity to TblTruckProducts ...

Thanks

[techgnome]

The insert is irrelevant ...
"I want to update TblTruckProducts with the quantity left at the same time. " -- that's the relevant part... update your Products table and set the Quantity to the new value ... so what you need is an UPDATE statement.

-tg

[dr223]

I understand - I know how to write an update statement in VB, but How will update it with the correct value ...???

[techgnome]

something like this perhaps?
update tblTrkProduts
set Quantity = Quantity - @BoughtQty
where your criteria goes here


-tg

[dr223]

Ok;

Created this code and I receive the error incorrect syntax near '-'

Any help please

Try

                                query = "UPDATE TblTruckProducts SET Quantity - @Quantity WHERE ProductID = @ProductID"
                                cmd = New SqlCommand(query, nextConn)
                                cmd.Parameters.AddWithValue("@Quantity", Me.DgvSelectedItems.Rows(RowID).Cells(6).Value)
                                cmd.Parameters.AddWithValue("@ProductID", Me.DgvSelectedItems.Rows(RowID).Cells(3).Value)
                                cmd.ExecuteNonQuery()

                            Catch ex As Exception
                                MsgBox(ex.Message, MsgBoxStyle.Information, "Spare Elements")
                            End Try

Thank you

[techgnome]

derementing a value in SQL is no different from doing it in VB... look again at my sample.

-tg