File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL coding to add stock balance in JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL coding to add stock balance in JSP " Watch "SQL coding to add stock balance in JSP " New topic
Author

SQL coding to add stock balance in JSP

richardtham
Greenhorn

Joined: Nov 07, 2001
Posts: 4
I am facing some problem writing this SQL coding on adding
a stock balance record in JSP:
Below is an extract from my coding:

[This message has been edited by richardtham (edited November 25, 2001).]
[ Editted by Dave to fix the HTML and add code tags ]
[This message has been edited by David O'Meara (edited November 25, 2001).]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

OK, you're really wranking up the offenses here (not an attack on you, just for reference):
Firstly, JavaRanch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp .
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in again with a new name which meets the requirements.
Secondly, please don't post in multiple forums. Many visitors look at multiple forums and will see your post twice, but if it ends up getting answered twice it wastes the time of the second person. I think this is the correct forum so I'll organise for the other one to be closed.
Finally, use the [ code] UBB tags, format your code, and beware of pasting HTML into your posts...
Dave.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Now that we've sorted that out, is there a specific problem so we don't have to go line by line through your code? If there is an exception thrown can we get it pasted (inside code tags!) or if it is behaving incorrectly, what is going wrong?
Dave.
Richard Tham
Greenhorn

Joined: Nov 25, 2001
Posts: 3
Sorry for the mess created earlier.
Okie, let me refine my question here:
I like the input field "Qtypurchase" to increase the stock quantity (IQty) as more purchases is made to the Stock Inventory System.
Hope someone out there could rewrite the codes below.
thanks in advanced
Richard
int rowsAffected = stmt.executeUpdate("add Qtypurchase into IQty from item where ItemID=" + ItemID4);
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

No stress!
Are you talking to an update operation?
(this uses a java.sql.PreparedStatement instead of a Statement)

or am I completely off track?
Richard Tham
Greenhorn

Joined: Nov 25, 2001
Posts: 3
Maybe I was not really clear with my original question.
Let me explain what I should be the required output and results:
For a given "ItemID", I need to update the "IQty" with the input field
"Qtypurchase" so that the resulting Purchase Quantity would be a larger
amount. Example:
Original Quantity: 100 (IQty )
Purchase Amount: 50 (Qtypurchase)
Closing Quantity After Purchase: 150 (IQty)
I shall copy paste the full coding here again for three files,
idbpurchase.jsp, imodproc2purchase.jsp and iupdpurchase.jsp
Hope u could help me solve my problems here.
Thank you very much. I appreciate it
[This message has been edited by Richard Tham (edited November 26, 2001).]
[ Editted by Dave to remove the code - Sorry ]
[This message has been edited by David O'Meara (edited November 26, 2001).]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Please don't paste the code for all files, only the relevant (SMALL!) code is required. Otherwise we drown in code. (oh, and use lowwercase 'code' blocks, not upper and escape < brackets as &lt; first)
There is some SQL that allows relative alteration of data, I'll have a look and see what I can find...
Dave.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Hmm, my guess is that what you are trying to do is a vendor specific flavour of SQL. Are you sure you need to update multiple rows or can you read the data of a single row, change it and then re-write it back to the database? This is definitely the way I'd do it.
Dave.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

can you try this:

or even better using preparedStatement:

It would be my advice to not add the original amount owned to the amount bought(read in 100, add 50) and re-write the new total(150) to the amount owned column. Just add the amount(50 + previously owned total) bought to the amount owned column. Don't have to worry about dirty reads, multithreading/multiuser problems
Jamie

[This message has been edited by Jamie Robertson (edited November 26, 2001).]
Richard Tham
Greenhorn

Joined: Nov 25, 2001
Posts: 3
Hi Jamie,
After trying out the first coding above, I got the following error msg:
org.apache.jasper.JasperException: Unable to compile class for JSPC:\Program Files\Apache Group\jakarta-tomcat\work\localhost_8080%2Fstock\_0002fiupdpurchase_0002ejspiupdpurchase_jsp_6.java:76: Undefined variable: Qtypurchase
int rowsAffected = stmt.executeUpdate("update item set IQty = IQty + "+ Qtypurchase + " where ItemID=" + ItemID4);
^
1 error

Hope u could help me to troubleshoot the problem as the full coding is shown above at the top of the forum thread.
Thanks
Richard
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Richard:
you haven't defined what Qtypurchase is? You may have defined it in another piece of code(different html file), but it seems as if it is out of scope and becomes undefined by the time it processes the current request. Can you do a requestParameter call again? I'm not familiar with JSP so this is just a guess.
Jamie
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL coding to add stock balance in JSP
 
Similar Threads
Pool Manager with MySQL - JSP ... need help
urgent help in jsp taglib
Tomcat and Mysql Configuration
Stripping out HTML from String
SQL coding to add stock balance in JSP