Question 182333 in Mysql Question posted in
The official MySQL documentation can be found here.

Java code for inserting data into a MySQL database gives an 'Error: No value specified for parameter 2' – how do I fix it?

So I’m working on my internal assessment for IB for comp sci and I have the snippet of code that basically is supposed to insert a a row within a subjects table that contains both a subject name, and the amount of events under such subject. It is able to see the amount of events by referencing another separate table called ‘Events’ within the DB, and will put in the amount of events that will have the name of the subject inserted within. However, the error I am getting is:
"Error: No value specified for parameter 2", what is going on and how do I fix this?

here’s the code to work with:

“private void insertSubject_ButtonActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:

    String command = evt.getActionCommand();

    String dbName = "ScheduleManagementDB";
    String tableName = "Subjects";
    String[] columnNames =
    {
        "Subject", "EventAmount"
    };
    String dbQuery = "INSERT INTO Subjects VALUES(?,?)";

    JavaMySQL_DB objDb = new JavaMySQL_DB(dbName);
    Connection myDbConn = objDb.getDbConn();
    PreparedStatement ps = null;
    try
    {
        ps = myDbConn.prepareStatement(dbQuery);
    }
    catch (SQLException ex)
    {

        System.out.print("error");
    }
    String Subject;
    int EventAmount;

    Subject = insertSubject_Field.getText();

    String getCountQuery = "SELECT COUNT(*) FROM Events WHERE SubjectE = ?";
    int count = 0;

    try
    {
        ps.setString(1, Subject);
        ps.executeUpdate();

        PreparedStatement getCountPs = myDbConn.prepareStatement(getCountQuery);
        getCountPs.setString(1, Subject);
        ResultSet rs = getCountPs.executeQuery();
        if (rs.next())
        {
            count = rs.getInt(1);
        }

        ps.setInt(2, count);
        ps.executeUpdate();

        System.out.println("Data Inserted");

    }
    catch (SQLException e)
    {
        System.out.println("Error: " + e.getMessage());
    }

I tried not using a result set either as I thought that the getCountQuery statement would work anyways, as I just need a value but that didn’t work either.

Tags:

2

Answers


  1. 0

    The preparedstatement expects 2 parameters for the 2 placeholders:

    String dbQuery = "INSERT INTO Subjects VALUES(?,?)";
ps = myDbConn.prepareStatement(dbQuery);

    When you execute your update, you only provide one placeholder, thus its not valid. Now, i m not sure if the subject table allows nulls, but even then i think you would still need to explicitly replace the correct placeholder with a null value.

    ps.setString(1, Subject);
ps.executeUpdate();

    Now as for a quick fix, that would actually depend on what you hope to achieve with your code. Any recommendations i make might fix the error, but not help you achieve your end result, so take that in mind. The cxode you have:

    ps.setString(1, Subject);  //<-- move this
        ps.executeUpdate(); //<-- remove this

        PreparedStatement getCountPs = myDbConn.prepareStatement(getCountQuery);
        getCountPs.setString(1, Subject);
        ResultSet rs = getCountPs.executeQuery();
        if (rs.next())
        {
            count = rs.getInt(1);
        }

        ps.setInt(2, count);
        ps.executeUpdate();

    would be changed to:

        PreparedStatement getCountPs = myDbConn.prepareStatement(getCountQuery);
    getCountPs.setString(1, Subject);
    ResultSet rs = getCountPs.executeQuery();
    if (rs.next())
    {
        count = rs.getInt(1);
    }

    ps.setString(1, Subject); //<--moved
    ps.setInt(2, count);
    ps.executeUpdate();
    Login or Signup to reply.
  2. 0

    You can’t invoke the PreparedStatement ps until you bind both parameters. You currently bind the first one, then attempt to call execute update and then attempt to load the second value by calling another PreparedStatement. Perform that first. Also, you currently leak your Connection, Statement(s) and ResultSet(s). Either close them yourself, or use a try-with-Resources. Something like

    private void insertSubject_ButtonActionPerformed(java.awt.event.ActionEvent evt) {
    String command = evt.getActionCommand();
    String dbName = "ScheduleManagementDB";
    String tableName = "Subjects";
    String[] columnNames = { "Subject", "EventAmount" };
    String dbQuery = "INSERT INTO Subjects VALUES(?,?)";
    String getCountQuery = "SELECT COUNT(*) FROM Events WHERE SubjectE = ?";

    JavaMySQL_DB objDb = new JavaMySQL_DB(dbName);

    String Subject = insertSubject_Field.getText();
    int count = 0;
    try (Connection myDbConn = objDb.getDbConn()) {
        try (PreparedStatement getCountPs = myDbConn
                    .prepareStatement(getCountQuery)) {
            getCountPs.setString(1, Subject);
            try (ResultSet rs = getCountPs.executeQuery()) {
                if (rs.next()) {
                    count = rs.getInt(1);
                }
            }
        }
        try (PreparedStatement ps = myDbConn.prepareStatement(dbQuery)) {
            ps.setString(1, Subject);
            ps.setInt(2, count);
            ps.executeUpdate();
            System.out.println("Data Inserted");
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search