SQLException: Number of values not same

Question

I am using Apache Derby as an embedded database.

DatabaseCheck.java

This class is used to create the tables.

package normal;

//This class if s for checking the database. If the database doesn't exists, this class will create one

import java.sql.*;

public class DatabaseCheck
{
    private Connection con;

    public DatabaseCheck()
    {
        createConnection();
        try
        {
            Statement st = con.createStatement();
            st.executeQuery("select * from PhoneData");

            new MainForm();
        }
        catch(Exception e)
        {
            System.out.println(e.getLocalizedMessage());

            if(e.getLocalizedMessage().equals("Schema 'SA' does not exist"))
            {
                try
                {
                PreparedStatement ps = con.prepareStatement("create table PhoneData(ids integer generated always as identity constraint pkId primary key,names varchar(20),mobileNumber1 varchar(20),mobileNumber2 varchar(20),landNumber1 varchar(20),landNumber2 varchar(20),address varchar(100),category varchar(20),nickName varchar(20),email varchar(20),middleName varchar(20),lastName varchar(20),city varchar(20),country varchar(20))");
                ps.execute();

                PreparedStatement ps2 = con.prepareStatement("create table Emails(accountType varchar(10) constraint pk_user primary key,userName varchar(50) ,passwords varchar(50))");
                ps2.execute();

                new MainForm();

                }
                catch(Exception e2)
                {
                    e2.printStackTrace();
                }
            }
        }
        finally
        {
            closeConnection();
        }
    }

     public void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

            con = DriverManager.getConnection("jdbc:derby:PhoneBook;create=true","sa","sasasa");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

      public void closeConnection()
    {
        try
        {
            con.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }
}

After the table creation, I am inserting data using a method in another class.

 public void createConnection()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

            con = DriverManager.getConnection("jdbc:derby:PhoneBook","sa","sasasa");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }



    @Override
    public void closeConnection()
    {
        try
        {
            con.close();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }



    @Override
    public String insertData(String firstName, String mobileNumber1, String mobileNumber2, String landNumber1, String landNumber2, String streetAddress, String category, String nickName, String email, String middleName, String lastName, String city, String country)
    {
        String resultString="";
        try
        {
            createConnection();

            con.setAutoCommit(false);
            PreparedStatement ps = con.prepareStatement("insert into PhoneData values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1, firstName);
            ps.setString(2, mobileNumber1);
            ps.setString(3, mobileNumber2);
            ps.setString(4, landNumber1);
            ps.setString(5,landNumber2);
            ps.setString(6,streetAddress);
            ps.setString(7,category);
            ps.setString(8,nickName);
            ps.setString(9,email);
            ps.setString(10,middleName);
            ps.setString(11,lastName);
            ps.setString(12,city);
            ps.setString(13,country);

            int result = ps.executeUpdate();

            if(result>0)
            {
                resultString =  "Data Entered Successfully";
            }
            else
            {
                resultString =  "Data Enter Failed";
            }
            con.commit();
        }
        catch(SQLException se)
        {
            try
            {
                con.rollback();
                resultString = "Data rollbacked because of an error";
                se.printStackTrace();
            }
            catch(Exception e)
            {
                resultString = "Data insertion failed and rollback failed because of an error";
                e.printStackTrace();
            }
        }
        finally
        {
            closeConnection();
        }

        return resultString;
    }

I never managed to insert any, because of the following error

java.sql.SQLSyntaxErrorException: The number of values assigned is not the same as the number of specified or implied columns.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at normal.DatabaseHandler.insertData(DatabaseHandler.java:56)
    at normal.InsertForm$InsertDataAction.actionPerformed(InsertForm.java:385)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6269)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4860)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2713)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
    at java.awt.EventQueue.access$000(EventQueue.java:101)
    at java.awt.EventQueue$3.run(EventQueue.java:666)
    at java.awt.EventQueue$3.run(EventQueue.java:664)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:680)
    at java.awt.EventQueue$4.run(EventQueue.java:678)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Caused by: java.sql.SQLException: The number of values assigned is not the same as the number of specified or implied columns.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 51 more
Caused by: ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    ... 45 more
BUILD SUCCESSFUL (total time: 10 seconds)

Why is this?

Answer 1

Your INSERT query is not correct, when you use that type of syntax, it is assume that all fileds must have values and since you have an auto_incremented column, you did not include it which makes it invalid.

So to correct your syntax, you must explicitly declare all columns, example

INSERT INTO tableName(col1, col2) VALUES (?,?)

If I'm not mistaken, you have 14 columns in your table PhoneData and you have only supplied 13 values for 13 column excluding the auto_increment ID. The correct query for your prepared statement should look like this,

INSERT INTO PhoneData( Names, mobileNumber1,..., country) VALUES (?,?,...,?)