oracle update statement within the resultset is not working.pls see code.

Hi

This code below is not updating the table with the date created from the ID obtained from the select clause.

I have created 2 statement objects one for select query and one for update ...can some one help me with updating the table for every new date created using the ID.
Any help is apprecaited.Thanks in advance.

Code:

---

try {
                        statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE );
                        statementIUD = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
                } catch (SQLException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                }       
                try {
                        ResultSet rs= statement.executeQuery("select schoolID from school where year = 2005 and county_id = 1");
               
                        while(rs.next()){
                            count = rs.getRow();
                            ID = rs.getInt(1);       
                                System.out.println("the ID value is: "+ID);
               
                                int x,y, z;
                                int second,third,fourth;
                                x = ID;
                                y=x/10000000;
                                int year= 2000+y;

                                //for the 2 nd digit from left.
                                z= x%10000000;
                                y= z/1000000;
                                second= y;
                 
                                //for the 3 rd digit from left.
                                z= z%1000000;
                                y= z/100000;
                                third= y;
                 
                                //for the 4 th digit from left.
                                z= z%100000;
                                y= z/10000;
                                fourth= y;
                 
                                int julianday = second*100+third*10+fourth;
//                                System.out.println("the julian day is: "+julianday);
                                int completeVal = year*1000+julianday;
//                                System.out.println("the value of julian date is: "+completeVal);
                 
                                String test = String.valueOf(completeVal);
//                                System.out.println("the string value of completeVal is: "+test);
                                Date d = LoadDateReceived.getDateFromJulian7(test);
                                System.out.println(d);
                                DateFormat df = new SimpleDateFormat("dd-MMM-yy");
                                String d1 = df.format(d);
                                System.out.println(d1);                               
                                String Sql = ("UPDATE school set DATE_RECEIVED = '"+d1+"' where (year = 2005 and county_id = 1) and schoolID = "+ID);
                                System.out.println("the statement is: "+Sql);                                       
//                                Execute the update Statement
                                Count_updatedLevel = statementIUD.executeUpdate(Sql);
                                System.out.println("the count is: "+Count_updatedLevel);                       
//                                System.out.println(LoadDateReceived.getJulian7FromDate(d));
                        }//end of while loop
                        statement.close();
                        statementIUD.close();
                        rs.close();
                        System.out.println("the count of updated records are: "+Count_updatedLevel);
                        System.out.println("the count is: "+count);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
}

---

The results look like this:-
10-May-07
10-May-07
11-May-07
14-May-07
14-May-07
14-May-07
14-May-07
14-May-07
14-May-07
14-May-07
14-May-07
14-May-07
15-May-07
15-May-07
15-May-07
16-May-07
16-May-07
the count of updated records are: 1
the count is: 219

If u see , for every date ..one record should be updated , but only one record is updated for the first date .

Any help is appreciated..how to perform all the updates for every date ?

A few pointers first.
This:

Code:

---

String Sql = ("UPDATE school set DATE_RECEIVED = '"+d1+"' where (year = 2005 and county_id = 1) and schoolID = "+ID);

---

should be part of a PreparedStatement, so statementIUD should be declared as a PreparedStatement. Then you can assign the DATE (and school id) properly, and not as a String, assuming that DATE_RECEIVED is correctly declared as a DATE in your db of course. In addition you can use addBatch() instead of executeUpdate and, after the loop has finished, you can then executeBatch() to execute all the updates in one go. This is the usual way of handling this sort of thing.

Also your resources (the two statements and the result set) should be closed in a finally block. If this code were to throw an exception these resources would not get closed, so you could get a resource leak.

Right, that lot aside, what do you mean by "perform all the updates for all the records"?

You've not shown us what you are expecting to see compared with what you are seeing.

That code (looking at it briefly) seems to loop over your result set and do the update you want, setting the DATE_RECEIVED to whatever it is you've calculated.