Member Avatar for KRUX17

Hi,

I'm having some issues with getting next data from the database, it seemed to be working before, but now it doesn't change the textfields to the next data just appends the data into TextArea1 and TextArea2, the other data TextField1 etc.. Doesn't even change, so I do not know why this is happening. Been trying to figure out I can get the next data from the database for about 3 hours would really love some help.Anyone like to shine some light into this?

Thanks

Kru

` private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
// Next Button

    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null; 



    String host = "xxxx";
    String uName = "xxxx"; 
    String uPass = "xxxx";


    try {
        con = DriverManager.getConnection(host,uName,uPass);

        ps = con.prepareStatement("SELECT * FROM Job",ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        rs = ps.executeQuery();
       // String sql = "SELECT Customer.Firstname,Customer.Surname,Customer.TelephoneNo, ";
        if(rs.next()) {

       int id = rs.getInt("JobID");
       Date date =  rs.getDate("JobDate");
       String status = rs.getString("Status"); 
       String description = rs.getString("Description"); 
       String mechanic = rs.getString("Mechanic");
       Time duration = rs.getTime("Duration");
      // int customer = rs.getInt("CustomerID");
       String txtID = Integer.toString(id);



       jTextField1.setText(txtID);
       jTextField2.setText(date.toString());
       jTextArea1.append(status);
       jTextArea2.append(description);
       jTextField3.setText(mechanic);
       jTextField4.setText(duration.toString());

       System.out.println(id + "" +  date +  status  + description + mechanic +  duration );

        }
        } catch(SQLException e) {

        e.getMessage();

     } finally {
    try {
            if(con != null) {
                con.close();
                 System.out.println("CLOSED!!");
            }      
            if (ps != null) {
                ps.close();
            }
            if(rs != null) {
                rs.close();
            }

        } catch (SQLException e) {
            System.out.println("Exception has been caught");

        }   

    }

}  `
Edited by KRUX17
  • 2 Contributors
  • 8 Replies
  • 224 Views
  • 58 Minutes Discussion Span
  • Latest Post Latest Post by KRUX17

Dani AI

Generated

Your handler both re-runs the SELECT each time and uses append(), so every click restarts the cursor and concatenates text instead of replacing it. As suggested, do the SQL once (or cache results) and update the fields with setText() on each Next click. Two practical, safe patterns follow.

First (recommended for small–medium result sets): load the rows into a simple in-memory list and close the DB connection immediately. This avoids long-lived DB resources and makes next/previous trivial:

private static class Job {
  int id; Date date; String status, description, mechanic; Time duration;
}

private final List<Job> jobs = new ArrayList<>();
private int currentIndex = -1;

private void loadJobs() throws SQLException {
  String sql = "SELECT JobID, JobDate, Status, Description, Mechanic, Duration FROM Job ORDER BY JobID";
  try (Connection con = DriverManager.getConnection(host,user,pass);
       PreparedStatement ps = con.prepareStatement(sql);
       ResultSet rs = ps.executeQuery()) {
    while (rs.next()) {
      Job j = new Job();
      j.id = rs.getInt("JobID");
      j.date = rs.getDate("JobDate");
      j.status = rs.getString("Status");
      j.description = rs.getString("Description");
      j.mechanic = rs.getString("Mechanic");
      j.duration = rs.getTime("Duration");
      jobs.add(j);
    }
  }
  if (!jobs.isEmpty()) { currentIndex = 0; showJob(currentIndex); }
}

private void showJob(int i) { /* setText on your UI fields from jobs.get(i) */ }

private void nextButtonActionPerformed(...) {
  if (currentIndex < jobs.size()-1) { currentIndex++; showJob(currentIndex); }
}

Second (less preferred): create a scrollable ResultSet once at form init and keep Connection/PreparedStatement/ResultSet as fields until the window closes. That works but risks timeouts and ties up DB resources.

Quick troubleshooting checklist:

  • Replace append() with setText() to avoid concatenation.
  • Use ORDER BY so navigation is predictable.
  • Call e.printStackTrace() in catches to see errors.
  • Handle null Date/Time when converting to string.
  • For large tables, implement paging (LIMIT/OFFSET) or load pages via a SwingWorker to keep the UI responsive.
  • Always close DB resources when the window is closed.

Recommended Answers

All 8 Replies

Member Avatar for JamesCherrill

Line 18 if (rs.next... will only execute the following code zero or one times. Didn't you mean
while (rs.next... ?

ps line 43 is a disaster. You get the message string and completely ignore it, so you will never see any error messages. Replace that line with e.printStackTrace(); to see the full details of any diagnostics

Edited by JamesCherrill
Member Avatar for KRUX17

Thanks for the reply,

And nope it doesn't seem to work. I have attached pics.

Click Here
Click Here

Member Avatar for JamesCherrill

What is supposed to happen? eg start this form with the first record displayed then move on to the next record each time that ActionPerformed is called? Or something else?

Member Avatar for KRUX17

Start the form with the first record (I have another method which does this) then moves on to the next record each time the button is invoked.

Edited by KRUX17
Member Avatar for JamesCherrill

In that case your structure is going to have to change. Right now you start again with a new SQL query every time the next button is pressed. You should just do that SQL once, when the form is started, and keep the result set. Then in the button handler you can execute one rs.next() and update all the fields - using setText not append.

Member Avatar for KRUX17

Yes, but how do I keep the resultset I mean when I close the rs there won't be anything in the Result set.

Member Avatar for JamesCherrill

Obviously you can't close the result set while the user is trying to "next" his way through it. You need to keep that result set open until the form is closed.

Create window:   do SQL, create result set
Previous/Next:   move through result set, display the current record
Close window:    release all SQL-related resources that were created when the window was opened, including the result set
Edited by JamesCherrill
Member Avatar for KRUX17

Thanks Again, James!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.