How to deduct quantity of items in database?

Question

0.00/5 (No votes)

See more:

I'm creating a system where I must deduct and add by one if the item is borrowed/returned. In my system I have 2 tables:

tblItems(_itemnumber_, _itemname_, _quantity_)
tblBorrow(_dateborrowed_, _datedue_, _status_)

Where if I borrowed an item the status will be borrowed and the quantity will deducted by 1 and if the status is returned the quantity will added by 1. This is my idea(I already how to add and update) but i don`t know how to code this idea. I'd really appreciate your help.

I`m using imports system.data.oledb

Posted 15-Jan-14 12:57pm

Member 10526674

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2014-01-15T15:11:00

as it is, I cant see a relation in between the tables you show ..

so, for instance, using 'pseudo' SQL, Im thinking its like 'update tblItems set _quantity_ = _quantity_ - 1', and, you wish to do that 'where tblBorrow._status_ = 'borrowed''; (??)

If you had a link between those two tables, for say an _itemnumber_, I'd probably write say 2 stored procedures,

one for 'borrowed(_itemnumber)' which sets the _status_ and _quantity_, then another
for 'returned(_itemnumber_)' ditto status and quantity appropriately

but Im not very sure of your schema

'g'

Sandeep Singh Shekhawat · Answer 2 · 2014-01-15T15:25:00

Solution 2

You need to define relationship between these two tables using itemId so you can identify which item borrowed or which returned. After that define insert/update trigger on table tblBorrow.

please see this example in following blog

Trigger in Sql Server[^]

Posted 15-Jan-14 15:25pm

Sandeep Singh Shekhawat

Comments

Member 10526674 16-Jan-14 6:35am

sir thank you for your relply and i change the database. I add itemnumber in the tblBorrow so the relationship is now one is to one(itemnumber-itemnumber). is this enough to create some codes w/o error?

tblItems(_itemnumber_, _itemname_, _quantity_)
tblBorrow(_itemnumber_, _dateborrowed_, _datedue_, _status_)