looping through resultset to search another value on different table

Question

I want to search for some specific results in Results table, using an id from a different table to calculate cgpa based on the students id selected from studentInfo table, below is what I tried, the issue is that it just computes for the first Id or rather student

    String lev = levelCombo.getSelectedItem().toString();
    int leve = Integer.parseInt(lev);
    int le = leve / 100;
    try {
        String sql
                = "select idNumber from studentInfo where level ='" + lev + "'  ";

        pst = conn.prepareStatement(sql);
        rs = pst.executeQuery();
        while (rs.next()) {
            String idNumb = rs.getString("idNumber");
            cgpa(idNumb, le);
            System.out.print("" + idNumb);
        }
    } catch (Exception e) {
    }
}

public void cgpa(String idnumber, int level) {
    float units = 0;
    float ugps = 0;
    float cgp = 0;

    for (int i = 1; i <= level; i++) {
        try {

            String sql = "select sum(UNIT) ,sum(UGP) ,round(sum(UGP) / NULLIF(sum(UNIT), 0), 2) from Results where ID_NUMBER =  ? and  level ='" + i + "'  ";

            pst = conn.prepareStatement(sql);
            pst.setString(1, idnumber);
            rs = pst.executeQuery();
            while (rs.next()) {
                Float un = rs.getFloat("sum(UNIT)");
                Float gp = rs.getFloat("sum(UGP)");
                //  Float sum=rs.getFloat("round(sum(UGP)/NULLIF(sum(UNIT), 0),2)");
                units += un;
                ugps += gp;
                cgp = ugps / units;
                String sql1 = "update academicstatus set cgpa ='" + cgp + "'  ,product='" + ugps 
                        + "',unit='" + units + "' where idnumber = ? ";
                updateAcademicstatus(cgp, ugps, units, idnumber);
                pst = conn.prepareStatement(sql1);
                pst.setString(1, idnumber);
                pst.executeUpdate();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                pst.close();
            } catch (Exception e) {
            }
        }
    }
}

what is your problem, your program have many problems did you check the `}` of your first method? — Youcef LAIDANI, Jan 31 '17 at 14:26
try to use `} catch (Exception ex) { ex.printStackTrace(); }` and show us your problem? — Youcef LAIDANI, Jan 31 '17 at 14:31
sorry for the }, I just used my phone to upload the question my computer is off, the problem is it just use the first value of the column from the first method to search the results, but I want it to search through the whole column value — Saddam Musa Mohammed, Jan 31 '17 at 14:33

score 0 · Answer 1 · answered Jan 31 '17 at 14:30

0

Are You trying in to single query

select  sum(UNIT),sum(UGP),round(sum(UGP)/ ULLIF(sum(UNIT),0),2)idNumber from studentInfo sinfo
join Results re on re.ID_NUMBER = sinfo.idNumber where level='"+lev+"'"

answered Jan 31 '17 at 14:30

JDevil

169
3
14

looping through resultset to search another value on different table

1 Answers1