title: Java SQL Exception Invalid Cursor State - no current row
body: <p>I've been having a hard time trying to figure this out. First I have an <code>insertProduct(Product p)</code> method that is supposed to check if a product with the specified code exists in the database. If so, this method will display an error message. Otherwise, it should add the product to the database and print it to the console. I'm not sure if I'm doing that correctly. </p>
  
  <p>Second, the <code>deleteProduct(Product p)</code> method is supposed to delete the product that was added by the <code>insertProduct</code> method. Now the thing is that I keep getting a SQL Exception when I try to add the product and then the <code>deleteProduct</code> method just keeps deleting the products that are in the database one by one each time the program is run until non are left. I'm not sure what's wrong with both of these methods.</p>
  
  <p>Console Output:</p>
  
  <pre><code>Derby has been started.
  
  Product list:
  bvbn    Murach's Beginning Visual Basic .NET        $49.50
  cshp    Murach's C#                                 $49.50
  java    Murach's Beginning Java                     $49.50
  jsps    Murach's Java Servlets and JSP              $49.50
  mcb2    Murach's Mainframe COBOL                    $59.50
  sqls    Murach's SQL for SQL Server                 $49.50
  zjcl    Murach's OS/390 and z/OS JCL                $62.50
  
  First product:
  bvbn    Murach's Beginning Visual Basic .NET        $49.50
  
  Last product:
  zjcl    Murach's OS/390 and z/OS JCL                $62.50
  
  Product by code: cshp
  cshp    Murach's C#                                 $49.50
  
  Insert test: 
  java.sql.SQLException: Invalid cursor state - no current row.
  Product list:
  bvbn    Murach's Beginning Visual Basic .NET        $49.50
  cshp    Murach's C#                                 $49.50
  java    Murach's Beginning Java                     $49.50
  jsps    Murach's Java Servlets and JSP              $49.50
  mcb2    Murach's Mainframe COBOL                    $59.50
  sqls    Murach's SQL for SQL Server                 $49.50
  zjcl    Murach's OS/390 and z/OS JCL                $62.50
  
  Delete test: 
  zjcl    Murach's OS/390 and z/OS JCL                $62.50
  
  Product list:
  bvbn    Murach's Beginning Visual Basic .NET        $49.50
  cshp    Murach's C#                                 $49.50
  java    Murach's Beginning Java                     $49.50
  jsps    Murach's Java Servlets and JSP              $49.50
  mcb2    Murach's Mainframe COBOL                    $59.50
  sqls    Murach's SQL for SQL Server                 $49.50
  
  Derby has been shut down.
  </code></pre>
  
  <p>Code:</p>
  
  <pre><code>   import java.sql.*;
  
  public class DBTesterApp
  {
  private static Connection connection = null;
  private static Product p = null ;
  public static void main(String args[])
  {
      // get the connection and start the Derby engine
      connection = MurachDB.getConnection();
      if (connection != null)
          System.out.println("Derby has been started.\n");
  
      // select data from database
      printProducts();
      printFirstProduct();
      printLastProduct();
      printProductByCode("cshp");
  
      // modify data in the database
       p = new Product("test", "Test Product", 49.50);        
      insertProduct(p);
      printProducts();
  
      deleteProduct(p);
      printProducts();
  
      // disconnect from the database
      if (MurachDB.disconnect())
          System.out.println("Derby has been shut down.\n");
   }
  
   public static void printProducts()
   {
  
     // Product p = null ;
      try (Statement statement = connection.createStatement();
           ResultSet rs = statement.executeQuery("SELECT * FROM Products"))
      {            
  
  
          System.out.println("Product list:");
          while(rs.next())
          {
              String code = rs.getString("ProductCode");
              String description = rs.getString("Description");
              double price = rs.getDouble("Price");
  
           p = new Product(code, description, price);
  
              printProduct(p);
          }
          System.out.println();
      }
      catch(SQLException e)
      {
          e.printStackTrace();  // for debugging
      }
    }
  
    public static void printFirstProduct()
    {
  
       try(Statement statement = connection.createStatement(
               ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_UPDATABLE);
  
           ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
  
           System.out.println("First product:");
  
           rs.first();
           rs.last();
  
           if(rs.isFirst() == false){
            rs.previous();
           }
  
           if(rs.isLast() == false){
               rs.next();
           }
  
           rs.absolute(1);
           String code = rs.getString(1);
           String description = rs.getString(2);
           double price = rs.getDouble(3);
  
           p = new Product(code , description , price);
           printProduct(p);
           System.out.println();
       }
  
       catch(SQLException e){
            e.printStackTrace();
           }
    }
  
   public static void printLastProduct()
   {
       try(Statement statement = connection.createStatement(
               ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_UPDATABLE);
  
           ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
           System.out.println("Last product:");
  
  
           rs.first();
           rs.last();
  
           if(rs.isFirst() == false){
            rs.previous();
           }
  
           if(rs.isLast() == false){
               rs.next();
           }
  
           rs.absolute(7);
           String code = rs.getString(1);
           String description = rs.getString(2);
           double price = rs.getDouble(3);
  
           p = new Product(code, description, price);
           printProduct(p);
           System.out.println();
  
  
  
       }
  
       catch(SQLException e){
           e.printStackTrace();
       }
  }
  
    public static void printProductByCode(String productCode)
    {
  
         String sql  = 
             "SELECT ProductCode, Description, Price " + 
             "FROM  Products "  +    
             "WHERE ProductCode = ?";
  
     try(PreparedStatement ps = connection.prepareStatement(sql);){
  
         ps.setString(1, productCode);
         ResultSet rs = ps.executeQuery();
  
         if(rs.next()){   
           String description = rs.getString("Description");
           double price = rs.getDouble("Price");
           p = new Product(productCode, description, price);
           System.out.println("Product by code: " + productCode);
           printProduct(p);
          }
            else{
            rs.close();
                }
  
     }
  
  
     catch(SQLException e){
      System.err.println(e);
  
     }
  
  
      System.out.println();
    }
  
    public static void insertProduct(Product p)
    {
      System.out.println("Insert test: ");
  
  
      //check if product code exists in database
     try(Statement statement = connection.createStatement();
          ResultSet rs = statement.executeQuery("SELECT * FROM Products")){
  
     String code =   rs.getString(1);
  
  
    if (p.getCode().equals(code) ){
         System.out.println("Error: This product is already in the database!");
         } 
  
  
      else{
      String sql = 
              "INSERT INTO Products (productCode, Description, Price) " +
              "VALUES (?, ?, ?)";
  
      try(PreparedStatement ps = connection.prepareStatement(sql)){
  
  
          ps.setString(1, p.getCode());
          ps.setString(2, p.getDescription());
          ps.setDouble(3, p.getPrice());
          ps.executeUpdate();
         }
  
  
       catch(SQLException e){
            System.err.println(e);
        } 
  
  
      } //end else
  
        printProduct(p);
      System.out.println();
  
     }//end try
  
  
          catch(SQLException e ){
          System.out.println(e);
           } 
  }
  
   private static void deleteProduct(Product p)
  {
      System.out.println("Delete test: ");
  
      String sql = "DELETE FROM Products " +
                   "WHERE ProductCode = ?";
  
      try(PreparedStatement ps = connection.prepareStatement(sql)){
          ps.setString(1, p.getCode());
          ps.executeUpdate();
  
      }
  
      catch(SQLException e){
         System.err.println(e);
      }
      // add code that deletes the specified product from the database
      // if a product with the specified code doesn't exist, display an error message
  
      printProduct(p);
      System.out.println();
  }
  
  // use this method to print a Product object on a single line
  private static void printProduct(Product p)
  {
      String productString =
          StringUtils.padWithSpaces(p.getCode(), 8) +
          StringUtils.padWithSpaces(p.getDescription(), 44) +
          p.getFormattedPrice();
  
      System.out.println(productString);
  }
  }
  </code></pre>