Forum:

JDBC and Relational Databases

SQL coding to add stock balance in JSP

Greenhorn

Posts: 4

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I am facing some problem writing this SQL coding on adding
a stock balance record in JSP:
Below is an extract from my coding:

<html>
<head>
<title>Purchase A Stock Item</title>
</head>
<h1>Purchase A Stock Item</h1>
<body background="indtextb.jpg" bgcolor="#CCCCCC" text="#000000" 
link="#3366CC"
vlink="#666666" alink="#996600">
<table border="1" width="400">
<tr> 
<td>[B]Item ID[/B]</td><td>[B]Item Name[/B]</td>
<td>[B]Item Ref[/B]</td><td>[B]Vendor ID[/B]</td> <td>[B]Item 
Cost[/B]</td> <td>[B]Item 
Markup[/B]</td>
<td>[B]Selling Price[/B]</td><td>[B]Item Quantity[/B]</td><td>[B]Re-
order Quantity[/B]</td> 
<td>[B]Description[/B]</td> <td>[B]Action To Perform[/B]</td>
</tr>
<%@ page language="java" import="java.sql.*" %>
<body>
<form action="idbpurchase.jsp">
<input name="ItemID" type="text" width="30">
<input type="submit" value="Enter A Item ID To Purchase">
</form>
<% String ItemID;
ItemID = request.getParameter("ItemID");
if (ItemID == null) {
%>

<% 
}

else
Class.forName("org.gjt.mm.mysql.Driver");

Connection myConn = DriverManager.getConnection("jdbc:mysql:///stock");
Statement stmt = myConn.createStatement();
String sql = "SELECT * FROM ITEM WHERE ItemID=" + ItemID;
ResultSet rs = stmt.executeQuery(sql);

if (rs != null) {
while (rs.next()) {
String ItemID2 = rs.getString("ItemID");
String IName2 = rs.getString("IName");
String IRef2 = rs.getString("IRef");
String VendorID2 = rs.getString("VendorID");
String ICost2 = rs.getString("ICost");
String IMarkup2 = rs.getString("IMarkup");
String ISellp2 = rs.getString("ISellp");
String IQty2 = rs.getString("IQty");
String ReQty2 = rs.getString("ReQty");
String IDesc2 = rs.getString("IDesc");
%>
<tr>
<td><%= ItemID2 %></td>
<td><%= IName2 %></td>
<td><%= IRef2 %> </td>
<td><%= VendorID2 %> </td>
<td><%= ICost2 %></td>
<td><%= IMarkup2 %></td>
<td><%= ISellp2 %> </td>
<td><%= IQty2 %> </td>

<td><%= ReQty2 %></td>
<td><%= IDesc2 %></td>
<td><a href='imodproc2purchase.jsp?ItemID2=<%= ItemID2 %>'>confirm 
purchase ?</a></td>
</tr>

<%
} /* of while */
} /* of if */
stmt.close();
myConn.close();
%>
</table>
</table>
<a href="icontrol.html">Go back to admin control</a>
</body>
</html>

******************************************
<title>
Purchase A Stock Item
</title>
</head>
<%@ page language="java" import="java.sql.*" %>
<body>
<body background="indtextb.jpg" bgcolor="#CCCCCC" text="#000000" link="#3366CC" vlink="#666666" alink="#996600">
<h1>Purchase A Stock Item</h1>
<form method="post" action="iupdpurchase.jsp">
<table border="1" width="400">
<tr>
<td>[B]Quantity to Purchase:[/B]</td><td><input name="Qtypurchase" 
type="text" 
width="4"</td>
</tr>
<% 
Class.forName("org.gjt.mm.mysql.Driver");
Connection myConn = DriverManager.getConnection("jdbc:mysql:///stock");
Statement stmt = myConn.createStatement();
%>
<tr>
<td colspan="2"> 
<center>
<input name="pagemode" type="hidden" value="submit">
<input type="submit" value="Enter Quantity To Purchase">
</center>
</td>
</tr>
<%
stmt.close();
myConn.close();
%>
</table>
</form>
<a href="icontrol.html">Go back to admin control</a>
</body>
</html>

*********************************************************************
<html>
<head>
<title>
Purchase A Stock Item Processing
</title>
<body background="indtextb.jpg" bgcolor="#CCCCCC" text="#000000" 
link="#3366CC"
vlink="#666666" alink="#996600">
</head>
<%@ page language="java" import="java.sql.*" %>
<body>
<%
Class.forName("org.gjt.mm.mysql.Driver");
Connection myConn = DriverManager.getConnection("jdbc:mysql:///stock");
String ItemID4 = request.getParameter("ItemID");
String IQty4 = request.getParameter("IQty3");

Statement stmt = myConn.createStatement();
int rowsAffected = stmt.executeUpdate("add Qtypurchase into IQty from item where ItemID=" + ItemID4);
if (rowsAffected == 1)
{
%>
<h1>Successful Update of Purchase Item</h1>
The stock item has been modified.
<a href="idbquery.jsp">See all stock items</a>

<a href="icontrol.html">Go back to control center</a>
<% }
else
{
%>
<h1>Sorry, purchase update has failed.</h1>
<a href="icontrol.html">Go back to control center</a>
<%
}
stmt.close();
myConn.close();
%>
</body>
</html>

[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

Posts: 13459

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 13459

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 3

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 13459

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 3

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 13459

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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 < 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

Posts: 13459

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 1879

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 3

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 1879

I like...

posted 20 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Won't you be my neighbor? - Fred Rogers. tiny ad:

free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth

https://coderanch.com/t/751654/free-earth-friendly-heat-kickstarter