We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Change connectivity from arraylist to database

t38
t38 asked
on
Medium Priority
384 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
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
t38

Author

Commented:
OK thanks, I'm not familiar with PreparedStatement, any info on that pls
CERTIFIED EXPERT
Top Expert 2016

Commented:
t38

Author

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?
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>
        //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)
t38

Author

Commented:
thanks, i'm working on calling insertRow at the moment, the parameters are giving me a problem but i'm working around same..
t38

Author

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.
CERTIFIED EXPERT
Top Expert 2016

Commented:
>>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
t38

Author

Commented:
oh right thanks.
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

Commented:
There should be no select going on. Also you can't use the Statement before it's been created
t38

Author

Commented:
i don't follow what you mean
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
t38

Author

Commented:
Thanks, I'll add this later today and will get back to you
t38

Author

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(?,?,?,?,?,?);
CERTIFIED EXPERT
Top Expert 2016

Commented:
please post code
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

Commented:
You missed out Publisher in setting your parameters
t38

Author

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);
CERTIFIED EXPERT
Top Expert 2016

Commented:
Notionally, it would be
insertRow(0, "", "", "", 0, 0.0);

Open in new window

t38

Author

Commented:
i thought so too but that returns - Unhandled exception type SQLException
CERTIFIED EXPERT
Top Expert 2016

Commented:
Well you need to catch that exception where you call insertRow
CERTIFIED EXPERT
Top Expert 2016

Commented:
You can actually delete 'throws SQLException'  from insertRow
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

Commented:
You're *still* missing Publisher
t38

Author

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)
CERTIFIED EXPERT
Top Expert 2016

Commented:
OK - can you now post the new code?
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

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

You need to increment those indices by one
t38

Author

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();
CERTIFIED EXPERT
Top Expert 2016

Commented:
Yes
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

Commented:
Make sure you've set the db up properly. Try a separate method that just does a select to test it
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

Commented:
You need to get rid of any other code that's making a connection apart from the code in insertRow (the new one)
t38

Author

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

CERTIFIED EXPERT
Top Expert 2016

Commented:

>>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?
t38

Author

Commented:
closed everything, rebooted computer same error returned I'm afraid
CERTIFIED EXPERT
Top Expert 2016

Commented:
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

t38

Author

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);

CERTIFIED EXPERT
Top Expert 2016

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

Try some real data, with an ID that's definitely unused
CERTIFIED EXPERT
Top Expert 2016

Commented:
e.g.
insertRow(909090, "Java for Masochists", "CEHJ", "Technojeeves", 2011, 110.0);

Open in new window

t38

Author

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

insertRow(101, "Java for Masochists", "CEHJ", "Technojeeves", 2011, 110.0);
CERTIFIED EXPERT
Top Expert 2016

Commented:
Well that's what you WILL be doing of course
t38

Author

Commented:
I'm sorry I don't get your point, it's still not taking input via the GUI though?
CERTIFIED EXPERT
Top Expert 2016

Commented:
Probably because you're not giving it real data..?
CERTIFIED EXPERT
Top Expert 2016

Commented:
>> 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

t38

Author

Commented:
CEJH, Thanks a lot for your assistance, much appreciated.
CERTIFIED EXPERT
Top Expert 2016

Commented:
:)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.