Java

Java RSS

help with database assignment

•

Join Date: Aug 2004

Posts: 15

Reputation: axiss is an unknown quantity at this point

Solved Threads: 0

axiss

Offline

Newbie Poster

help with database assignment

Aug 14th, 2004

Well basically for the assignment I have to write a project that reads data from a .mdb database file and stores this data into a 2D array, then lets the user manipulate the data in the array (stuff like adding, editing, sorting, deleting and searching data) by using GUI and then finally writing back from the array into the database.

Everything in the program works exept for where I have to write to and from the array. :cry:

This is my program so far:

The class that creates an object that the array will consist of

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
public class CarObject  
{  	
 	private String make;  	  	
  	private String model;  	  	
  	private int year;   	   	
  	private String price;   
 
//Default constructor     
	public CarObject()   	
        { }   	   	
 
//parameterised constructor  	 
        public CarObject(String m, String mod, int y, String p)
   	{ 
  		make = m;  
	 	model = mod;  
	 	year = y;  
	 	price = p;  
   	}  
 
/**Mutator method for make field  	 
*@param m make as String  	 
*/  	public void setMake(String m)
  	{ 
 		make = m;
  	}	   	   	
 
/**Mutator method for model field  	 
*@param mod model as String  	 
*/  	public void setModel(String mod) 
 	{  		model = mod;
  	}  	  	
 
/**Mutator method for year field  	 
*@param y year as int  	 
*/  	public void setYear(int y)
  	{ 
 		year = y;
  	}  	  	
 
/**Mutator method for price field  	 
*@param p price as String  	 
*/  	public void setPrice(String p) 
 	{
  		price = p;
  	}			  	  	
 
/**Accessor method for make field  	 
*@return make as String  	 
*/  	public String getMake() 
 	{
  		return make;
  	}  	  	
 
/**Accessor method for model field  	 
*@return model as String  	 
*/  	public String getModel() 
 	{
  		return model;
  	}  	  	
 
/**Accessor method for year field  	 
*@return year as int  	 
*/  	public int getYear() 
 	{
  		return year;
  	}  	  	
 
/**Accessor method for price field  	 
*@return price as String  	 
*/  	public String getPrice()
  	{
  		return price;
  	}  	  	
 
/**toString method for CarObject, intended for screen output  	 
*@return CarObject properties as String  	
 */  	public String toString()
  	{
  		return this.getMake()+ ", " + this.getModel() + ", made in "
  		  + this.getYear() + " costs " + this.getPrice();
      } 
}//end of class
public class CarObject  
{  	
 	private String make;  	  	
  	private String model;  	  	
  	private int year;   	   	
  	private String price;   
	   	
//Default constructor     
	public CarObject()   	
        { }   	   	

//parameterised constructor  	 
        public CarObject(String m, String mod, int y, String p)
   	{ 
  		make = m;  
	 	model = mod;  
	 	year = y;  
	 	price = p;  
   	}  

/**Mutator method for make field  	 
*@param m make as String  	 
*/  	public void setMake(String m)
  	{ 
 		make = m;
  	}	   	   	

/**Mutator method for model field  	 
*@param mod model as String  	 
*/  	public void setModel(String mod) 
 	{  		model = mod;
  	}  	  	

/**Mutator method for year field  	 
*@param y year as int  	 
*/  	public void setYear(int y)
  	{ 
 		year = y;
  	}  	  	

/**Mutator method for price field  	 
*@param p price as String  	 
*/  	public void setPrice(String p) 
 	{
  		price = p;
  	}			  	  	

/**Accessor method for make field  	 
*@return make as String  	 
*/  	public String getMake() 
 	{
  		return make;
  	}  	  	

/**Accessor method for model field  	 
*@return model as String  	 
*/  	public String getModel() 
 	{
  		return model;
  	}  	  	

/**Accessor method for year field  	 
*@return year as int  	 
*/  	public int getYear() 
 	{
  		return year;
  	}  	  	

/**Accessor method for price field  	 
*@return price as String  	 
*/  	public String getPrice()
  	{
  		return price;
  	}  	  	

/**toString method for CarObject, intended for screen output  	 
*@return CarObject properties as String  	
 */  	public String toString()
  	{
  		return this.getMake()+ ", " + this.getModel() + ", made in "
  		  + this.getYear() + " costs " + this.getPrice();
      } 
}//end of class

This is the main component of the program that calls mot of the other classes

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
  import javax.swing.*;
  import java.awt.event.*;
  import java.awt.*;
  import java.io.*;
  import java.sql.*;
 
    /**Car application class
   */
  public class CarApplication extends JFrame
  {
     	protected static CarArray ca = new CarArray();
  	public CarApplication() 
 	{
  		//Creates panel
  		super("Main Menu");
  		JPanel firstPanel = new JPanel();
 
 		//Allows the frame to be closed when the cross is clicked  		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 
  		//Adds buttons to it
 		JButton displayButton = new JButton("Display");
  		JButton addButton = new JButton("Add");
  		JButton deleteButton = new JButton("Delete");
  		JButton editButton = new JButton("Edit");
  		JButton sortButton = new JButton("Sort");
  		JButton searchButton = new JButton("Search");
  		JButton exitButton = new JButton("EXIT");
 
  		 //Add components to it.
  		getContentPane().add(firstPanel);
  		firstPanel.add(displayButton);
  		firstPanel.add(addButton);
  		firstPanel.add(deleteButton);
  		firstPanel.add(editButton);
  		firstPanel.add(sortButton);
  		firstPanel.add(searchButton);
  		firstPanel.add(exitButton);
 
  		//Displays the window.
  		setSize(200,200);
  		setLocationRelativeTo(null); //centers it
  		show();
 
  		 displayButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e)
  			{new DisplayFrame();} 
                 });
 
  		addButton.addActionListener(new ActionListener()  		
                 {
  			public void actionPerformed(ActionEvent e) 
 			{new AddFrame();	}
                 });
 
  		 deleteButton.addActionListener(new ActionListener()
  		{ 
 			public void actionPerformed(ActionEvent e)  
			{new DeleteFrame();}            
                  });  
 
		editButton.addActionListener(new ActionListener()
  		{ 
 			public void actionPerformed(ActionEvent e)
  			{new EditFrame1();} 
                  });
 
  		sortButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e)
  			{ new SortArray();}
  		});
 
  		 searchButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e)
  			{new SearchFrame();}
  		});  		
 
  		exitButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e) 
 			{ setVisible(false);
          		   dispose();
            		   try
          		   {
          			ca.arrayToFile();
          		   }
          		   catch(SQLException se)
          		   {
          			System.out.println("ERROR!!!! CAN'T WRITE TO DATABASE FILE");
          		   }
          		 }
  		});
  	}
 
 	public static void main(String [] args) throws IOException
  	{
  		try 
 		{
  			ca.fileToArray();
  		}
  		catch(SQLException se)
                {
          	       System.out.println("ERROR!!! CAN'T READ FROM DATABASE FILE");
         	}
  		new CarApplication();
  	}  
}
  import javax.swing.*;
  import java.awt.event.*;
  import java.awt.*;
  import java.io.*;
  import java.sql.*;

    /**Car application class
   */
  public class CarApplication extends JFrame
  {
     	protected static CarArray ca = new CarArray();
  	public CarApplication() 
 	{
  		//Creates panel
  		super("Main Menu");
  		JPanel firstPanel = new JPanel();

 		//Allows the frame to be closed when the cross is clicked  		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

  		//Adds buttons to it
 		JButton displayButton = new JButton("Display");
  		JButton addButton = new JButton("Add");
  		JButton deleteButton = new JButton("Delete");
  		JButton editButton = new JButton("Edit");
  		JButton sortButton = new JButton("Sort");
  		JButton searchButton = new JButton("Search");
  		JButton exitButton = new JButton("EXIT");

  		 //Add components to it.
  		getContentPane().add(firstPanel);
  		firstPanel.add(displayButton);
  		firstPanel.add(addButton);
  		firstPanel.add(deleteButton);
  		firstPanel.add(editButton);
  		firstPanel.add(sortButton);
  		firstPanel.add(searchButton);
  		firstPanel.add(exitButton);

  		//Displays the window.
  		setSize(200,200);
  		setLocationRelativeTo(null); //centers it
  		show();

  		 displayButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e)
  			{new DisplayFrame();} 
                 });

  		addButton.addActionListener(new ActionListener()  		
                 {
  			public void actionPerformed(ActionEvent e) 
 			{new AddFrame();	}
                 });

  		 deleteButton.addActionListener(new ActionListener()
  		{ 
 			public void actionPerformed(ActionEvent e)  
			{new DeleteFrame();}            
                  });  

		editButton.addActionListener(new ActionListener()
  		{ 
 			public void actionPerformed(ActionEvent e)
  			{new EditFrame1();} 
                  });

  		sortButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e)
  			{ new SortArray();}
  		});

  		 searchButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e)
  			{new SearchFrame();}
  		});  		

  		exitButton.addActionListener(new ActionListener()
  		{
  			public void actionPerformed(ActionEvent e) 
 			{ setVisible(false);
          		   dispose();
            		   try
          		   {
          			ca.arrayToFile();
          		   }
          		   catch(SQLException se)
          		   {
          			System.out.println("ERROR!!!! CAN'T WRITE TO DATABASE FILE");
          		   }
          		 }
  		});
  	}

 	public static void main(String [] args) throws IOException
  	{
  		try 
 		{
  			ca.fileToArray();
  		}
  		catch(SQLException se)
                {
          	       System.out.println("ERROR!!! CAN'T READ FROM DATABASE FILE");
         	}
  		new CarApplication();
  	}  
}

This is where the error occurs, in the in the FileToArray() and ArrayToFile() methods. :mad: :o

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
   import java.io.*;
   import java.sql.*;
 
     /**Creates a CarArray object<br>
   *CarArray object is an array of length 20 of CarObject objects
   */
   public class CarArray
   {
   	/**Array of length 20 of CarObject objects*/
   	private CarObject[] carArr = new CarObject[20];
 
   	/**Number of items used in array, ie number of CarObject objects in array
  	 */
  	private int size = 0;
 
  	/**Default constructor */
  	public CarArray()
  	{}
 
   	/**Loads JDBC-ODBC bridge driver*/
   	public static boolean loadODBC ()
  	{
     		 try 
    		 { 
        		  	Class.forName
           		  	("sun.jdbc.odbc.JdbcOdbcDriver");     			 System.out.println ("Bridge loaded");
         			  return true;
      		 }
      		catch (ClassNotFoundException e)
    		 {
          			  System.out.println ("Bridge not found");       			  return false;
     		 } // catch block
  	} // loadODBC method 
 
 	/**Reads contents of cars.mdb file containing properties of CarObject
  	 *objects and constructs CarObject objects in array using data as parameters.
   	 *<br>size field is updated to reflect number of CarObject objects in array.*/
  	public void fileToArray() throws SQLException
  	{
  		Connection conn;
   		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
  		Statement stmt = conn.createStatement();
     		String query = "SELECT Make, Model, Year, Price ";
  		ResultSet rs = stmt.executeQuery(query);
  		while (rs.next())
   		{
  		    String m = rs.getString("Make");
  		    String mod = rs.getString("Model");
  		    int y = rs.getInt("Year");
  		    String p = rs.getString("Price");
  		    carArr[size] = new CarObject(m, mod, y, p);
  		    size++;
  		}
   	}
 
   	/**Writes contents of array to cars.mdb database file.
   	 *The properties of each CarObject object in the array are recorded in the file */
  	public void arrayToFile()throws SQLException
  	{
  		Connection conn;
   		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
 
  		 for(int x = 0; x < size; x++)
  		{
  		  			Statement stmt = conn.createStatement();
  			stmt.executeUpdate(  			    	"INSERT INTO Cars " +       				"VALUES (carArr[x].getMake(), carArr[x].getModel(), carArr[x].getYear(), carArr[x].getPrice())");
      	         }
      	}  	 
 
 	/**Sends carArr to DisplayFrame which calls it so that the data can be displayed
  	 */
  	public CarObject[] display()
  	{
  		return carArr;
  	}
 
 	/**Allows user to input data for a new CarObject object to be added to the end
   	 *of the array.
  	 *<br>size field is incremented to account for new CarObject object added.
  	 *@throws IOException*/
	  public void addToArray(String m, String mod, String y, String p) throws IOException
  	{
  		carArr[size] = new CarObject(m, mod, Integer.parseInt(y), p);
  		size++;
  	}  	
 
  	/**Deletes one CarObject object from array.
  	 *Position of object to be deleted is determined by user input.
  	 *@throws IOException*/ 
 	public void deleteCar(int pos) throws IOException
  	{
  		if(pos<19)//prevents array out of bound error if last object in the list is selected
   		{
  			for(int x = pos; x < size; x++)
  			{
  				carArr[x] = carArr[x+1];
  			}
  		}
  		size--;
  	}  	 
 
  	 /**Edits the properties of a CarObject object in the array.  	  *@throws IOException
  	  */
  	public void editArray(String m, String mod, String y, String p, int edNum) throws IOException
  	{
  		String make
;  		String model
;  		int year;
  		String price;
 
  		if(!(m.equals(null)))
  		{
  			make = m;
  		}		
	  	else
  		{
  			make = carArr[edNum].getMake();
  		}  
 
		if(!(mod.equals(null)))
  		{
  			model = mod;
  		}			
  		else
  		{
  			model = carArr[edNum].getModel();
  		}  		  
 
		if(!(y.equals(null))) 
 		{
  			year = Integer.parseInt(y);
  		}
		else
  		{
  			year = carArr[edNum].getYear();
  		}  		  
 
		if(!(p.equals(null))) 
 		{
  			price = p;
  		}		
	  	else
  		{
  			price = carArr[edNum].getPrice();
  		}
 
  		carArr[edNum] = new CarObject(make, model, year, price);
  	}  	 
 
 	/**Sorts the records in the array ascending by price property
  	 */
  	public void sortPrice()
  	{
  		//uses bubble sort
  		for(int outt = size - 1; outt >= 0; outt--)
  		{
  			for(int inn = 0; inn < outt; inn++)
  			{
  				if(carArr[inn].getPrice().compareToIgnoreCase(carArr[inn + 1].getPrice()) > 0)
  				{
  					CarObject temp = new CarObject();
  					temp = carArr[inn];
  					carArr[inn] = carArr[inn + 1];
  					carArr[inn + 1] = temp;
  				}
  			}
  		}
  	}  	 
 
 	/**Searches for make input by user.
  	 *<br>calls FoundFrame if found or NotFoundFrame if not found,
  	 *@throws IOException
  	 */
  	public void searchArray(String searchText) throws IOException
  	{
  		//use binary search
  		boolean found = false;
  		int startPt = 0;
  		int endPt = size - 1;
  		int midPt = 0;
  		while(startPt <= endPt && !found)
  		{
  			midPt = (endPt + startPt) / 2;
  			if(carArr[midPt].getMake().equalsIgnoreCase(searchText))
  			//it has been found
  			{
  				found = true;
  			}
  			else if(carArr[midPt].getMake().compareToIgnoreCase(searchText) > 0)
  			//searchText may be in 1st half
  			{
  				endPt = midPt - 1;
  			}
  			else
  			//searchText may be in 2nd half
  			{
  				startPt = midPt + 1;
  			}
  		}
  		if(found)
  		{
  			new FoundFrame();
  		}
  		else
  		{
  			new NotFoundFrame();
  		}
  	}
}//end of class
   import java.io.*;
   import java.sql.*;

     /**Creates a CarArray object<br>
   *CarArray object is an array of length 20 of CarObject objects
   */
   public class CarArray
   {
   	/**Array of length 20 of CarObject objects*/
   	private CarObject[] carArr = new CarObject[20];

   	/**Number of items used in array, ie number of CarObject objects in array
  	 */
  	private int size = 0;

  	/**Default constructor */
  	public CarArray()
  	{}

   	/**Loads JDBC-ODBC bridge driver*/
   	public static boolean loadODBC ()
  	{
     		 try 
    		 { 
        		  	Class.forName
           		  	("sun.jdbc.odbc.JdbcOdbcDriver");     			 System.out.println ("Bridge loaded");
         			  return true;
      		 }
      		catch (ClassNotFoundException e)
    		 {
          			  System.out.println ("Bridge not found");       			  return false;
     		 } // catch block
  	} // loadODBC method 

 	/**Reads contents of cars.mdb file containing properties of CarObject
  	 *objects and constructs CarObject objects in array using data as parameters.
   	 *<br>size field is updated to reflect number of CarObject objects in array.*/
  	public void fileToArray() throws SQLException
  	{
  		Connection conn;
   		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
  		Statement stmt = conn.createStatement();
     		String query = "SELECT Make, Model, Year, Price ";
  		ResultSet rs = stmt.executeQuery(query);
  		while (rs.next())
   		{
  		    String m = rs.getString("Make");
  		    String mod = rs.getString("Model");
  		    int y = rs.getInt("Year");
  		    String p = rs.getString("Price");
  		    carArr[size] = new CarObject(m, mod, y, p);
  		    size++;
  		}
   	}

   	/**Writes contents of array to cars.mdb database file.
   	 *The properties of each CarObject object in the array are recorded in the file */
  	public void arrayToFile()throws SQLException
  	{
  		Connection conn;
   		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");

  		 for(int x = 0; x < size; x++)
  		{
  		  			Statement stmt = conn.createStatement();
  			stmt.executeUpdate(  			    	"INSERT INTO Cars " +       				"VALUES (carArr[x].getMake(), carArr[x].getModel(), carArr[x].getYear(), carArr[x].getPrice())");
      	         }
      	}  	 

 	/**Sends carArr to DisplayFrame which calls it so that the data can be displayed
  	 */
  	public CarObject[] display()
  	{
  		return carArr;
  	}
  	 
 	/**Allows user to input data for a new CarObject object to be added to the end
   	 *of the array.
  	 *<br>size field is incremented to account for new CarObject object added.
  	 *@throws IOException*/
	  public void addToArray(String m, String mod, String y, String p) throws IOException
  	{
  		carArr[size] = new CarObject(m, mod, Integer.parseInt(y), p);
  		size++;
  	}  	

  	/**Deletes one CarObject object from array.
  	 *Position of object to be deleted is determined by user input.
  	 *@throws IOException*/ 
 	public void deleteCar(int pos) throws IOException
  	{
  		if(pos<19)//prevents array out of bound error if last object in the list is selected
   		{
  			for(int x = pos; x < size; x++)
  			{
  				carArr[x] = carArr[x+1];
  			}
  		}
  		size--;
  	}  	 

  	 /**Edits the properties of a CarObject object in the array.  	  *@throws IOException
  	  */
  	public void editArray(String m, String mod, String y, String p, int edNum) throws IOException
  	{
  		String make
;  		String model
;  		int year;
  		String price;

  		if(!(m.equals(null)))
  		{
  			make = m;
  		}		
	  	else
  		{
  			make = carArr[edNum].getMake();
  		}  
		  
		if(!(mod.equals(null)))
  		{
  			model = mod;
  		}			
  		else
  		{
  			model = carArr[edNum].getModel();
  		}  		  

		if(!(y.equals(null))) 
 		{
  			year = Integer.parseInt(y);
  		}
		else
  		{
  			year = carArr[edNum].getYear();
  		}  		  

		if(!(p.equals(null))) 
 		{
  			price = p;
  		}		
	  	else
  		{
  			price = carArr[edNum].getPrice();
  		}

  		carArr[edNum] = new CarObject(make, model, year, price);
  	}  	 

 	/**Sorts the records in the array ascending by price property
  	 */
  	public void sortPrice()
  	{
  		//uses bubble sort
  		for(int outt = size - 1; outt >= 0; outt--)
  		{
  			for(int inn = 0; inn < outt; inn++)
  			{
  				if(carArr[inn].getPrice().compareToIgnoreCase(carArr[inn + 1].getPrice()) > 0)
  				{
  					CarObject temp = new CarObject();
  					temp = carArr[inn];
  					carArr[inn] = carArr[inn + 1];
  					carArr[inn + 1] = temp;
  				}
  			}
  		}
  	}  	 

 	/**Searches for make input by user.
  	 *<br>calls FoundFrame if found or NotFoundFrame if not found,
  	 *@throws IOException
  	 */
  	public void searchArray(String searchText) throws IOException
  	{
  		//use binary search
  		boolean found = false;
  		int startPt = 0;
  		int endPt = size - 1;
  		int midPt = 0;
  		while(startPt <= endPt && !found)
  		{
  			midPt = (endPt + startPt) / 2;
  			if(carArr[midPt].getMake().equalsIgnoreCase(searchText))
  			//it has been found
  			{
  				found = true;
  			}
  			else if(carArr[midPt].getMake().compareToIgnoreCase(searchText) > 0)
  			//searchText may be in 1st half
  			{
  				endPt = midPt - 1;
  			}
  			else
  			//searchText may be in 2nd half
  			{
  				startPt = midPt + 1;
  			}
  		}
  		if(found)
  		{
  			new FoundFrame();
  		}
  		else
  		{
  			new NotFoundFrame();
  		}
  	}
}//end of class

I have managed to find a fragment of code on writing to the array but I have not yet had time to test this:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
dbArray = rs.GetRows()
 
       for i = 0 to ubound(dbArray, 2)
    response.write dbArray(0, i) & ",&nbsp;
 "    response.write dbArray(1, i) & "<br>" 
  next
dbArray = rs.GetRows()

       for i = 0 to ubound(dbArray, 2)
    response.write dbArray(0, i) & ",&nbsp;
 "    response.write dbArray(1, i) & "<br>" 
  next

Would this work effectively?

Please note that these are not all my classes but the rest just have to do with displaying different windows depends on which button the user presses in window created in the CarApplication class and these all work so it would just be a waste of both your and my time by posting them.

Any help would be grately apprechiated, so please don't hesitate if you can think of anything

•

Join Date: Aug 2004

Posts: 153

Reputation: cosi is an unknown quantity at this point

Solved Threads: 1

cosi

Offline

Junior Poster

Re: help with database assignment

Aug 16th, 2004

Hi,

What is the error you receive when you run the program? Please be specific... maybe even copy and paste the error into this thread.

I've banged my head against the wall before with all sorts of dumb database errors.

By the way... your code is meticulously commented---a very good habit that will reap many benefits for you. Nice work.

Ed

•

Join Date: Aug 2004

Posts: 15

Reputation: axiss is an unknown quantity at this point

Solved Threads: 0

axiss

Offline

Newbie Poster

Re: help with database assignment

Aug 20th, 2004

Ok first of all I managed to get it to write to the array by changing the code in my fileToArray method to this:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
public void fileToArray() throws SQLException
	{
		loadODBC ();//calls method to load the driver
 
		Connection conn;
 		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
 
 		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Make, Model, Year, Price FROM cars");
 
		while (rs.next()) 
		{
		    String m = rs.getString(1);
       		    String mod = rs.getString(2);
       		    int y = rs.getInt(3);
                     String p = rs.getString(4);
		    carArr[size] = new CarObject(m, mod, y, p);
		    size++;
		}
 
		recordNum = size;//classwide variable storing the amount of record in the database 		
	}
public void fileToArray() throws SQLException
	{
		loadODBC ();//calls method to load the driver
		
		Connection conn;
 		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
		
 		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Make, Model, Year, Price FROM cars");
				
		while (rs.next()) 
		{
		    String m = rs.getString(1);
       		    String mod = rs.getString(2);
       		    int y = rs.getInt(3);
                     String p = rs.getString(4);
		    carArr[size] = new CarObject(m, mod, y, p);
		    size++;
		}
		
		recordNum = size;//classwide variable storing the amount of record in the database 		
	}

This works great so I'm really happy.

•

Originally Posted by cosi

What is the error you receive when you run the program? Please be specific... maybe even copy and paste the error into this thread.

Well there isn't doesn't actually give me an error, just my try catch block that tries to load my arrayToFile method printed the error that it can't write to the database file as specified.

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
try
{
          ca.arrayToFile();
}
catch(SQLException se)
{
          System.out.println("ERROR!!!! CAN'T WRITE TO DATABASE FILE");
}
try
{
          ca.arrayToFile();
}
catch(SQLException se)
{
          System.out.println("ERROR!!!! CAN'T WRITE TO DATABASE FILE");
}

So it's only the arrayToFile method that I need help with now.
I've changed it quite a bit and this is what it looks like now:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
public void arrayToFile()throws SQLException
	{
		loadODBC ();//calls method to load the driver
 
		Connection conn;
 		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
 
 
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Make, Model, Year, Price FROM cars");
 
		//the array holds the same amount of car data as Cars.mdb 
		if(size == recordNum)
		{
			for(int x = 0; x < size; x++)
			{
				//UPDATE
			}
		}
 
		//the array holds more car data then Cars.mdb 
		else if(size > recordNum)
		{
			for(int x = 0; x < size; x++)
			{
				if(x <= recordNum)
				{
					//UPDATE
				}
 
				else
				{
					//INSERT
				}
 
			}
		}
 
		//Cars.mdb holds more car data then the array
		else
		{
			for(int x = 0; x < recordNum; x++)
			{
					while(size < recordNum)
					{
						String delNum = String.valueOf(recordNum);
						stmt.executeUpdate("DELETE FROM cars WHERE ID = " + delNum);
						recordNum--;
					}
 
					//UPDATE
			}
		}
 
	}
public void arrayToFile()throws SQLException
	{
		loadODBC ();//calls method to load the driver
		
		Connection conn;
 		conn = DriverManager.getConnection ("jdbc:odbc:Cars", "", "");
		 		
 			
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Make, Model, Year, Price FROM cars");
				
		//the array holds the same amount of car data as Cars.mdb 
		if(size == recordNum)
		{
			for(int x = 0; x < size; x++)
			{
				//UPDATE
			}
		}
		
		//the array holds more car data then Cars.mdb 
		else if(size > recordNum)
		{
			for(int x = 0; x < size; x++)
			{
				if(x <= recordNum)
				{
					//UPDATE
				}
				
				else
				{
					//INSERT
				}
			
			}
		}
		
		//Cars.mdb holds more car data then the array
		else
		{
			for(int x = 0; x < recordNum; x++)
			{
					while(size < recordNum)
					{
						String delNum = String.valueOf(recordNum);
						stmt.executeUpdate("DELETE FROM cars WHERE ID = " + delNum);
						recordNum--;
					}
					
					//UPDATE
			}
		}
				
	}

Basically what I'm trying to do is compare the amount of record in the array with the amount in the database (stored in recordNum) so that basically is they are the same it updates all the records, if there are more records in the array the it updates and inserts records and if the array has less records then it deletes the records from the database till it equals the amount in the array and then updates the remaining records.

The delete part works fine, I'm just haveing trouble with the UPDATE AND INSERT statements since I've never worked with databases before and honestly have no idea how the use them. :-|

By the way, thanks for trying to help so far.

Me fail English?
Unpossible

•

Join Date: Aug 2004

Posts: 15

Reputation: axiss is an unknown quantity at this point

Solved Threads: 0

axiss

Offline

Newbie Poster

Re: help with database assignment

Aug 28th, 2004

By the way if it helps my database has the following columns in it in this order:
ID, make, model, year, price
(ID is just a sequencial numbers for every record)

I need to put my values from my array into my database by using my .getMake() .getModel() .getPrice() and .getYear() methods

so if anyone can think of the proper syntax to use to UPDATE and INSERT statements it would be a great help since I have really tried everything and really can't get it to work.

Me fail English?
Unpossible