Thread: Inventory application with Read, Delete, Insert and Update Methods

Can anyone help me with the read, insert, delete and update methods in my code? I believe the delete method is working OK but I get SQL errors when I use any of the other methods. I'm a beginner in programming and really can't figure this out. This is an applet that uses buttons named read, insert, delete and update. They are reading from a database that contains fields called partNumber(primary Key), name, description and price. I appreciate any help. Thanks!

private void read_actionPerformed(ActionEvent e) {

String partNumber = txtPartNumber.getText();

try{
String queryString = "select partNumber, name, description, price from Inventory" +
" where Inventory.partNumber = " + partNumber ;

ResultSet rset = stmt.executeQuery(queryString);

if (rset.next()){
String partNumber1 = rset.getString(1);
String name = rset.getString(2);
String description = rset.getString(3);
String price = rset.getString(4);

txtName.setText(name);
txtDescription.setText(description);
txtPrice.setText(price);


} else { JOptionPane.showMessageDialog(null, "Inventory item not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}

}

private void update_actionPerformed(ActionEvent e) {

String partNumber = txtPartNumber.getText();
String name = txtName.getText();
String description = txtDescription.getText();
String price = txtPrice.getText();

txtName.setText("");
txtDescription.setText("");
txtPrice.setText("");
txtPartNumber.setText(partNumber);

try{

String updateString = "update Inventory "+

" name= '"+ name +"',"+

" description= '"+description+"',"+ "price= "+price+

" where Inventory.partNumber = " + partNumber;

ResultSet rset = stmt.executeQuery(toString());

//Results

if(rset.next()) {

int resultInsert = stmt.executeUpdate(updateString);
}
else {
JOptionPane.showMessageDialog(null,"Update NOT executed, inventory item not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
}

private void delete_actionPerformed(ActionEvent e) {

String partNumber = txtPartNumber.getText();

try {
String deleteString = "delete from Inventory where partNumber= "+partNumber;

//Results
int result = stmt.executeUpdate(deleteString);

}
catch (SQLException ex) {
ex.printStackTrace();

}
}

private void insert_actionPerformed(ActionEvent e) {

try{
String partNumber = txtPartNumber.getText();
String name = txtName.getText();
String description = txtDescription.getText();
String price = txtPrice.getText();

String insertString = "insert into Inventory "+

"set partNumber = '"+ partNumber +"', name = '"+ name +"',"+

" description= '"+description+"',"+ "price= "+price;


System.out.println(insertString);
txtName.setText(name);
txtDescription.setText(description);
txtPrice.setText(price);
txtPartNumber.setText(partNumber);

int result = stmt.executeUpdate(toString());


ResultSet rset = stmt.executeQuery
("select * from inventory");

if (rset.next()) {
String partName = rset.getString(1);
String partDescription = rset.getString(2);
String partNumber1 = rset.getString(3);
String partPrice = rset.getString(5);

txtName.setText(name);
txtDescription.setText(description);
txtPrice.setText(price);
txtPartNumber.setText(partNumber);

// Display result in a dialog box
JOptionPane.showMessageDialog(null, "Part#: " + partNumber + " will be inserted with == > " + partDescription +
" " + partNumber + partPrice);
} else {
// Display result in a dialog box
JOptionPane.showMessageDialog(null, "Update NOT executed, inventory item not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
}

First repost your code in the [code=java][/code ] tags to maintain formatting and highlighting. Also, post the errors your are getting.

How do I do that?

private void read_actionPerformed(ActionEvent e) {

String partNumber = txtPartNumber.getText();

try{
String queryString = "select partNumber, name, description, price from Inventory" +
" where Inventory.partNumber = " + partNumber ;

ResultSet rset = stmt.executeQuery(queryString);

if (rset.next()){
String partNumber1 = rset.getString(1);
String name = rset.getString(2);
String description = rset.getString(3);
String price = rset.getString(4);

txtName.setText(name);
txtDescription.setText(description);
txtPrice.setText(price);


} else { JOptionPane.showMessageDialog(null, "Inventory item not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}

}

private void update_actionPerformed(ActionEvent e) {

String partNumber = txtPartNumber.getText();
String name = txtName.getText();
String description = txtDescription.getText();
String price = txtPrice.getText();

txtName.setText("");
txtDescription.setText("");
txtPrice.setText("");
txtPartNumber.setText(partNumber);

try{

String updateString = "update Inventory "+

" name= '"+ name +"',"+

" description= '"+description+"',"+ "price= "+price+

" where Inventory.partNumber = " + partNumber;

ResultSet rset = stmt.executeQuery(toString());

//Results

if(rset.next()) {

int resultInsert = stmt.executeUpdate(updateString);
}
else {
JOptionPane.showMessageDialog(null,"Update NOT executed, inventory item not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
}

private void delete_actionPerformed(ActionEvent e) {

String partNumber = txtPartNumber.getText();

try {
String deleteString = "delete from Inventory where partNumber= "+partNumber;

//Results
int result = stmt.executeUpdate(deleteString);

}
catch (SQLException ex) {
ex.printStackTrace();

}
}

private void insert_actionPerformed(ActionEvent e) {

try{
String partNumber = txtPartNumber.getText();
String name = txtName.getText();
String description = txtDescription.getText();
String price = txtPrice.getText();

String insertString = "insert into Inventory "+

"set partNumber = '"+ partNumber +"', name = '"+ name +"',"+

" description= '"+description+"',"+ "price= "+price;


System.out.println(insertString);
txtName.setText(name);
txtDescription.setText(description);
txtPrice.setText(price);
txtPartNumber.setText(partNumber);

int result = stmt.executeUpdate(toString());


ResultSet rset = stmt.executeQuery
("select * from inventory");

if (rset.next()) {
String partName = rset.getString(1);
String partDescription = rset.getString(2);
String partNumber1 = rset.getString(3);
String partPrice = rset.getString(5);

txtName.setText(name);
txtDescription.setText(description);
txtPrice.setText(price);
txtPartNumber.setText(partNumber);

// Display result in a dialog box
JOptionPane.showMessageDialog(null, "Part#: " + partNumber + " will be inserted with == > " + partDescription +
" " + partNumber + partPrice);
} else {
// Display result in a dialog box
JOptionPane.showMessageDialog(null, "Update NOT executed, inventory item not found");
}
}
catch (SQLException ex) {
ex.printStackTrace();
}
}

--- Update ---

This is one of the errors I get when I try to update the price from item partNumber 22222. Thats the only item I can read to try to update and it. When I click update it does not update and when I read it after clicking the update button I get this error:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Inventory[panel0,0,0,1024x663,invalid,layout=java.awt.Border Layout,rootPane=java' at line 1

--- Update ---

I also get this error when I try to insert a new entry with part number 66666, name:test, description:testagain, price:201

com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Inventory[panel0,0,0,1024x663,invalid,layout=java.awt.Border Layout,rootPane=java' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:15 73)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java: 3170)
at com.mysql.jdbc.Statement.executeUpdate(Statement.j ava:1316)
at com.mysql.jdbc.Statement.executeUpdate(Statement.j ava:1235)
at Inventory.insert_actionPerformed(Inventory.java:20 7)
at Inventory.access$3(Inventory.java:186)
at Inventory$4.actionPerformed(Inventory.java:54)
at javax.swing.AbstractButton.fireActionPerformed(Unk nown 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.mouseRe leased(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(U nknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unkno wn Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPri vilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPri vilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPri vilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilter s(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(U nknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarch y(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Your code is still near impossible to read since you've used quote tags, not code tags. Please re-read the forum FAQ and try to get your posted code formatted well. It's extremely hard to help if we can't read the code.

Is this the correct way?I am sorry. I never used this website before...I attached the .java code in a zip file if that helps...

private void read_actionPerformed(ActionEvent e) {
 
	  String partNumber = txtPartNumber.getText();
 
      try{
      String queryString = "select partNumber, name, description, price from Inventory" +
      " where Inventory.partNumber = " + partNumber ;
 
	  ResultSet rset = stmt.executeQuery(queryString);
 
	  if (rset.next()){
		  String partNumber1 = rset.getString(1);
		  String name = rset.getString(2);
		  String description = rset.getString(3);
		  String price = rset.getString(4);
 
		  txtName.setText(name);
		  txtDescription.setText(description);
		  txtPrice.setText(price);
 
 
	  } else { JOptionPane.showMessageDialog(null, "Inventory item not found");
	  }
	}
  catch (SQLException ex) {
	  ex.printStackTrace();
  }
 
  }
 
  private void update_actionPerformed(ActionEvent e) {
 
	  String partNumber = txtPartNumber.getText();
	  String name = txtName.getText();
	  String description = txtDescription.getText();
	  String price = txtPrice.getText();
 
	  txtName.setText("");
	  txtDescription.setText("");
	  txtPrice.setText("");
	  txtPartNumber.setText(partNumber);
 
	  try{
 
	 String updateString = "update Inventory "+
 
	  " name= '"+ name +"',"+
 
	        " description= '"+description+"',"+ "price= "+price+
 
	        " where Inventory.partNumber = " + partNumber;
 
	 ResultSet rset = stmt.executeQuery(toString());
 
	 //Results
 
	 if(rset.next()) {
 
	  int resultInsert = stmt.executeUpdate(updateString);
	 } 
	   else {
	      JOptionPane.showMessageDialog(null,"Update NOT executed, inventory item not found");
	  }
	     }
	   catch (SQLException ex) {
	     ex.printStackTrace();
	   }
	 }
 
  private void delete_actionPerformed(ActionEvent e) {
 
	    String partNumber = txtPartNumber.getText();
 
	    try {
	        String deleteString = "delete from Inventory where partNumber= "+partNumber;
 
    		//Results
    		int result = stmt.executeUpdate(deleteString);  
 
	    }  
	    catch (SQLException ex) {
            ex.printStackTrace();
 
          }
  }
 
  private void insert_actionPerformed(ActionEvent e) {
 
	  try{
		  String partNumber = txtPartNumber.getText();
		  String name = txtName.getText();
		  String description = txtDescription.getText();
		  String price = txtPrice.getText();
 
		  String insertString = "insert into Inventory "+
 
		  "set partNumber = '"+ partNumber +"', name = '"+ name +"',"+
 
		        " description= '"+description+"',"+ "price= "+price;
 
 
		 System.out.println(insertString);
		 txtName.setText(name);
		 txtDescription.setText(description);
		 txtPrice.setText(price);
		 txtPartNumber.setText(partNumber);
 
	     int result = stmt.executeUpdate(toString());
 
 
    ResultSet rset = stmt.executeQuery
    ("select * from inventory"); 
 
      if (rset.next()) {
        String partName = rset.getString(1);
        String partDescription = rset.getString(2);
        String partNumber1 = rset.getString(3);
        String partPrice = rset.getString(5);
 
        txtName.setText(name);
		  txtDescription.setText(description);
		  txtPrice.setText(price);
		  txtPartNumber.setText(partNumber);
 
        // Display result in a dialog box
        JOptionPane.showMessageDialog(null, "Part#: " + partNumber + " will be inserted with == > " + partDescription +
          " " + partNumber + partPrice);
      } else {
        // Display result in a dialog box
        JOptionPane.showMessageDialog(null, "Update NOT executed, inventory item not found");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

Can anyone help me with this code?

Hello. Can anyone help me with this code? Thanks!

private void read_actionPerformed(ActionEvent e) {
 
	  String partNumber = txtPartNumber.getText();
 
      try{
      String queryString = "select partNumber, name, description, price from Inventory" +
      " where Inventory.partNumber = " + partNumber ;
 
	  ResultSet rset = stmt.executeQuery(queryString);
 
	  if (rset.next()){
		  String partNumber1 = rset.getString(1);
		  String name = rset.getString(2);
		  String description = rset.getString(3);
		  String price = rset.getString(4);
 
		  txtName.setText(name);
		  txtDescription.setText(description);
		  txtPrice.setText(price);
 
 
	  } else { JOptionPane.showMessageDialog(null, "Inventory item not found");
	  }
	}
  catch (SQLException ex) {
	  ex.printStackTrace();
  }
 
  }
 
  private void update_actionPerformed(ActionEvent e) {
 
	  String partNumber = txtPartNumber.getText();
	  String name = txtName.getText();
	  String description = txtDescription.getText();
	  String price = txtPrice.getText();
 
	  txtName.setText("");
	  txtDescription.setText("");
	  txtPrice.setText("");
	  txtPartNumber.setText(partNumber);
 
	  try{
 
	 String updateString = "update Inventory "+
 
	  " name= '"+ name +"',"+
 
	        " description= '"+description+"',"+ "price= "+price+
 
	        " where Inventory.partNumber = " + partNumber;
 
	 ResultSet rset = stmt.executeQuery(toString());
 
	 //Results
 
	 if(rset.next()) {
 
	  int resultInsert = stmt.executeUpdate(updateString);
	 } 
	   else {
	      JOptionPane.showMessageDialog(null,"Update NOT executed, inventory item not found");
	  }
	     }
	   catch (SQLException ex) {
	     ex.printStackTrace();
	   }
	 }
 
  private void delete_actionPerformed(ActionEvent e) {
 
	    String partNumber = txtPartNumber.getText();
 
	    try {
	        String deleteString = "delete from Inventory where partNumber= "+partNumber;
 
    		//Results
    		int result = stmt.executeUpdate(deleteString);  
 
	    }  
	    catch (SQLException ex) {
            ex.printStackTrace();
 
          }
  }
 
  private void insert_actionPerformed(ActionEvent e) {
 
	  try{
		  String partNumber = txtPartNumber.getText();
		  String name = txtName.getText();
		  String description = txtDescription.getText();
		  String price = txtPrice.getText();
 
		  String insertString = "insert into Inventory "+
 
		  "set partNumber = '"+ partNumber +"', name = '"+ name +"',"+
 
		        " description= '"+description+"',"+ "price= "+price;
 
 
		 System.out.println(insertString);
		 txtName.setText(name);
		 txtDescription.setText(description);
		 txtPrice.setText(price);
		 txtPartNumber.setText(partNumber);
 
	     int result = stmt.executeUpdate(toString());
 
 
    ResultSet rset = stmt.executeQuery
    ("select * from inventory"); 
 
      if (rset.next()) {
        String partName = rset.getString(1);
        String partDescription = rset.getString(2);
        String partNumber1 = rset.getString(3);
        String partPrice = rset.getString(5);
 
        txtName.setText(name);
		  txtDescription.setText(description);
		  txtPrice.setText(price);
		  txtPartNumber.setText(partNumber);
 
        // Display result in a dialog box
        JOptionPane.showMessageDialog(null, "Part#: " + partNumber + " will be inserted with == > " + partDescription +
          " " + partNumber + partPrice);
      } else {
        // Display result in a dialog box
        JOptionPane.showMessageDialog(null, "Update NOT executed, inventory item not found");
      }
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

[/QUOTE]