[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Change connectivity from arraylist to database

Hi, The attached code is for a GUI class that interacts with a class 'BookShelf' to add a book etc. to an arraylist also called BookShelf. There's also some code in the BookGUI class that interacts with a database called BookShelf to insert and retrieve information from the database.  I need to change the code so the GUI works with the database to add book details to the database and preferably maintain the other connectivity with the arraylist (although that's not essential), as you can see from the code below the 'insert' code is already there but I need some advice on how to change the code so that the Add Book button on the GUI adds information to the database and not the arraylist, any help appreciated.
package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost, Statement statement) throws SQLException

	{
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", "
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);

  //System.out.println("statn: " + statement);



	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       {
	    	  System.out.println("Failed to insert the following data: "
	    			         + ID   + ","
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }

	//link.close();

	//new Insert();


	  }

/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    Connection link;
   Statement statement;

    try{
        //Step 1g
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        //Step 2
        link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
        statement = link.createStatement();

                    insertRow(47, "The Big Sky", "A.B. Guthrie Jr.", "Mariner", 2000, 20.0, statement);
                    
        // in fact you'll probably call many times insert method to insert many rows
      //          you can in future use PreparedStatement, especially if you need multiple inserts
                    
                    try{
                    	  
                    	  // Step 3
                    	  statement = link.createStatement();
                    	  String select = "Select * from BookShelf";
                    	  //String select = "Select acctNum, firstName, surname, Balance from Accounts";
                    	  
                    	  
                    	  // Step 4
                    	  results = statement.executeQuery(select);
                    	}
                    	catch(SQLException e){
                    	   System.out.println("Cannot execute query");
                    	   e.printStackTrace();
                    	   System.exit(1);
                    		  }

                    	try{
                    	  // Step 5
                    	  while (results.next()){
                    	System.out.println("ID:    " + results.getInt(1));
                    	System.out.println("Title: " + results.getString(2) + " " + 
                    	results.getString(3));
                    	     System.out.println("Author:        " + results.getString(4));
                    	     System.out.println("Publisher:        " + results.getString(5));
                    	     System.out.println("Year:        " + results.getInt(6));
                    	     //System.out.println("Cost:        " + results.getDouble(7));
                    	     System.out.println();
                    	 }
                    	}
                    	catch(SQLException e){
                    	     System.out.println("Error retrieving data");
                    	    e.printStackTrace();
                    	   System.exit(1);
                    	}
       
       



        link.close();

    //	System.out.println("linl: " + link);



        // System.out.println("statn: " + statement);

    }
    catch(ClassNotFoundException e){
        System.out.println("Unable to load driver");
        e.printStackTrace();
        System.exit(1);
    }
    catch(SQLException e){
        System.out.println("Cannot connect to the database");
        e.printStackTrace();
        System.exit(1);
    }   catch (Exception ex){

        System.out.println("Error " + ex.toString());
        ex.printStackTrace();

    }


	 


    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    JButton button3 = new JButton("Size of BookShelf");
    content.add(button3);
    button3.addActionListener(this);

    JButton button4 = new JButton("Highest Price Paid");
    content.add(button4);
    button4.addActionListener(this);


}
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        bookShelf.addBook(book);

        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    else if (e.getActionCommand().equals("Cost of BookShelf"))
    {
       String message = "The book shelf value is " + bookShelf.costOfBookShelf() + "Euro";
       JOptionPane.showMessageDialog(this, message);
    }
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "");
    }
    else if (e.getActionCommand().equals("Highest Price Paid"))
    {
        String message = "The highest price paid for a book is: " + bookShelf.highestPricePaid() + "Euro";
        JOptionPane.showMessageDialog(this, message);
    }
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
t38
Asked:
t38
  • 25
  • 24
1 Solution
 
CEHJCommented:
You need to get the button to call insertRow. You should use a PreparedStatement to do that or you could get into trouble with quoting. It should also make it much easier generally and the code cleaner
0
 
t38Author Commented:
OK thanks, I'm not familiar with PreparedStatement, any info on that pls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
t38Author Commented:
"you need to get the button to call insertRow"  I'm having difficulty getting this to work, could you give me a bit more info in terms of what i need to do please?
0
 
CEHJCommented:
>>
        //Add an instance of book to the ArrayList
            bookShelf.addBook(book);
>>

is what you're doing at the moment. Call insertRow instead with the correct parameters. My advice is

a. Use PreparedStatement
b. Do all the db work in insertRow, including connecting and disconnecting (which you won't do often enough to worry about in a gui)
0
 
t38Author Commented:
thanks, i'm working on calling insertRow at the moment, the parameters are giving me a problem but i'm working around same..
0
 
t38Author Commented:
if i replace the arraylist with - insertRow(0, "", "", "", 0, 0.0);

i get the following error -

The method insertRow(int, String, String, String, int, double, Statement) in the type BookGUI is not applicable for the arguments (int, String, String, String,
 int, double)

not sure how to deal with Statement in this instance?

thanks for your other suggestions, i'd like to get it working this 'simple' way first before worrying about preparestatement as i'm completely unfamiliar with it.
0
 
CEHJCommented:
>>not sure how to deal with Statement in this instance?

As i mentioned, you should get rid of that parameter and deal with it *inside* the method
0
 
t38Author Commented:
oh right thanks.
0
 
t38Author Commented:
is this what you mean?  i'm getting a sql exception error at insertRow (0, "", "","",0,0.0)
package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	Connection link;
	Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException

	{
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", "
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);

  //System.out.println("statn: " + statement);



	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       {
	    	  System.out.println("Failed to insert the following data: "
	    			         + ID   + ","
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }

	       //Connection link;
	       //Statement statement;

	        try{
	            //Step 1g
	            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	            //Step 2
	            link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
	            statement = link.createStatement();

	                        insertRow(70, "The Big Sky", "A.B. Guthrie Jr.", "Mariner", 2000, 20.0);
	                        
	            
	                        
	                        try{
	                        	  
	                        	  // Step 3
	                        	  statement = link.createStatement();
	                        	  String select = "Select * from BookShelf";
	                        	  //String select = "Select acctNum, firstName, surname, Balance from Accounts";
	                        	  
	                        	  
	                        	  // Step 4
	                        	  results = statement.executeQuery(select);
	                        	}
	                        	catch(SQLException e){
	                        	   System.out.println("Cannot execute query");
	                        	   e.printStackTrace();
	                        	   System.exit(1);
	                        		  }

	                        	try{
	                        	  // Step 5
	                        	  while (results.next()){
	                        	System.out.println("ID:    " + results.getInt(1));
	                        	System.out.println("Title: " + results.getString(2) + " " + 
	                        	results.getString(3));
	                        	     System.out.println("Author:        " + results.getString(4));
	                        	     System.out.println("Publisher:        " + results.getString(5));
	                        	     System.out.println("Year:        " + results.getInt(6));
	                        	     //System.out.println("Cost:        " + results.getDouble(7));
	                        	     System.out.println();
	                        	 }
	                        	}
	                        	catch(SQLException e){
	                        	     System.out.println("Error retrieving data");
	                        	    e.printStackTrace();
	                        	   System.exit(1);
	                        	}
	           
	           
            link.close();

	        }
	        catch(ClassNotFoundException e){
	            System.out.println("Unable to load driver");
	            e.printStackTrace();
	            System.exit(1);
	        }
	        catch(SQLException e){
	            System.out.println("Cannot connect to the database");
	            e.printStackTrace();
	            System.exit(1);
	        }   catch (Exception ex){

	            System.out.println("Error " + ex.toString());
	            ex.printStackTrace();

	        }

  }

/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    


    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    }
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(0, "", "", "", 0, 0.0);

        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
   
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:
There should be no select going on. Also you can't use the Statement before it's been created
0
 
t38Author Commented:
i don't follow what you mean
0
 
CEHJCommented:
You need something more like
public void insertRow(int ID, String Title, String Author,
        String Publisher, int Year, double Cost) throws SQLException {
        String sql = "INSERT INTO BookShelf VALUES (?, ?, ?, ?, ?, ?)";
        //String insert = "INSERT INTO BookShelf" + " VALUES (" + ID + ", " + "'" + Title + "', " + "'" + Author + "','" + Publisher + "', " + Year + "," + Cost + ")";


        Connection link = null;
        try {
            //Step 1g
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Step 2
            link = DriverManager.getConnection("jdbc:odbc:BookShelf", "", "");
            PreparedStatement statement = link.prepareStatement(sql);
	    statement.setInt(1, ID);
	    statement.setString(2, Title);
	    // SET ALL THE OTHERS
	    statement.executeUpdate();
	    link.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load driver");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Cannot connect to the database");
            e.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Error " + ex.toString());
            ex.printStackTrace();
        }
	finally {
	    try { link.close() ; } catch(Exception e) { /* ignore */ }	
	}
    }

Open in new window

0
 
t38Author Commented:
Thanks, I'll add this later today and will get back to you
0
 
t38Author Commented:
i've applied the changes as suggested but when i try to add insertRow (to replace bookshelf.addBook(book) in any of the following formats i'm getting errors -

//insertRow(0, "", "", "", 0, 0.0);
//insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
//insertRow(int, String, String, String, int, Double);
//insertRow(?,?,?,?,?,?);
0
 
CEHJCommented:
please post code
0
 
t38Author Commented:

package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost) throws SQLException

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setInt(4,Year);
				  statement.setDouble(5,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 

	








	
	




					  
/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    }
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       //insertRow(0, "", "", "", 0, 0.0);
       //insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
       //insertRow(Setint, String, String, String, int, Double);
       //insertRow(?,?,?,?,?,?);



       
       
        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
   
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:
You missed out Publisher in setting your parameters
0
 
t38Author Commented:
thanks, none of these work though, i assume on of them should at this point? getting various errors use one or the other

//insertRow(0, "", "", "", 0, 0.0);
       //insertRow(?,?,?,?,?,?);
       //insertRow(int, String, String, String, int, double);
       //insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
0
 
CEHJCommented:
Notionally, it would be
insertRow(0, "", "", "", 0, 0.0);

Open in new window

0
 
t38Author Commented:
i thought so too but that returns - Unhandled exception type SQLException
0
 
CEHJCommented:
Well you need to catch that exception where you call insertRow
0
 
CEHJCommented:
You can actually delete 'throws SQLException'  from insertRow
0
 
t38Author Commented:
Hi,
I'm getting the following error when I run the code and enter book details into the GUI

Cannot connect to the database
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
      at database.BookGUI.insertRow(BookGUI.java:68)
      at database.BookGUI.actionPerformed(BookGUI.java:198)
      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.DefaultButtonModel.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.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(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)
package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost)

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setInt(4,Year);
				  statement.setDouble(5,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 

	








	
	
	
					  
/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    }
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(0, "", "", "", 0, 0.0);
       //insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
       //insertRow(Setint, String, String, String, int, Double);
       //insertRow(?,?,?,?,?,?);



       
       
        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
   
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:
You're *still* missing Publisher
0
 
t38Author Commented:
Oh yes, I must have ctrl + z once too often.  the same error returns with publisher added

Cannot connect to the database
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
      at database.BookGUI.insertRow(BookGUI.java:69)
      at database.BookGUI.actionPerformed(BookGUI.java:199)
      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.DefaultButtonModel.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.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(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)
0
 
CEHJCommented:
OK - can you now post the new code?
0
 
t38Author Commented:

package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost)

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setString(4,Publisher);
				  statement.setInt(4,Year);
				  statement.setDouble(5,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 

	








	
	
	
					  
/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    }
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(0, "", "", "", 0, 0.0);
       //insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
       //insertRow(Setint, String, String, String, int, Double);
       //insertRow(?,?,?,?,?,?);



       
       
        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
   
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:
>>      
                    statement.setInt(4,Year);
                          statement.setDouble(5,Cost);
>>

You need to increment those indices by one
0
 
t38Author Commented:
i don't get you, like this?

 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                          link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
                          PreparedStatement statement = link.prepareStatement(sql);
                          statement.setInt(1,ID);
                          statement.setString(2,Title);
                          statement.setString(3,Author);
                          statement.setString(4,Publisher);
                          statement.setInt(5,Year);
                          statement.setDouble(6,Cost);
                          statement.executeUpdate();
                          link.close();
0
 
CEHJCommented:
Yes
0
 
t38Author Commented:
this error is returned when i run and try to add the information from the gui  -

Cannot connect to the database
java.sql.SQLException: General error
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
      at database.BookGUI.insertRow(BookGUI.java:69)
      at database.BookGUI.actionPerformed(BookGUI.java:199)
      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.DefaultButtonModel.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.pumpOneEventForFilters(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForFilter(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)

package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost)

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setString(4,Publisher);
				  statement.setInt(5,Year);
				  statement.setDouble(6,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 

	
/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    }
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(0, "", "", "", 0, 0.0);
       //insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
       //insertRow(Setint, String, String, String, int, Double);
       //insertRow(?,?,?,?,?,?);



       
       
        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
   
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:
Make sure you've set the db up properly. Try a separate method that just does a select to test it
0
 
t38Author Commented:
yes i checked that just in case, if i use the code below that just inserts a row outside the gui it populates the database
package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost, Statement statement) throws SQLException

	{
		  String insert = "INSERT INTO BookShelf"
              + " VALUES (" + ID   + ", "
                            + "'" + Title + "', "
                            + "'" + Author   + "','"
                            + Publisher + "', " + Year +"," +  Cost   + ")";

  System.out.println(insert);

  //System.out.println("statn: " + statement);



	       int result = statement.executeUpdate(insert);
	       if (result == 0)
	       {
	    	  System.out.println("Failed to insert the following data: "
	    			         + ID   + ","
                             + Title + ","
                             + Author   + ","
                             + Publisher   + ","
                             + Year   + ","
                             + Cost);
	  }

	//link.close();

	//new Insert();


	  }

/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    Connection link;
   Statement statement;

    try{
        //Step 1g
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        //Step 2
        link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
        statement = link.createStatement();

                    insertRow(91, "The Big Sky", "A.B. Guthrie Jr.", "Mariner", 2000, 20.0, statement);
                    
        // in fact you'll probably call many times insert method to insert many rows
      //          you can in future use PreparedStatement, especially if you need multiple inserts
                    
                    try{
                    	  
                    	  // Step 3
                    	  statement = link.createStatement();
                    	  String select = "Select * from BookShelf";
                    	  //String select = "Select acctNum, firstName, surname, Balance from Accounts";
                    	  
                    	  
                    	  // Step 4
                    	  results = statement.executeQuery(select);
                    	}
                    	catch(SQLException e){
                    	   System.out.println("Cannot execute query");
                    	   e.printStackTrace();
                    	   System.exit(1);
                    		  }

                    	try{
                    	  // Step 5
                    	  while (results.next()){
                    	System.out.println("ID:    " + results.getInt(1));
                    	System.out.println("Title: " + results.getString(2) + " " + 
                    	results.getString(3));
                    	     System.out.println("Author:        " + results.getString(4));
                    	     System.out.println("Publisher:        " + results.getString(5));
                    	     System.out.println("Year:        " + results.getInt(6));
                    	     //System.out.println("Cost:        " + results.getDouble(7));
                    	     System.out.println();
                    	 }
                    	}
                    	catch(SQLException e){
                    	     System.out.println("Error retrieving data");
                    	    e.printStackTrace();
                    	   System.exit(1);
                    	}
       
       



        link.close();

    //	System.out.println("linl: " + link);



        // System.out.println("statn: " + statement);

    }
    catch(ClassNotFoundException e){
        System.out.println("Unable to load driver");
        e.printStackTrace();
        System.exit(1);
    }
    catch(SQLException e){
        System.out.println("Cannot connect to the database");
        e.printStackTrace();
        System.exit(1);
    }   catch (Exception ex){

        System.out.println("Error " + ex.toString());
        ex.printStackTrace();

    }


	 


    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    JButton button3 = new JButton("Size of BookShelf");
    content.add(button3);
    button3.addActionListener(this);

    JButton button4 = new JButton("Highest Price Paid");
    content.add(button4);
    button4.addActionListener(this);


}
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        bookShelf.addBook(book);

        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    else if (e.getActionCommand().equals("Cost of BookShelf"))
    {
       String message = "The book shelf value is " + bookShelf.costOfBookShelf() + "Euro";
       JOptionPane.showMessageDialog(this, message);
    }
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
    else if (e.getActionCommand().equals("Highest Price Paid"))
    {
        String message = "The highest price paid for a book is: " + bookShelf.highestPricePaid() + "Euro";
        JOptionPane.showMessageDialog(this, message);
    }
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:
You need to get rid of any other code that's making a connection apart from the code in insertRow (the new one)
0
 
t38Author Commented:
Hi, This is what I'm using and as far as I can see insertRow is the only code trying to make a connection?


package database;

import java.awt.FlowLayout;
import java.awt.Container;
import java.awt.event.ActionEvent;
import javax.swing.*;
import java.awt.event.ActionListener;
import java.sql.*;

	/**
	 * This class operates as the graphical user interface that interacts with the
	 * Book and BookShelf classes to add Books to the array and return
	 * a graphical representation of information from the BookShelf methods.
	 *
	 *
	 */

public class BookGUI extends JFrame implements ActionListener
{
	// Declare the instance variables for the class
	private int ID = 0;
	private String title  = "";
	private String author  = "";
	private String publisher  = "";
	private int year = 0;
	private double cost = 0;
    private boolean goodInput = false;
    ResultSet results;
    


	// Define the exact dimensions of the graphical interface
    private static final int WIDTH = 600;
   	private static final int HEIGHT = 90;
   	private BookShelf bookShelf;


	//Connection link;
	//Statement statement;
	//ResultSet results;


	public static void main(String[] args){

		BookGUI gui = new BookGUI( );
	       gui.setVisible(true);


    }

	public void insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost)

	{
		  String sql = "INSERT INTO BookShelf VALUES(?, ?, ?, ?, ?, ?)";
			  
			  Connection link = null;
			  try{
				  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				  link = DriverManager.getConnection("jdbc:odbc:BookShelf","","");
				  PreparedStatement statement = link.prepareStatement(sql);
				  statement.setInt(1,ID);
				  statement.setString(2,Title);
				  statement.setString(3,Author);
				  statement.setString(4,Publisher);
				  statement.setInt(5,Year);
				  statement.setDouble(6,Cost);
				  statement.executeUpdate();
				  link.close();
				  
			  } catch (ClassNotFoundException e) {
		            System.out.println("Unable to load driver");
		            e.printStackTrace();
		        } catch (SQLException e) {
		            System.out.println("Cannot connect to the database");
		            e.printStackTrace();
		        } catch (Exception ex) {
		            System.out.println("Error " + ex.toString());
		            ex.printStackTrace();
		        }
			finally {
			    try { link.close() ; } catch(Exception e) { /* ignore */ }	
			}
		    } 

	
/**
 * Create the GUI frame with buttons and listeners
 */
public BookGUI( )   {

    // Create a new instance of BookShelf
	bookShelf = new BookShelf();

    JFrame frame = this;

    // Create the main frame's menu bar.
    JMenuBar menubar = new JMenuBar();
	frame.setJMenuBar(menubar);
	frame.pack();
	frame.setVisible(true);

	//  Create the File menu
	JMenu fileMenu = new JMenu("File");
	menubar.add(fileMenu);

	// Create a Quit option on the File menu
	JMenuItem quitItem = new JMenuItem("Quit");
	fileMenu.add(quitItem);
	quitItem.addActionListener(this);

	// create the About menu
	JMenu aboutMenu = new JMenu ("About");
	menubar.add(aboutMenu);

	JMenuItem infoItem = new JMenuItem("Info");
	aboutMenu.add(infoItem);
	infoItem.addActionListener(this);

	//Set the Size of the frame
	setSize(WIDTH, HEIGHT);

	// Create a new instance of the WindowDestroyer class
    addWindowListener(new WindowDestroyer( ));
    setTitle("GUI Assignment");
    Container content = getContentPane( );


    // Arrange the buttons on the GUI in a Flow Layout
    content.setLayout(new FlowLayout());

    // Set up the required buttons on the GUI and the corresponding listener for that action.
    JButton button1 = new JButton("Add Book");
    content.add(button1);
    button1.addActionListener(this);

    JButton button2 = new JButton("Cost of BookShelf");
    content.add(button2);
    button2.addActionListener(this);

    }
/**
* These methods perform the corresponding action when the buttons on the GUI are clicked.
* Exception handling in these methods ensure the correct values are entered.  If not, the
* user is prompted to re-enter the data correctly.
*
* @param e holds the event that was fired.
*/
public void actionPerformed(ActionEvent e)
{
	if (e.getActionCommand().equals("Add Book"))
    {
	   Book book = new Book("", "", 0, "", 0);
	   ID = Integer.parseInt(JOptionPane.showInputDialog("ID"));
	   title = JOptionPane.showInputDialog("Title");
       author = JOptionPane.showInputDialog("Author");
       publisher = JOptionPane.showInputDialog("Publisher");

       do{
          try
          {
    	     cost = Double.parseDouble(JOptionPane.showInputDialog("Cost"));
    	     book.setCost(cost);
    	     goodInput = true;
    	  }
          catch (Exception cE){
             JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for cost");
    	  }
       }while (!goodInput);

	       goodInput = false;
       do{
           try
           {
     	     year = Integer.parseInt(JOptionPane.showInputDialog("Year"));
     	     book.setYear(year);
     	     goodInput = true;
     	  }
           catch (Exception yE){
              JOptionPane.showMessageDialog(this, "Numerical entry required. Please re-enter a value for year");
     	  }
        }while (!goodInput);

        //Add an instance of book to the ArrayList
        //bookShelf.addBook(book);
       insertRow(0, "", "", "", 0, 0.0);
       //insertRow(int ID, String Title, String Author, String Publisher, int Year, double Cost);
       //insertRow(Setint, String, String, String, int, Double);
       //insertRow(?,?,?,?,?,?);



       
       
        String message =  "The Title of the book is " + title
                       +  " the Author of the Book is "  + author
                       + " it's published by " + publisher
                       + " in " + year
                       + " and it costs " + cost + " euro ";
        JOptionPane.showMessageDialog(null, message, "Book Details", JOptionPane.PLAIN_MESSAGE);
    }
    else if (e.getActionCommand().equals("Size of BookShelf"))
    {
       String message = "The book shelf has " + bookShelf.sizeOfBookshelf() + " book(s)";
       JOptionPane.showMessageDialog(this, message);
    }
    
	else if (e.getActionCommand().equals("Info"))
    {
    	JOptionPane.showMessageDialog(this, "Tony O'Keeffe - GUI Assignment One");
    }
   
    else
    {
        System.exit( 0 );
    }

 } // end actionPerformed

}

Open in new window

0
 
CEHJCommented:

>>this error is returned when i run and try to add the information from the gui  -

Close all other connections (from other apps as well) and try again. Is that still the exact same stack trace?
0
 
t38Author Commented:
closed everything, rebooted computer same error returned I'm afraid
0
 
CEHJCommented:
I can only imagine, then, that the db/driver doesn't support PreparedStatement. Try the following instead
public void insertRow(int ID, String Title, String Author,
        String Publisher, int Year, double Cost) {
        String sqlTemplate = "INSERT INTO BookShelf VALUES(%d, '%s', '%s', '%s', %d, %f)";

        Connection link = null;

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            link = DriverManager.getConnection("jdbc:odbc:BookShelf", "", "");

            Statement statement = link.createStatement();
	    String sql = String.format(sqlTemplate, ID, Title, Author, Publisher, Year, Cost);
            statement.executeUpdate(sql);
            link.close();
        } catch (ClassNotFoundException e) {
            System.out.println("Unable to load driver");
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println("Cannot connect to the database");
            e.printStackTrace();
        } catch (Exception ex) {
            System.out.println("Error " + ex.toString());
            ex.printStackTrace();
        } finally {
            try {
                link.close();
            } catch (Exception e) { /* ignore */
            }
        }
    }

Open in new window

0
 
t38Author Commented:
no that returns the same error and the same two lines of code are identified as the problem -


statement.executeUpdate(sql);
insertRow(0, "", "", "", 0, 0.0);

0
 
CEHJCommented:
>>insertRow(0, "", "", "", 0, 0.0);

Try some real data, with an ID that's definitely unused
0
 
CEHJCommented:
e.g.
insertRow(909090, "Java for Masochists", "CEHJ", "Technojeeves", 2011, 110.0);

Open in new window

0
 
t38Author Commented:
if i insertRow with real data as suggested it updates the database.......... -

insertRow(101, "Java for Masochists", "CEHJ", "Technojeeves", 2011, 110.0);
0
 
CEHJCommented:
Well that's what you WILL be doing of course
0
 
t38Author Commented:
I'm sorry I don't get your point, it's still not taking input via the GUI though?
0
 
CEHJCommented:
Probably because you're not giving it real data..?
0
 
CEHJCommented:
>> insertRow(0, "", "", "", 0, 0.0);

If you're literally doing that instead of giving it the real data, then it's not going to work

It's got to be something like the following, and of course you're going to have to give it a unique id

insertRow(id, title, author, publisher, year, cost);

Open in new window

0
 
t38Author Commented:
CEJH, Thanks a lot for your assistance, much appreciated.
0
 
CEHJCommented:
:)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 25
  • 24
Tackle projects and never again get stuck behind a technical roadblock.
Join Now