Updating SQL problem
Hi guys,
Ok i have an array which collects 'quantity' values from the previous jsp and the aim is to update my DB with these values. The updating of the specific records is working fine (in the sense that it finds the right recorded to update.
However the problem is that it updates all the records with the last value in the array. for example, if the values 1,2,3 go into the array i think it is updating them ALL with 1, then all with 2 then all with 3. the intension is to have the first record with value 1, the second with 2 and third with 3 but it seems to be running through all and obviously 3 is the last number in the array so thats all im seeing in the DB.
I can see that the problem lies within that loop but cannot figure out how to update the records with the different array values.
here's my code:
String[] vals = request.getParameterValues("Quantity");
Statement statement = null;
ResultSet rs = null;
try{
PreparedStatement ps = connection.prepareStatement("UPDATE SelectedItems SET Quantity=? WHERE LotID=(SELECT max(LotID) FROM SelectedItems)");
for (int i = 0; i<vals.length; i++){
ps.setString(1, vals[ i ]);
ps.executeUpdate();
}
}
catch (Exception e)
{
e.printStackTrace();
System.err.println(
"TS_ERROR: Problem with inserting selected items");
}
....
any suggestions?
Thanks>
