I'm having difficulty of inserting a value into a column. When I enter a "ID" number and a value in the "last name" text field, I get an error saying that the "mi" text field can't be an zero length string. So, i enter a value in the "mi" text field and click insert button, I get a SQLException:General error. I'm not quite sure what the SQLException is referring too.
I also have a syntax error with my update statement. I have look at several update statements and still can't find the syntax error.
The view button(select statement) does work, so connecting to a the database isn't an issue.
Any help would be appreciated!
import java.awt.*;
import java.awt.event.*;
import java.applet.*;
import java.io.File;
import javax.swing.*;
import java.sql.*;
import javax.swing.border.*;
import java.util.Scanner;
public class Staff extends JApplet {
private PreparedStatement pstmt;
boolean isStandalone = false;
JPanel jpDisplay = new JPanel();
JLabel jlblStatus = new JLabel();
JPanel jpButtons = new JPanel();
BorderLayout borderLayout1 = new BorderLayout();
JPanel jpStaff = new JPanel();
JButton jbtView = new JButton();
JButton jbtInsert = new JButton();
JButton jbtUpdate = new JButton();
JButton jbtClear = new JButton();
JPanel jPanel1 = new JPanel();
JPanel jPanel2 = new JPanel();
JPanel jPanel3 = new JPanel();
JPanel jPanel4 = new JPanel();
JPanel jPanel5 = new JPanel();
JLabel jLabel2 = new JLabel();
JLabel jLabel3 = new JLabel();
JLabel jLabel4 = new JLabel();
FlowLayout flowLayout1 = new FlowLayout();
FlowLayout flowLayout2 = new FlowLayout();
FlowLayout flowLayout3 = new FlowLayout();
JTextField jtfID = new JTextField();
JTextField jtfLastName = new JTextField();
JLabel jLabel5 = new JLabel();
JTextField jtfFirstName = new JTextField();
JLabel jLabel6 = new JLabel();
JTextField jtfmi = new JTextField();
JTextField jtfAddress = new JTextField();
JLabel jLabel7 = new JLabel();
JTextField jtfCity = new JTextField();
JLabel jLabel8 = new JLabel();
JTextField jtfState = new JTextField();
FlowLayout flowLayout4 = new FlowLayout();
JLabel jLabel9 = new JLabel();
JTextField jtfTelephone = new JTextField();
FlowLayout flowLayout5 = new FlowLayout();
JLabel jLabel10 = new JLabel();
JTextField jtfEmail = new JTextField();
FlowLayout flowLayout6 = new FlowLayout();// The Statement for processing queries
Statement stmt;
TitledBorder titledBorder1;
GridLayout gridLayout1 = new GridLayout();
/**Initialize the applet*/
public void init() {
try {
jbInit();
} catch (Exception e) {
e.printStackTrace();
}
}
private void jbInit() throws Exception {
titledBorder1 = new TitledBorder("");
this.setSize(450, 350);
jpDisplay.setLayout(borderLayout1);
jpStaff.setLayout(gridLayout1);
jbtView.setText("View");
jbtView.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jbtView_actionPerformed(e);
}
});
jbtInsert.setText("Insert");
jbtInsert.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jbtInsert_actionPerformed(e);
}
});
jbtUpdate.setText("Update");
jbtUpdate.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jbtUpdate_actionPerformed(e);
}
});
jbtClear.setText("Clear");
jbtClear.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jbtClear_actionPerformed(e);
}
});
jPanel5.setLayout(flowLayout5);
jPanel4.setLayout(flowLayout4);
jPanel3.setLayout(flowLayout3);
jPanel2.setLayout(flowLayout2);
jPanel1.setLayout(flowLayout1);
jLabel2.setText("ID");
jLabel3.setText("Last Name");
jLabel4.setText("Address");
flowLayout1.setAlignment(0);
flowLayout2.setAlignment(0);
flowLayout3.setAlignment(0);
jtfID.setColumns(11);
jtfID.setBackground(Color.yellow);
jtfLastName.setColumns(10);
jLabel5.setText("First Name");
jtfFirstName.setColumns(10);
jLabel6.setText("mi");
jtfmi.setColumns(2);
jtfAddress.setColumns(15);
jLabel7.setText("City");
jtfCity.setColumns(15);
jLabel8.setText("State");
jtfState.setColumns(2);
flowLayout4.setAlignment(0);
jLabel9.setText("Telephone");
jtfTelephone.setColumns(12);
flowLayout5.setAlignment(0);
jLabel10.setText("Email");
jtfEmail.setColumns(20);
flowLayout6.setAlignment(0);
jlblStatus.setBackground(Color.pink);
jlblStatus.setText("Connecting ...");
jpStaff.setBorder(titledBorder1);
titledBorder1.setTitle("Staff Information");
gridLayout1.setColumns(1);
gridLayout1.setRows(5);
this.getContentPane().add(jpDisplay, BorderLayout.CENTER);
jpDisplay.add(jpButtons, BorderLayout.SOUTH);
jpButtons.add(jbtView, null);
jpButtons.add(jbtInsert, null);
jpButtons.add(jbtUpdate, null);
jpButtons.add(jbtClear, null);
jpDisplay.add(jpStaff, BorderLayout.CENTER);
jpStaff.add(jPanel1, null);
jPanel1.add(jLabel2, null);
jPanel1.add(jtfID, null);
jpStaff.add(jPanel2, null);
jPanel2.add(jLabel3, null);
jPanel2.add(jtfLastName, null);
jPanel2.add(jLabel5, null);
jPanel2.add(jtfFirstName, null);
jPanel2.add(jLabel6, null);
jPanel2.add(jtfmi, null);
jpStaff.add(jPanel3, null);
jPanel3.add(jLabel4, null);
jPanel3.add(jtfAddress, null);
jpStaff.add(jPanel4, null);
jPanel4.add(jLabel7, null);
jPanel4.add(jtfCity, null);
jPanel4.add(jLabel8, null);
jPanel4.add(jtfState, null);
jpStaff.add(jPanel5, null);
jPanel5.add(jLabel9, null);
jPanel5.add(jtfTelephone, null);
jPanel5.add(jLabel10, null);
jPanel5.add(jtfEmail, null);
this.getContentPane().add(jlblStatus, BorderLayout.SOUTH);
// Connect to the database
initializeDB();
}
private void initializeDB() {
try {
// Load the driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //for Access
// Connect to the ODBC - Access database
Connection connection = DriverManager.getConnection( //establish a connection
"jdbc:odbc:Staff"); //i created teh database test.mbd for Access
System.out.println("Database connected");
// Create a statement
stmt = connection.createStatement();
} catch (Exception ex) {
jlblStatus.setText("Connection failed: " + ex);
}
}
void jbtInsert_actionPerformed(ActionEvent e) {
insert();
}
void jbtView_actionPerformed(ActionEvent e) {
view();
}
void jbtUpdate_actionPerformed(ActionEvent e) {
update();
}
void jbtClear_actionPerformed(ActionEvent e) {
clear();
}
/**View record by ID*/
private void view() {
// Build a SQL SELECT statement
//added 2/23
//don't have a where clause, do i need a where clause? I don't think that i do
String sqlSelect = "SELECT id, lastName, firstName, mi, address,"
+ "city, state, telephone, email " + "FROM Staff";
try {
ResultSet rset = stmt.executeQuery(sqlSelect);
loadToTextField(rset);
} catch (SQLException ex) {
jlblStatus.setText("Select failed: " + ex);
}
}
/**Load the record into text fields*/
private void loadToTextField(ResultSet rs) throws SQLException {
if (rs.next()) {
//added 2/23
jtfID.setText(rs.getString(1));
jtfLastName.setText(rs.getString(2));
jtfFirstName.setText(rs.getString(3));
jtfmi.setText(rs.getString(4));
jtfAddress.setText(rs.getString(5));
jtfCity.setText(rs.getString(6));
jtfState.setText(rs.getString(7));
jtfTelephone.setText(rs.getString(8));
jtfEmail.setText(rs.getString(9));
jlblStatus.setText("Record found");
} else {
jlblStatus.setText("Record not found");
}
}
/**Insert a new record*/
private void insert() {
// Build a SQL INSERT statement
String sqlInsert = "INSERT INTO Staff (id,lastName,firstName, mi, address,"
+ " city,state, telephone, email) VALUES ( '" + jtfID.getText().trim()
+ "', '" + jtfLastName.getText().trim() + "', ' " + jtfFirstName.getText().trim()
+ "', '" + jtfmi.getText().trim() + "', '" + jtfAddress.getText().trim()
+ "', '" + jtfCity.getText().trim() + "', '" + jtfState.getText().trim()
+ "',' " + jtfTelephone.getText().trim() + "', '" + jtfEmail.getText().trim() + "')";
try {
stmt.executeUpdate(sqlInsert);
jlblStatus.setText("record inserted");
} catch (SQLException ex) {
jlblStatus.setText("Insertion failed: " + ex);
}
}
/**Update a record*/
private void update() {
String sqlUpdate = "UPDATE Staff (id,lastName,firstName, mi, address,"
+ " city,state,telephone, email) SET ( '" + jtfID.getText().trim()
+ "','" + jtfLastName.getText().trim() + "','" + jtfFirstName.getText().trim()
+ "', '" + jtfID.getText().trim() + "', '" + jtfAddress.getText().trim()
+ "', '" + jtfCity.getText().trim() + "', '" + jtfTelephone.getText().trim()
+ "', '" + jtfEmail.getText().trim() + "')";
try {
//added 2/26
stmt.executeUpdate(sqlUpdate);
jlblStatus.setText("Record updated");
} catch (SQLException ex) {
jlblStatus.setText("Update failed: " + ex);
}
}
/**Clear text fields*/
private void clear() {
jtfID.setText(null);
jtfLastName.setText(null);
jtfFirstName.setText(null);
jtfmi.setText(null);
jtfAddress.setText(null);
jtfCity.setText(null);
jtfState.setText(null);
jtfTelephone.setText(null);
jtfEmail.setText(null);
}
/**Get Applet information*/
public String getAppletInfo() {
return "Applet Information";
}
/**Get parameter info*/
public String[][] getParameterInfo() {
return null;
}
/**Main method*/
public static void main(String[] args) {
Staff applet = new Staff();
applet.isStandalone = true;
JFrame frame = new JFrame();
//EXIT_ON_CLOSE == 3
frame.setDefaultCloseOperation(3);
frame.setTitle("Accessing Database Staff");
frame.getContentPane().add(applet, BorderLayout.CENTER);
applet.init();
applet.start();
frame.setSize(1000, 500);
Dimension d = Toolkit.getDefaultToolkit().getScreenSize();
frame.setLocation((d.width - frame.getSize().width) / 2, (d.height - frame.getSize().height) / 2);
frame.setVisible(true);
}
}
