inventory problem

[bettybarnes]

hi guys i would like to ask how do I subtract a value from my column Quantity(data type is integer = 18) in my inventory table if a customer will want to have 2 orders and of course 18 - 2 will be 16. how will i do this? please help me.

[debasisdas]

Not sure exactly what is your confusion.

All arithmetic operations works on database tables while used as part of a SQL.

[bettybarnes]

if my remaining quantity is 18 and my customer buys 2 pencils for example, of course the quantity will be reduced to 16. and what if my customer wants to buy 20 pencils, it should display a warning message saying, there's insufficient quantity in ur inventory..

[bettybarnes]

anyone who knows this? pls help me

[debasisdas]

how exactly are you handling these DB transactions ?

If I was dealing with pencils I would handle all transactions with pencil and paper. A software application my not be the way to go. Hope this helps.

[sandeepparekh9]

please put some coding here

Heres some code.

This will return a point on a circle given the center point of the circle, the radius, and the angle the point should be at.

Public Function GetPointOnCircle(ByRef centerPoint As PointF, ByRef radius As Double, ByVal angle As Single) As PointF

   Return New PointF(CSng(Math.Cos((Math.PI / 180) * angle) * radius + centerPoint.X), CSng(Math.Round(Math.Sin((Math.PI / 180) * angle) * radius + centerPoint.Y, 2)))

End Function

Heres a query string

Update MyTable
Set Quantity = Quantity - 2
Where InventoryItem = Pencil

[bettybarnes]

ahm it's about my sql queries.. i dont have an idea... my application im making is Inventory system wherein if my customer wants to buy 2 pencils and my remaining pencils are 18 in the database it should deduct automatically 2 pencils resulting to 16 pencils..

[debasisdas]

lets see the code that you are working on and fix it now itself.

[bettybarnes]

i dont have the code for sql queries. but i have my codes for my applications. that's why i'm asking for sql queries sample codes

[debasisdas]

you need to update the table row with the new data after the transaction.

if
current_quantity = 10
order = 2

so new_quantity = 10 -2 =8

that is what you needs to do

update table_name set quantity = quantity - 2;

[bettybarnes]

thanks i was thinking this solution before. but what if another order is done. the customer asks for 4 pencils? since update table_name set quantity = quantity - 2; is the code you gave me.. do i need to set a variable like this order = @variable? if 4 pencils need to be deducted in the table

[debasisdas]

Yes

[bettybarnes]

so how am i going to implement this one?

[debasisdas]

by writing SQL the way i have already suggested earlier.

[bettybarnes]

hi thanks for your patience. is it possible for this to make stored procedure?

[debasisdas]

Yes.

Using stored procedure is a better option than executing raw SQL.