This discussion is archived
5 Replies Latest reply: Aug 7, 2007 10:22 AM by masijade RSS

please help me solve PreparedStatement problem

807600 Newbie
Currently Being Moderated
hello.
i'm having a problem running a PreparedStatement program (shown below). when i enter the name of a product in the text field and click on the check box, i get the following SQLExceptions. there is data in the VideoLibrary database. how can i get rid of these SQLExceptions?

----jGRASP exec: java ViewProductByName

java.sql.SQLException: No data found
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataInteger(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getObject(Unknown Source)
at ViewProductByName.selectProductByName(ViewProductByName.java:115)
at ViewProductByName$1.actionPerformed(ViewProductByName.java:54)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.JToggleButton$ToggleButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

----jGRASP: process aborted by user.
import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
 
public class ViewProductByName extends JFrame{
   JPanel pnlBox = new JPanel();
   JPanel pnlBody = new JPanel();
   JPanel pnlFooter = new JPanel();
   JTextField vp = new JTextField(25);
   JCheckBox cvp = new JCheckBox("View Product(s)");  
   JButton rvp = new JButton("Return to View Product Details Menu");
   JLabel lbl = new JLabel("Enter Query:");
   JTextArea jta = new JTextArea();
   Container contentpane;
   Connection db;
   PreparedStatement ps;
     
   public ViewProductByName(){
      super("View Product By Name");
      contentpane = getContentPane();
      contentpane.setLayout(new BorderLayout());
      pnlBox.add(lbl);
      pnlBox.add(vp);
      pnlBox.add(cvp);
      jta.setFont(new Font("Serif", Font.PLAIN, 12));
      jta.setLineWrap(true);
      jta.setWrapStyleWord(true);
      jta.setEditable(false);
      JScrollPane jsp = new JScrollPane(jta);
      Dimension d = new Dimension();
      d.setSize(500, 500);
      jsp.setPreferredSize(d);
      pnlBody.add(jsp, BorderLayout.CENTER);
      pnlFooter.add(rvp);
      contentpane.add(pnlBox,BorderLayout.NORTH);
      contentpane.add(pnlBody,BorderLayout.CENTER);
      contentpane.add(pnlFooter,BorderLayout.SOUTH);
      pack();
      setLocationRelativeTo(null);
      setVisible(true);
          
      cvp.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
            makeConnection();
            setHostURL();
            selectProductByName();
            closeDB();
         }
      });
          
      rvp.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
            setVisible(false);
            //ViewProductDetails vpd = new ViewProductDetails();
            //vpd.setVisible(true); 
         }
      });
   } 
     
   public void makeConnection(){ 
      try{ 
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 
      } 
      catch(Exception e){ 
         System.out.println("Problem loading the driver"); 
      } 
   }
     
   public void setHostURL(){ 
      String url = "jdbc:odbc:VideoLibrary";
          
      try{ 
         db = DriverManager.getConnection(url,"",""); 
      } 
      catch(Exception e){ 
         System.out.println("Could not initialise the database"); 
         e.printStackTrace(); 
      } 
   }
     
   public void selectProductByName(){
      String n = vp.getText();
      
      try{
         String q = "SELECT ProductNumber, Name, RentalPrice, AgeCategory, Type, Year FROM Product WHERE Name = ?";
         ps = db.prepareStatement(q);
         DatabaseMetaData dbmd = db.getMetaData(); 
         ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
         ps.setString(1, n);
         rs = ps.executeQuery();
         ResultSetMetaData rsmd = rs.getMetaData();
      
         for(int i = 1; i <= rsmd.getColumnCount(); i++){
            jta.append(rsmd.getColumnName(i) + "    ");
         }
         jta.append("\n");
 
         while(rs.next()){
            int productNumber = rs.getInt(1);
            String name = rs.getString(2);
            float rentalPrice = rs.getFloat(3);
            String ageCategory = rs.getString(4);
            String type = rs.getString(5);
            int year = rs.getInt(6);
               
            for(int i = 1; i <= rsmd.getColumnCount(); i++){
               jta.append(rs.getObject(i) + "     ");
            }
            jta.append("\n");
         }
      }
      catch(SQLException ea){
         ea.printStackTrace();
      }
   }
     
   public void closeDB(){ 
      try{  
         if(ps != null){ 
            ps.close(); 
         } 
         if(db != null){ 
            db.close(); 
         } 
      } 
      catch(Exception e){ 
         System.out.println("Could not close the current connection"); 
         e.printStackTrace(); 
      } 
   }
      
   public static void main(String[] args){
      new ViewProductByName();
   }
}
  • 1. Re: please help me solve PreparedStatement problem
    807600 Newbie
    Currently Being Moderated
    should i use JButton instead of JCheckBox?
  • 2. Re: please help me solve PreparedStatement problem
    807600 Newbie
    Currently Being Moderated
    Hi,

    In selectProductByName() I can see that you are using same result set
    for executing the query and databasemetadata.
    ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
             ps.setString(1, n);
             rs = ps.executeQuery();
             ResultSetMetaData rsmd = rs.getMetaData();
    After executing the prepared statement you are using the result set to get the metadata. But the result set now will contain the result of the SQL query.

    Hope it helps

    Thanks
  • 3. Re: please help me solve PreparedStatement problem
    807600 Newbie
    Currently Being Moderated
    Hi,

    In selectProductByName() I can see that you are using
    same result set
    for executing the query and databasemetadata.
    ResultSet rs = dbmd.getTables(null,
    null, null, new String[]{"TABLE"});
    ps.setString(1, n);
    rs = ps.executeQuery();
    ResultSetMetaData rsmd =
    rs.getMetaData();
    After executing the prepared statement you are using
    the result set to get the metadata. But the result
    set now will contain the result of the SQL query.

    Hope it helps

    Thanks
    There is nothing wrong with this; in fact it's the way you would ordinarilly do it to capture the column names, etc.
  • 4. Re: please help me solve PreparedStatement problem
    807600 Newbie
    Currently Being Moderated
    This code here:
    try{
      String q = "SELECT ProductNumber, Name, RentalPrice, AgeCategory, Type, Year FROM Product WHERE Name = ?";
      ps = db.prepareStatement(q);
      DatabaseMetaData dbmd = db.getMetaData(); 
    // Add this:
      System.out.println("Got the dbmd");
      ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
    // And this:
      System.out.println("Got the rs from the 'getTables' method");
      ps.setString(1, n);
      rs = ps.executeQuery();
    // And this:
      System.out.println("rs is null? ___"+ (rs == null));
      ResultSetMetaData rsmd = rs.getMetaData();
    // And this:
      System.out.println("Got the rsmd");
  • 5. Re: please help me solve PreparedStatement problem
    masijade Explorer
    Currently Being Moderated
    Please do not cross post. Stick with the thread in the JDBC forum. Also, when you read the resultset, you read it once, all the way through, assigning the values to variables, then read it again to insert it into the JTextArea. I don't believe you can read the same field twice when using the JDBC-ODBC Bridge (at least not when using MS Access).