SQL Error using JDBC

Question

I'm trying to get a form to write information to a MySQL DB. The servlet is connecting to the confirmation jsp but the database is not recording the information. I'm getting the following error message:

SEVERE: java.sql.SQLException: No value specified for parameter 11
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2560)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2536)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2383)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
at eBooks.data.UserDB.update(UserDB.java:100)
at eBooks.controller.RegisterUserServlet.doPost(RegisterUserServlet.java:65)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1539)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)
at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:330)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:174)
at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:828)
at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:725)
at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1019)
at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)
at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
    at java.lang.Thread.run(Thread.java:722)

SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
SEVERE:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
SEVERE:     at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2560)
SEVERE:     at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2536)
SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2383)
SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2327)
SEVERE:     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2312)
SEVERE:     at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
SEVERE:     at eBooks.data.UserDB.update(UserDB.java:100)
SEVERE:     at eBooks.controller.RegisterUserServlet.doPost(RegisterUserServlet.java:65)
SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
SEVERE:     at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

This is my code for my UserDB class:

package eBooks.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import eBooks.business.User;
import eBooks.util.DBUtil;

/**
 * @author L10
 */

public class UserDB {

    public static int insert(User user)
    {

        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;

         String query = 
                "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
                + " address, city,  state,  country,  zipcode, accountType)"
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

         try
         {
             ps = connection.prepareStatement(query);
             ps.setString(1, user.getfName());
             ps.setString(2, user.getlName());
             ps.setString(3, user.getEmailAddress());
             ps.setString(4, user.getPassword());
             ps.setString(5, user.getDateOfBirth());
             ps.setString(5, user.getPhone());
             ps.setString(6, user.getAddress());
             ps.setString(7, user.getCity());
             ps.setString(8, user.getCountry());
             ps.setString(9, user.getState());
             ps.setString(10, user.getZipcode());
             //ps.setString(11, user.getAccountType()); 

             return ps.executeUpdate();

         }
         catch(SQLException e)
         {
             e.printStackTrace();
             return 0;
         }
         finally
         {
             DBUtil.closePreparedStatement(ps);
             pool.freeConnection(connection);
         }

    }


    public static int update(User user)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;

        String query = "UPDATE User SET" 
                        + "fName = ?"
                        + "lName = ?"
                        + "password = ?"
                        + "dateOfBirth = ?"
                        + "phone = ?"
                        + "address = ?"
                        + "city = ?"
                        + "state_or_Region = ?"
                        + "country = ?"
                        + "zip = ?"
                        + ""
                        + "WHERE email_address= ?";   

        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, user.getfName());
            ps.setString(2, user.getlName());
            ps.setString(3, user.getEmailAddress());
            ps.setString(4, user.getPassword());
            ps.setString(5, user.getDateOfBirth());
            ps.setString(5, user.getPhone());
            ps.setString(6, user.getAddress());
            ps.setString(7, user.getCity());
            ps.setString(8, user.getCountry());
            ps.setString(9, user.getState());
            ps.setString(10, user.getZipcode());
             //ps.setString(11, user.getAccountType()); 

            return ps.executeUpdate();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return 0;
        }
        finally
        {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

    }


    public static int delete(User user)
    {
       ConnectionPool pool = ConnectionPool.getInstance();
       Connection connection = pool.getConnection();
       PreparedStatement ps = null;

       String query = "DELETE FROM User" +
                      "WHERE email_address = ?";
       try
       {
           ps = connection.prepareStatement(query);
           ps.setString(1, user.getEmailAddress());

           return ps.executeUpdate();

       }
       catch(SQLException e)
       {
           e.printStackTrace();
           return 0;
       }
       finally
       {
           DBUtil.closePreparedStatement(ps);
           pool.freeConnection(connection);
       }
    }



    public static boolean emailExists(String emailAddress)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT email_address FROM User"+
                       "WHERE email_address = ?";


        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();
            return rs.next();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return false;            
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }

    public static User selectUser(String emailAddress)
    {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM  User"+
                       "WHERE email_address = ?";

        try
        {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();

            User user = null;
            if (rs.next())
            {
                user = new User();
                user.setfName(rs.getString("fName"));
                user.setlName(rs.getString("lName"));
                user.setEmailAddress(rs.getString("emailAddress"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setDateOfBirth(rs.getString("dateOfBirth"));
                user.setAddress(rs.getString("address"));
                user.setCity(rs.getString("city"));
                user.setCountry(rs.getString("country"));
                user.setState(rs.getString("state"));
                user.setZipcode(rs.getString("zip"));
                // user.setAccountType(rs.getString("accountType")); -- Ask Jassin
            }
            return user;            
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            return null;
        }
        finally
        {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

    }

}

I need a push in the right direction, would appreciate any suggestions.

Your values statement contains 11 "?" but it appears you only set 10 parameters in your UserDB class. — pd40
– pd40, Commented May 21, 2012 at 1:32

Pau Kiat Wee · Accepted Answer · 2012-05-21 01:38:23Z

Based on query

String query = 
            "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
            + " address, city,  state,  country,  zipcode, accountType)"
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

You should use following query instead, whereby the parameters are actually 12, not 11.

     String query = 
            "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
            + " address, city,  state,  country,  zipcode, accountType)"
            + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

While for the set parameter,

         ps = connection.prepareStatement(query);
         ps.setString(1, user.getfName());
         ps.setString(2, user.getlName());
         ps.setString(3, user.getEmailAddress());
         ps.setString(4, user.getPassword());
         // ps.setString(5, user.getDateOfBirth());
         // ps.setString(5, user.getPhone());
         ps.setString(5, user.getDateOfBirth());
         ps.setString(6, user.getPhone());
         ps.setString(7, user.getAddress());
         ps.setString(8, user.getCity());
         ps.setString(9, user.getState());
         ps.setString(10, user.getCountry());
         ps.setString(11, user.getZipcode());
         ps.setString(12, user.getAccountType());

For the update query

    String query = "UPDATE User SET" 
                    + "fName = ?"
                    + "lName = ?"
                    + "password = ?"
                    + "dateOfBirth = ?"
                    + "phone = ?"
                    + "address = ?"
                    + "city = ?"
                    + "state_or_Region = ?"
                    + "country = ?"
                    + "zip = ?"
                    + ""
                    + "WHERE email_address= ?";

You missing a parameter as well

         ps.setString(1, user.getfName());
         ps.setString(2, user.getlName());
         ps.setString(3, user.getPassword());
         ps.setString(4, user.getDateOfBirth());
         ps.setString(5, user.getPhone());
         ps.setString(6, user.getAddress());
         ps.setString(7, user.getCity());
         ps.setString(8, user.getState());
         ps.setString(9, user.getCountry());
         ps.setString(10, user.getZipcode());
         ps.setString(11, user.getEmailAddress());

Tu Tran · Accepted Answer · 2012-05-21 01:33:34Z

2

Why did you comment out this line

//ps.setString(11, user.getAccountType());

Your insert string has 11 placeholder parameter, you just put 10 for it. That causes the error. So reuse that line and it will be fine.

answered May 21, 2012 at 1:33

Tu Tran

1,9971 gold badge29 silver badges51 bronze badges

Comments

Balaswamy Vaddeman · Accepted Answer · 2012-05-21 01:36:35Z

0

 "INSERT INTO User (fName, lName, email_address, password, dataOfBirth, phone,"
                + " address, city,  state,  country,  zipcode, accountType)"
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?)";

You are missing accountType.you may provide extra ? in values and uncomment //ps.setString(11, user.getAccountType());

answered May 21, 2012 at 1:36

Balaswamy Vaddeman

8,6003 gold badges33 silver badges40 bronze badges

Collectives™ on Stack Overflow

SQL Error using JDBC

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related