How to update a database using SQL in jsp

Question

I have a database and a table called itemDetail it contains 2 fields : itemName and quantity

Using jsp how do I deduct/update from the database depend on the number I input?

Example

Initial quantity = 100 Html form quantity = 1 The SQL statement should update the database so Quantity - html form quantity New quantity in database = 99

Please help me.

Thanks.

Sample of my coding

<%

String iName = request.getParameter("itemName");
String Qty = request.getParameter("quantity");
String price = request.getParameter("price"); 

//out.print("itemName");
//out.print("quantity");
//out.print("price");
%>

<%-- <jsp:setProperty name="MrBeanA" property="itemName" /> 
<jsp:setProperty name="MrBeanA" property="Quantity" /> --%>

<%@ page import = "java.sql.Connection"%>
<%@ page import = "java.sql.DriverManager"%>
<%@ page import = "java.sql.Statement"%>

<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:Project");

Statement s = con.createStatement();
int yrwish = s.executeUpdate( "insert into Inventory (itemName, Qty, Price) values ('" + iName + "','" + Qty + "','" + price + "')");

s.close();
con.close();
%>

<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con1 = DriverManager.getConnection("jdbc:odbc:Project");

Statement delete = con.createStatement();
int yrwish2 = delete.executeUpdate("UPDATE itemDetail SET quantity =("+quantity-Qty+")WHERE itemName=+iName");

delete.close();
con1.close();

%>

Error An error occurred at line: 44 in the jsp file: /myjsp/Confirm.jsp Generated servlet error: quantity cannot be resolved

which is

<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con1 = DriverManager.getConnection("jdbc:odbc:Project");

Statement delete = con.createStatement();
int yrwish2 = delete.executeUpdate("UPDATE itemDetail SET quantity =("+quantity-Qty+")WHERE itemName=+iName");

delete.close();
con1.close();

%>

Answer 1

If you want to do this on same page use jQuery Ajax

else use form to get input and run Servlet on submit where you can update the database here

Demo of HTML Form

On form Submission , the Servlet will run which is in 'action' attribute

Code for Servlet : Not tested

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class MyServlet extends HttpServlet {

 public void doPost(HttpServletRequest req, HttpServletResponse res)
                            throws ServletException, IOException {
  String name = req.getParameter("itemName");
  int val = Integer.parseInt(req.getParameter("itemq").trim());

  Connection con = null;
  try {
   Class.forName("com.mysql.jdbc.Driver");
   con = DriverManager.getConnection("jdbc:mysql://localhost/test?" +
                               "user=minty&password=greatsqldb");

Edited:

   Statement stmt = con.createStatement();
   ResultSet rs1 = stmt.executeQuery("select quantity from itemDetail where itemName = "+iName); 
   rs1.next();
   int quantity = rs1.getInt("quantity"); //column label in database
   stmt.executeUpdate("UPDATE itemDetail SET quantity = "+(quantity - val)+" where itemName = "+name);

   con.commit();   
 }
 catch (Exception e) {
 }
 finally {
    if (con != null)
       con.close();
 }
}
}

Edited: Errors for reference to OP :

• Spelling of Statement at line 43
• s object is closed and now used at line 44, instead use delete object to execute query.
• And use this int yrwish2 = delete.executeUpdate("UPDATE itemDetail SET quantity = (quantity - "+Qty+") WHERE itemName="+iName);

Hope it helps !