cant get it to update

[kelly devon]

hi,
i need help on the updating of a field in my access database from my gui
but its just not updating it. is there something i am doing wrong in my code?
i'll appreciate corrections
heres the code

 qty = qtytext.getText();
total = totaltxt.getText();
rs = salesmade.executeQuery("SELECT * FROM Stock WHERE ITEM = "+'"+itemno+"'");
While (rs.next()){
   salesno = itemtxt.getString(itemno);
   salesmade.executeUpdate("UPDATE Stock SET Total= "+total+" WHERE ITEM ="+itemno+"");}
 

the total was supposed to replace the one in the DB but its not(pls permit any mistakes with the apostrophies i wrote it out with my head without compiling so i may have made mistakes cause im not on my system and i needed help thxs but apart from that that how i wrote it out and it is not updating)wat should i do any ideas thx

[Paul Sturrock]

Are you sure your update code is even being executed? You will only update if something was returned by the select query. I'd add some debug stataments so you can see the SQL being run and double check it against you DB.

[Rajendar Goud]

Hi kel,
Whats the error you are facing when u try to update?
as of your below database steps,

qty = qtytext.getText();
total = totaltxt.getText();
rs = salesmade.executeQuery("SELECT * FROM Stock WHERE ITEM = "+'"+itemno+"'");
While (rs.next())
{
salesno = itemtxt.getString(itemno);
salesmade.executeUpdate("UPDATE Stock SET Total= "+total+" WHERE ITEM ="+itemno+"");
}

as you said its fine with apstrophes b/c you just wrote it by hand for explaining it ,and hope u also
meant itemtxt = rs in your code.

Are you atleast able to fetch the results using the Select statement?
if so check out the privilages for the database user u are using .it may not have the update privilages for the table you r accessing .
or else
Remove the update statement from the while loop and run it seperately and test it.


Raj

[kelly devon]

alright i'll try that and i checked it but it is not being executed thx forthat reminder but how do i force it to execute? must i use the while(rs.next())?
pls how do i go about it?
thx

[Rajendar Goud]

iam sorry but i didnt get u . u said its not being executed. which is not getting executed? even the select statement also?

then first check out whether r u atleast able to connect to database or not ?

[Paul Sturrock]

rs.next() - moves the cursor on in a ResultSet. If the ResultSet is empty, then the cursor can't move and rs.next() returns false. Since your update is inside a loop which is controlled by this test you have to make sure the test returns true otherwise no iterations of the loop will run. So your update will only run if there are any records in your ResultSet, i.e. if your first bit of SQL gets results. Unless you are actually seeing an Exception, then that's the first thing I would be checking.