CODEWITHSUNDEEP
netbeans
userNoIdea
userNoIdea

Reputation: 143

Prepared Statement errors

I am having errors. When I run the file, it is stated that " INFO: SQL Exception hit: Parameter index out of range (2 > number of parameters, which is 0). " 

PreparedStatement pst = conn.prepareStatement(
          "UPDATE Customers SET Contact = " + contact_num + "," 
          + "Email = '" + email_add + "'," 
          + "Address = '" + mail_add + "'," 
          + "SurveyStatus = " + radio_group + ","
          + "Subscription = " + receive_info + " " 
  + "WHERE MembershipID = '" + member_ID + "' " );

          pst.setString(2,contact_num);
          pst.setString(3,email_add);
          pst.setString(4,mail_add);
          pst.setString(5,radio_group);
          pst.setString(6,receive_info);
          pst.setString(1,member_ID);

           stmt.executeUpdate(strSqlnsert);
            pst.executeUpdate(strSqlUpdate);

I've got another error for this,stated that Syntax Error

"INSERT INTO Customers(MembershipID,Contact,Email,Address,SurveyStatus,Subscription) VALUES"
  + "?" + "," + "?" + "," + "?" + "," + "?" + "," + "?" + "," + "?");

Upvotes: 0

Views: 935

Answers (2)

mack
mack

Reputation: 24

Write your prepared statement like this:

PreparedStatement pst =      conn.prepareStatement( "UPDATE Customers SET   Contact =?,  Email = ?, Address = ?,SurveyStatus =?,Subscription =? WHERE MembershipID =?" );

Upvotes: 0

James Cronen
James Cronen

Reputation: 5763

In a PreparedStatement, use the ? character without any quotes to indicate a parameter replacement. 

PreparedStatement pst = conn.prepareStatement(
      "UPDATE Customers SET Contact = ?," 
      + "Email = ?," 
      + "Address = ?," 
      + "SurveyStatus = ?,"
      + "Subscription = ? " 
      + "WHERE MembershipID = ? " );

You're defeating the purpose of a PreparedStatement by substituting values directly. Then your pst.setString() calls are meaningless since there aren't any ?'s to replace.

The error message is appearing because the parser is counting ?'s, and finds zero. When you do .setString() the first time it's panicking.

Also note that you'll need to change the indexes in your .setString() calls. member_ID is the last ?, so it should be number 6 and the other indexes should be adjusted accordingly.

Upvotes: 1

Related Questions