CODEWITHSUNDEEP
javamysqlsqlswing
Agwu Chile
Agwu Chile

Reputation: 21

Issues with resultSet

im trying to get values from 2 different tables, member_tbl and schedule_tbl where each table have the same member_ID. now when i try to retrieve the details. it says member_ID does not exist. my code is below.

public int getMemberDetails(String memberid) {

    int oky = 0; 
    String memberDetails  = "SELECT * FROM member_tbl WHERE member_ID = ?";
    String scheduleDetails = "SELECT * FROM schedule_tbl WHERE member_ID = ?";

    try {
        con.connect();
        ps = con.con.prepareStatement(memberDetails);
        pc = con.con.prepareStatement(scheduleDetails);
        ps.setString(1, memberid);
        pc.setString(1, memberid);
        rs = ps.executeQuery();
        rb = pc.executeQuery();

        while (rs.next() || rb.next()) {
            uID = rs.getString("member_ID");
            jdate = rs.getString("joinDate");
            surname = rs.getString("lastname");
            mname = rs.getString("middlename");
            fname = rs.getString("MfirstName");
            dob = rs.getString("DOB");
            telephone = rs.getString("telephone");
            email= rs.getString("Memail");
            street = rs.getString("Mstreet");
            city = rs.getString("Mcity");
            state = rs.getString("Mstate");
            country = rs.getString("Mcountry");
            memtype = rs.getString("Mtype");
            mfee = rs.getString("Mtotal");
            photo = rs.getString("mphoto");
            bweight = rs.getString("body_weight");
            bfat = rs.getString("body_fat");
            bmass = rs.getString("body_mass");
            bheight = rs.getString("height");
            bpressure = rs.getString("blood_pressure");
            days = rb.getString("Days");
            hour = rb.getString("Hours");
            instruct = rb.getString("instructor");

            oky = 1;
        }
        con.con.close();

    } catch(SQLException e) {

    }
    return oky;
}

// This is where i call the method.

private void memIDsearchButtonMouseClicked(java.awt.event.MouseEvent evt) {                                               

    try {
        mids = memIDsearchField.getText();
        sqlQuerry sql = new sqlQuerry();
        int ans = sql.getMemberDetails(mids);

        if (ans == 1 )
        {
            idfield.setText(sql.uID);
            jdatefield.setText(sql.jdate);
            surnamefield.setText(sql.surname);
            middlenamefield.setText(sql.mname);
            firstnamefield.setText(sql.fname);
            dobfield.setText(sql.dob);
            phonefield.setText(sql.telephone);

.......................................................

but it keeps returning no resultset. i dont know if im doing something wrong. it was working just fine a few days ago but i dont know what went wrong. Thanks in advance.

Upvotes: 0

Views: 72

Answers (2)

duffymo
duffymo

Reputation: 308848

I don't understand why you aren't doing a JOIN:

select *
from member_tbl as m
join schedule_tbl as s
on m.member_ID = s.member_ID

I'll second "no empty catch blocks".

I'll suggest that you spell out the columns you want by name, even if it's all of them. (No star notation.)

You aren't closing result sets or statements at all, and you aren't closing the connection properly. All should be closed in a finally block, wrapped in individual try/catch blocks. I write static methods to keep the code small.

Upvotes: 0

JB Nizet
JB Nizet

Reputation: 691835

while(rs.next() || rb.next()) {
    uID = rs.getString("member_ID");

This doesn't make sense. If there is nothing in rs, but rb is full of rows, you'll still try to get values from rs.

You have to execute a query, read everything from its result set, then execute the second one, and read everything from its result set.

Also:

catch(SQLException e)
{

}

You should never, ever do that. If there is a SQLException, you'll never be aware of it because you catch it and ignore it. Exceptions are thrown to signal a problem. Hiding the exception won't fix the problem.

Upvotes: 3

Related Questions