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

Edited 5 Years Ago by GooeyG: n/a

Attachments database.jpg 74.28 KB general_error.jpg 45.26 KB no_mi_value.jpg 49.42 KB

This sort of thing

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() + "')";

is your biggest problem.
Use PreparedStatement unless you want sporadic SQL syntax exceptions or SQL Injection attacks. (or learn how to properly escape anything that might be entered into those textfields yourself and do that, and it's not really worth the effort since the JDBC driver developers have already done it)

Edited 5 Years Ago by masijade: n/a

and add

} catch (SQLException ex) {
   jlblStatus.setText("Update failed: " + ex);
}finally{
   stme.close
}

What's your database table schema?

I'm not quite sure what you mean by what's your database table schema. Yes, i did Google it and Wikipedia defines it as "Database schema refers to the organization of data, to create a blueprint, of how a database will be constructed(divided into database tables)."

I did make some screen shot of the database(Access) that I'm using, is that what your referring too?

database table schema

not he's meaning table Columns properties defined in MsAccess,

but your problem is only with one column:

UPDATE Staff (id == jtfID.getText().trim()

1/ at 90% you are set autoIncrement in MsAccess for this column, remove this statement, MsAccess generated this ID by itself

2/ least 10% you allows and put non Integer value to MsAccess

note be sure that check if some table columns doesn't defined input mask, then you hold this column format from Java too,

Edited 5 Years Ago by mKorbel: n/a

This sort of thing

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() + "')";

is your biggest problem.
Use PreparedStatement unless you want sporadic SQL syntax exceptions or SQL Injection attacks. (or learn how to properly escape anything that might be entered into those textfields yourself and do that, and it's not really worth the effort since the JDBC driver developers have already done it)

Thanks for the suggestion and here is my updated version:

/**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
        {
            //prepareState = connection.prepareStatement(sqlInsert);
            prepareState.executeUpdate(sqlInsert);    //PreparedStatement 

            jlblStatus.setText("record inserted");
            prepareState.close();
        } catch (SQLException ex)
        {
            jlblStatus.setText("Insertion failed: " + ex);
        }
    }

Now I'm dealing with an NullPointerExeception. It's complaining about the "prepareState.executeUpdate(sqlInsert);"

I did try writing it like this:
prepareState = connection.prepareStatement(sqlInsert);
prepareState.executeUpdate();

I'm still getting a NULLPointerException. I'm not quite sure what to do.

I wrote the PrepareStatement like this: "PreparedStatement prepareState;" and I declared it outside of the function.

Edited 5 Years Ago by GooeyG: n/a

Attachments nullPointer.jpg 95.19 KB

and add

} catch (SQLException ex) {
   jlblStatus.setText("Update failed: " + ex);
}finally{
   stme.close
}

Thanks for the suggestion.

You commented out the statement that actually built the prepared statement

//prepareState = connection.prepareStatement(sqlInsert);

Thanks for the suggestion and here is my updated version:

And what I meant, of course, was the entire String concatenation that you are still doing. Simply changing to PreparedStatement and using it as a normal Statement (which is possible) defeats the purpose, of course.

Edited 5 Years Ago by masijade: typo

This article has been dead for over six months. Start a new discussion instead.