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);
}
}
}
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)