I have a stock system where you can add a item to the system, increase the amount of different items, decrease the amount of different items in a system and completely delete an item from the system.

What I am trying to do is when the user inputs the name of a item in the first textbox and a number in the second textbox when they press remove the number they entered into the second textbox will be removed from the numberinstock variable in the database.

Here is my code for the remove button:

private void RemoveButtonActionPerformed(java.awt.event.ActionEvent evt) {                                             

String url = "jdbc:derby://localhost:1527/Customers";
String username = "username";
String password = "password";

try {
    Connection conn = DriverManager.getConnection(url, username, password);
    if (conn != null) {

        String sql = "UPDATE STOCK\n"
        + "SET NUMBERINSTOCK = NUMBERINSTOCK -"+RemoveTextField1.getText()+" \n"
        + "WHERE UPPER(name)=?";

        PreparedStatement statement = conn.prepareStatement(sql);

        statement.setString(1, RemoveTextField1.getText());
        statement.setString(2, RemoveAnItemTextField.getText().toUpperCase());

        int rowsUpdated = statement.executeUpdate();

        if (rowsUpdated > 0)
            JOptionPane.showMessageDialog(null,"That item has been updated successfully");
            JOptionPane.showMessageDialog(null,"That item is either not in the database or you have entered the wrong information");
}catch (SQLException ex)
  ResultSet rs = null;
     PreparedStatement ps = null;
   Connection conn = DriverManager.getConnection(url, username, password);
   ps = conn.prepareStatement("select * from Stock");
   rs = ps.executeQuery();

}catch(Exception ex){
    JOptionPane.showMessageDialog(null, ex.getMessage());

What this code does is it connects to the database, there is then an update statement that will update the database once the remove button is pressed,I had so that when the user entered the name of an item it would remove one from that item in that database, but I then realized that someone may want to remove more than one item at a time. This is where I am stuck, I am not sure how to get the number the user enters in the second textbox to be removed from the database once the remove button is pressed

My first observation is that you should first query to see if « NUMBERINSTOCK » is equal or greater than the value entered in the « RemoveTextField1 » box ... Otherwise you might set « NUMBERINSTOCK » to a negative value.

Second, you should do a bit of validation on the value entered in the « RemoveTextField1 ».
Although an SQL sentense is a string, one should validate if any other characters than numeral are fed in « RemoveTextField1 ».

Finally, if the value from « RemoveTextField1 » is OK and smaller or equal to the « NUMBERINSTOCK » field, I don't see why it souldn't work.