java.sql.SQLException: Before start of result set

Question

Here I have attached my code, it shows the error like: java.sql.SQLException:

Before start of result set what I am doing wrong here:

String qry = "SELECT * From register ";



stmt = (PreparedStatement) conn.prepareStatement(qry);
rs =  stmt.executeQuery();
while (rs.next()) {
    String area = rs.getString("city");
    if(city.equals(area)){
        System.out.println("!!!!!!It matched: " + city);
        String qry2="select state from register where city='"+city+"'";
        System.out.println(qry2);
        stmt = (PreparedStatement) conn.prepareStatement(qry2);
        rs =  stmt.executeQuery();
        String state=rs.getString("state");
        System.out.println("state: " + state);
        break;
    } else {
        //System.out.println("No match with: " + area);
    }
}

This JDBC code makes no sense. You can't iterate over a ResultSet and change the value in mid stream. What are you doing? — duffymo
– duffymo, Commented Sep 7, 2017 at 18:20
Duffy is right, you should make new query outside the old one. — MaxZoom
– MaxZoom, Commented Sep 7, 2017 at 18:26
What is qry? Why is there an if on area when area should be part of the query WHERE clause? Why a second query? Can you update qry to join with the register table? — Andrew S
– Andrew S, Commented Sep 7, 2017 at 18:34
Agree with everything so far (you shouldn't be reassigning rs inside of an iteration over rs, you should have a WHERE clause in your SQL query instead of selecting everything.) And additionally, never, never use raw concatenation to build queries with values that come from ANY user (such as might come from a city column). You must, must, must use parameterization. Go research "SQL injection" to find out why. — ErikE
– ErikE, Commented Sep 7, 2017 at 18:51

geneSummons · Accepted Answer · 2017-09-07 18:37:10Z

1

You can use a new ResultSet object for query2 inside the query1 result set loop.

String qry2="select state from register where city='"+city+"'";
System.out.println(qry2);
stmt = (PreparedStatement) conn.prepareStatement(qry2);
ResultSet rs2 =  stmt.executeQuery();
String state=rs2.getString("state");

But generally it would be a better use of JDBC resources to iterate all the way through result set 1, collecting all the "city" values returned, and then loop through the "city" results calling query2 to get the "states" associated with the "cities" instead of they way you've showed.

edited Sep 7, 2017 at 18:37

answered Sep 7, 2017 at 18:28

geneSummons

9355 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sahil Chhabra · Accepted Answer · 2017-09-07 19:52:18Z

Second resultset is not required in your code. State and City both can be found in same resultset. You can use the following code:

String qry = "select * from register";
PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(qry);
             rs =  stmt.executeQuery();
             while (rs.next()) {
                 String area = rs.getString("city");
                 if(city.equals(area)){
                    System.out.println("!!!!!!It matched: " + city);
                    String state=rs.getString("state");
                    System.out.println("state: " + state);
                    break;
                 } else {
                    //System.out.println("No match with: " + area);
                 }
           }

Collectives™ on Stack Overflow

java.sql.SQLException: Before start of result set

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related