CODEWITHSUNDEEP
jspservletsjstl
Billy
Billy

Reputation: 79

JSP - Subtracting value from database

I am attempting to create a orders system in JSP & MySQL, I was hoping someone could please help me.

I want to be able to decrease the stock level of a product when it is placed in a order. (ie. 9* 16gb USB Flash Drives are purchased. At the time the order is placed A SQL query retrieves the current level 50 and subtracts the ordered amount 9 and updates the database with the new level 41).

I have tried this JSP but I can not get it to work.

<sql:query var="stockresult" scope="request" dataSource="jdbc/project">
  SELECT *
  FROM stocklevels
  WHERE productID = $param.productID
</sql:query>

$newstocklevel = stockresult - $orderqty;


<sql:update var="newstock" scope="request" dataSource="jdbc/project">
  UPDATE stocklevels
  SET stock=$newstocklevel
  WHERE productID = $param.productID
</sql:update>

Can anyone please help?

Thanks

Upvotes: 0

Views: 1156

Answers (1)

rusty
rusty

Reputation: 671

This can be a solution for you, but it is highly recommended not to insert request parameters directly in an SQL statement.

<sql:query var="stockresult" scope="request" dataSource="jdbc/project">
   SELECT stock <!-- select the current stock -->
   FROM stocklevels
   WHERE productID = $param.productID
</sql:query>

<!-- calculate the new value from the first row of the query result -->
<c:set var"newstocklevel" value="${stockresult.rows[0].stock - orderqty}"/> 


<sql:update var="newstock" scope="request" dataSource="jdbc/project">
   UPDATE stocklevels
   SET stock=$newstocklevel
   WHERE productID = $param.productID
</sql:query>

Upvotes: 1

Related Questions