javamysqlavailability

Trying to make item's availability to NO and not available(cant issue) if the stocks are 0 but it seems the item still getting issued


I'm trying to make item's availability to NO and not available(cant issue) if the stocks are 0 but it seems the item still getting issued.

Here's the code for the issuance of the item:

String sql = "insert into issueditem(Item_No,Item ID, Item Name, Issued Date, "
           + "Due Date,  Item Number, Stock) values (?,?,?,?,?,?,?)";

try {      
    connect()
    pst = conn.prepareStatement(sql);
    pst.setString(1, txtitemno.getText());
    pst.setString(2, itemid.getText());
    pst.setString(3, itemname.getText());
    pst.setString(4, txtidate.getText());
    pst.setString(5, txtredate.getText());
    pst.setString(6, itemnum.getText());
    pst.setString(7, stock.getText());

    pst.execute();

    JOptionPane.showMessageDialog(null, "Item issued");
    update();

} catch (Exception e) {
    JOptionPane.showMessageDialog(null, e);
} finally {
    try {
        rs.close();
        pst.close();
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
}

Here's my method of updating the stocks and the availability of the item if the stocks are 0 to NO

public void update(){ 
    int st = Integer.parseInt(stock.getText()); 
    int q = 1;
    int sup = st - q;
    String s = String.valueOf(sup);
    stock.setText(s);
    try {
        int n = Integer.parseInt(s);
        if (n>=0){
            String val1= itemid.getText();
            String val2 = stock.getText();
            String val3=jt14.getText();
            String sql = "update storageitem set ItemID='"+val1+"', "
                    + "Stock='"+val2+"' where ItemID='"+val1+"'"
                    + "Available= 'NO' where Stock='"+val2+"'";
            pst = conn.prepareStatement(sql);
            pst.executeUpdate();
            JOptionPane.showMessageDialog(null, "Record Updated");
        }else{
            JOptionPane.showMessageDialog(null, "Item is not issued");
        }
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }finally{
        try {
            rs.close();
            pst.close();
        } catch (Exception e) {
              JOptionPane.showMessageDialog(null, e);
        }
    }
}

Solution

  • Your SQL query is wrong, so if I understood well, you want to change the availability to 'no' if the ItemId = val1 and if val2 = 0

    public void update(){ 
        int st = Integer.parseInt(stock.getText()); 
        int q = 1;
        int sup = st - q;
        stock.setText(sup+"");
        try {
            String val1= itemid.getText();
            String val2 = stock.getText();
            String val3=jt14.getText();
            if (sup>=0 && val2.equals("0")){
                String sql =  "update storageitem set Available = 'NO',  Stock= '"+val2+"'"
                + " where ItemID='"+val1+"' ";
                pst = conn.prepareStatement(sql);
                pst.executeUpdate();
                JOptionPane.showMessageDialog(null, "Record Updated");
            }else{
                JOptionPane.showMessageDialog(null, "Item is not issued");
            }
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }finally{
            try {
                rs.close();
                pst.close();
            } catch (Exception e) {
                  JOptionPane.showMessageDialog(null, e);
            }
        }
    }
    

    you don't need to convert sup to a String value. and in stock.setText() you can put an int value of sup concatenating with an empty String sup+""
    sup = st-q which is the same value of n , so no need to convert it to String then reconvert it to int, use directly sup (just for better performance)