• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

hwo to update database value by subtracting the user input from it

 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I want to subtract the database value from the userinput and want to update table with a new value,
I have one selltable where seller can sell only the items which are in stock now i want to subtract that user input quantity from the stock quantity.i have made sql statement like this is it correct ,if not how can we do it,
and please forward me some dummy code which have implimented like this,
String sql=" update itemdetails set quantity =quantity-Double.parseDouble(stock_text.getText() WHERE itemcode=?";
 
Paul Clapham
Sheriff
Posts: 21586
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's a better basis for a PreparedStatement:

 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Paul Sir,
My question is which value will come into quantity=quantity -? where itemcode=?; Is this value will come here Double.parseDouble(stock_text.getText(),beacuse we are taking user input.
"String sql="update itemdetails set quantity=quantity-? WHERE itemcode=?";"
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tell me whether my understanding is correct?

You are taking a numeric value as user input.
You are doing stock_text.setText(String value).
You are passing the stock_text object to DAO.

If these are the steps followed, then next thing is to just execute the query mentioned by Paul using preparedStatement.
The place holder(1st ? mark) will be replaced by Double.parseDouble(stock_text.getText())
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Tapas,
Yes i am taking numeric user input from the user Double.parseDouble(stock_text.getText() by setting and getting text and now i am going to subtract those from existing qauantity in a database.
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
i want to access the quantity which is into table itemdetails,now when user will enter quantity into sell quantity ,it has to subtract into item quantity details, then on add button reamning quantity has to pdate into both the tables in sell table as well as in item details i can use the above explained update table query but the problem which i am stuck with,how can i subtract those in sql queries i tried something like this,for updating itemdetail..
String sql=" update itemdetails set Quantity=Double.parse(Quantity)-Double.parseDouble(sell_text.getText() WHERE ItemCode=?";

and for stock quantity i have used this query
String sql1="update sellproducttable set stockquantity=Quantity-Double.parseDouble(sell_text.getText() where ItemCode=? "


i could be able to use it properly does i need to use select querya anywhere because without it i wll not be able to use it.

how would i use it,i am stuck here please revert me.

 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Monisha,

Paul has already given you answer, just to clarify it a little more

 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi swastik
It is clear to me but I am stuck in the thing that I have to pick the value of item detail table where quantity is present.this table is where we are updating sell product table here we can only update the value when we fetch the quantity from item detail table how should I select that one my problem is this only.I want to select that value from item table (quantity ) and then I want to place it in update query or what ever logic I can apply just suggest me as I am making my college project and I am new so have very little idea about it
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So

You should have a select query that retrieves the value from your item table, and then you can use that value in the update query to update the other table.
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i know that query but how i will use it. should i use like this
String sql1="select Quantity from itemdetails where id=?"
String sql2="select sellquantity from sellproducttable where id=?"
now how would i manupulate those here

"String sql1="update sellproducttable set stockquantity=stockquantity-? where ItemCode=?""
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Monisha

So far as I understand your requirement is, you are retrieving Quantity from itemdetails and updating stockquantity of sellproducttable with the quantity retrieved from itemdetails table, right?
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes exactly when the user sell item that quantity must have to reduce in the item detail table as well as in reaming stock in sell product table.how can i do this if you are having some dummy code which show this kind of case i can understand from this ,btw thankyou so much
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry , but still little confused. Do you mean it will update both the tables with the quantity entered by user?
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i want to delete quantity entered by the user by the quantity available.and we swill show the stock quantity (remaining quantity ) there in sell product table and the bal quantity in the item quantity which is different table (itemdetail) i n the database hope you will get my problem


sqlq1="select quantity from itemdetails where id=?" but how will i use it lateron.
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does that mean you have to deduct the quantity from both the tables?
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes i dnt know how to code it,
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you just need two update statements.
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
but without using select query how should i select any value from item detail and update any table which are having quantity column and that has to fetch from item detail and it will deduct it here in selltable data to show
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, probably I am taking too much of time to understand. Let me clarify it once more.
1. User enters an item code.
2. Using this item code you retrieve the quantity from itemdetail table.
3. You now use this quantity (retrieved in step 2) to update the sellproducttable.
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
or is it?

1. User enters a quantity

2. You update the quantity field in both the tables with the above quantity



 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes .
how will i select quantity from item table and use this in sell table like this balance quantity(stock quantity it is in sell table)=total quantity (item quantity)- sellquantity(sell table),hope i am able to clear you this time,sorry i am new comer so not able to clear in one attempt
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No need to feel sorry, at times we take time to understand. So, as far as I understand, itemtable contains the actual stock, sale table contains the actual sale quantity When user enters a quantity you deduct this from actual stock from items table and add this saleqty in sale table, right?
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes sir now you are on track this what I want to do I want to update this at both the places in item detail it will show remaining quantity and in sell table it is stock quantity
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Probably still not fully on the track. What is the difference between remaining quantity and stock? Why are you using two different tables. Coding is not tough, but we first need to be clear about the actual requirement.
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you are getting it slightly wrong, I might be wrong as well, I am telling you with what I understood so far. In one table you should deduct the quantity i.e. currentstock-salesquantity(user input) and in the othertable you should add add the salesquantity, i.e. numberofitemssold+salesquantity.
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have one table where I will insert all inventory detail like item code quantity price everything next table I ammaintaing is sell product table where all sell detail will kept like item code selling price quantity you are selling and balance quantity which I called earlier a stock quantity .as user insert quantity which he is going to sell it has to subtract from both the places in item quantity as well as here in stock updated value .as user sell anything naturally the item quantity will reduce from quantity in item table
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In both the tables same thing will happen deduction when you are selling anything quantity will never increase at both the places quantity will decrease .
Leave the updation of one table item table suppose you have a task to update stock quantity in sell table how should we do so,we are having total quantity present in item table how will you use it as select query and use it for deduction with sell quantity
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok.. seems at last i got it, but for this I don't think you need any select query. Two update statements should be fine.

 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gm Swastik Sir,
My question is that only when you are using quantity of another table how can you use the quantity of another table it is not in sell product table it is present in item table now when you are selecting the quantity available on that table you must have to select atleast one time to use quantity available in item table.how can you directly use it.


String sql1="update itemtables set stock=stock-? where itemcode=?
String sql1="update salestable set qty=qty-? where itemcode=?
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Would you mind writing the flow step by step, because seems to be still confusing. Sometimes you are saying it should update the quantity field of both tables based on user in put , and sometimes you are saying it should pick up the quantity from one table. It would be nice if could elaborate it with exact process flow step by step.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think the Sale Table is a history of sales. One of the columns is the quantity remaining in stock after that sale.

So:
Sale - id, itemcode, quantity_sold, quantity_in_stock, maybe a timestamp?

Of course, then it would be an INSERT...not an UPDATE.
Which might imply I've got it wrong.
 
monisha kasturi
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes Swastik Sir Sure,
I have two tables item table and sell table,
Itemtable is having having field itemcode(primarykey),quantity,categories,tax and itemname,It is filled by user as he will make a stock in his inventory.
i have one another table selltable which are having fields itemcode,selling quantity,price and stock quantity, now suppose user will sell any item,this value which is remaining balance will have to be in this stockquantity section,now automatically item table which is having field quantity it also have to update with remaining quantity.
i am explaining this in better way suppose i have total quantity is 100 earler when user sell 10 item 90 has to be stockquantity and in quantity column of itemtable,but initially you have to pick the quantity from itemtable as we so in inventory. hope you will get it now.

i have use this query but this is not correct how ill i use this properly

try
{
String sql2="select Quantity from itemdetails where itemcode=?";
PreparedStatement pst=conn.prepareCall(sql2);
rs=pst.executeQuery(sql2);
String sql=" update sellproducttable set Quantity=Quantity-Double.parseDouble(stock_text.getText() WHERE ItemCode=?";
PreparedStatement pst1=conn.prepareCall(sql);
pst1.setDouble(6,Double.parseDouble(stock_text.getText()));
pst.executeUpdate();

}
catch(SQLException E)
{
E.printStackTrace();
}
}
 
Swastik Dey
Rancher
Posts: 1643
6
Android Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PreparedStatement pst1=conn.prepareCall(sql);

prepareCall is to call a stored procedure, it should be

PreparedStatement pst1=conn.prepareStatement(sql);
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic