0

I'm doing an assignment for college Using a query in netbeans to a database in ms access that took me two solid days of head scratching to get to so far. Problem is it will give me a lovely result as soon as I open the package and run it. There after I get this error:

net.ucanaccess.jdbc.UcanaccessSQLException: invalid cursor state: identifier cursor not positioned on row in UPDATE, DELETE, SET, or GET statement: ; ResultSet is empty.

I would appreciate any help you could give. Thanks a lot.

private void FindFlghtBtnActionPerformed(java.awt.event.ActionEvent evt) {                                             

    try{

        rs = stmt.executeQuery("SELECT DepAirportTable.AirportName,             DepAirportTable.AirportDesignaton, FlightTable.DepDate,          ArrAirportTable.ArrAirportName, ArrAirportTable.ArrAirportDesignaton\n" +
                 "FROM PlaneTable INNER JOIN (ArrAirportTable INNER JOIN (DepAirportTable INNER JOIN FlightTable ON DepAirportTable.AirportDesignaton = FlightTable.FromAirDes) ON ArrAirportTable.ArrAirportDesignaton = FlightTable.ToAirDes) ON PlaneTable.FlightDesignationCode = FlightTable.[FlightDesignationCode]\n" +
                 "WHERE (((DepAirportTable.AirportName)=\""+DepCombo.getSelectedItem()+"\") AND ((FlightTable.DepDate)=#"+((JTextField)DateDep.getDateEditor().getUiComponent()).getText()+"#) AND ((ArrAirportTable.ArrAirportName)=\""+ArrComboBox.getSelectedItem()+"\"));");

        rs.next();

        {

        departure = rs.getString("AirportName");
        depDate = rs.getString("DepDate");
        arrival = rs.getString("ArrAirportName");


        BookingPrevTa.setText("Your flight details are:\nDeparting from "+departure+" arriving at: "+arrival+" departing on: "+depDate);



        rs.close();

        }
    }   
    catch (SQLException ee)
    {
            ee.printStackTrace();
    }  
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Javaiskillingme
  • 49
  • 5
  • 1
    Three comments: 1) Why do you have so many parenthesis in the SQL statement? As far as I can see, you don't need any at all. 2) Split the SQL over more lines, to make it human-readable. 3) DO NOT use string concatenation to build a SQL statement with user-supplied text. It will leave you open to [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection) attacks, allowing a hacker to steal your data and/or delete your tables. Use a `PreparedStatement` and parameter markers (`?`). – Andreas Feb 27 '16 at 21:37
  • Thanks for your help. As for all the parentheses they are from access. The SQL I learned was for Oracle, Access doesn't seem to like this. I'm under time pressure for the hand in so I generated the sql by creating the query in access design view, switching to the sql view and copying and pasting into netbeans. – Javaiskillingme Feb 28 '16 at 22:00

2 Answers2

1

You problem is that you're not checking the return value of next(), and when it returns false, you'll get an exception like that.

Here is a cleaned-up version of your code, which assumes that you have the database connection as a field named conn. Adjust date format as needed.

Improvements are:

  • Added check of next() return value (fix of original problem)
  • Use of PreparedStatement to prevent SQL Injection vulnerabilities
  • Use of try-with-resources for optimal resource management, i.e. to ensure resources (PreparedStatement and ResultSet) are closed and never left dangling
  • Split statements over multiple lines to improve human readability
  • Eliminated unnecessary use of parenthesis in SQL statement
  • Unwrapped JOIN clauses for simpler use
  • Declared all local variables where first used. Added this. to field reference, for improved clarity and safeguard against hiding
String sql = "SELECT DepAirportTable.AirportName" +
                  ", DepAirportTable.AirportDesignaton" +
                  ", FlightTable.DepDate" +
                  ", ArrAirportTable.ArrAirportName" +
                  ", ArrAirportTable.ArrAirportDesignaton" +
              " FROM FlightTable" +
              " JOIN PlaneTable ON PlaneTable.FlightDesignationCode = FlightTable.FlightDesignationCode" +
              " JOIN DepAirportTable ON DepAirportTable.AirportDesignaton = FlightTable.FromAirDes" +
              " JOIN ArrAirportTable ON ArrAirportTable.ArrAirportDesignaton = FlightTable.ToAirDes" +
             " WHERE DepAirportTable.AirportName = ?" +
               " AND FlightTable.DepDate = ?" +
               " AND ArrAirportTable.ArrAirportName = ?";
try (PreparedStatement stmt = this.conn.prepareStatement(sql)) {
    String dateStr = ((JTextField)DateDep.getDateEditor().getUiComponent()).getText();
    java.util.Date date = new SimpleDateFormat("yyyy-MM-dd").parse(dateStr);
    stmt.setString(1, DepCombo.getSelectedItem());
    stmt.setDate  (2, new java.sql.Date(date.getTime()));
    stmt.setString(3, ArrComboBox.getSelectedItem());
    try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
            String departure = rs.getString("AirportName");
            String depDate = rs.getString("DepDate");
            String arrival = rs.getString("ArrAirportName");
            BookingPrevTa.setText("Your flight details are:\n" +
                                  "Departing from " + departure +
                                  " arriving at: " + arrival +
                                  " departing on: " + depDate);
        }
    }
}
Andreas
  • 154,647
  • 11
  • 152
  • 247
0

I am not sure about the function of {} there, if they are looping then it seems like you have placed rs.close() at wrong position. I think you should place it outside the {} if {} are doing what I think they are doing.

riz
  • 91
  • 4