错误:索引参数超出范围

Question

我想插入组合框中选定项的主键

但是我得到了这个错误Parameter index out of range(12 > number of parameter which is 0) ".

我真的不知道如何解决这个问题。

下面是我的代码:

String valueTrainer = "kosong";
try
{
   String sql2 = "Insert into ahli (MemberID, TrainerID, Name, ICNumber, Address, Nationality,"
                        + "PhoneNumber, Email, EmergencyPerson, EmergencyContact, DateReg, MemberTypeID) "
                        + "values(?,?,?,?,?,?,?,?,?,?,?,?)";

                pst = conn.prepareStatement(sql2);
                pst.setString(1, MemberIDTextField.getText());
                pst.setString(2, valueTrainer);
                pst.setString(3, NameTextField.getText());
                pst.setString(4, jTextField1.getText());
                pst.setString(5, AddressTextArea.getText());
                //Nationality combo box
                String nationalityList = NationalityComboBox.getSelectedItem().toString();
                pst.setString(6, nationalityList);
                pst.setString(7, PhoneNumberTextField.getText());
                pst.setString(8, EmailTextField.getText());
                pst.setString(9, EmerContactPersonTextField.getText());
                pst.setString(10, EmerContactNumberTextField.getText());
                //Date Chooser
                pst.setString(11, ((JTextField)MemberDateChooser.getDateEditor().getUiComponent()).getText());
                //membertype combobox
                // problem start from here, I think..
                String memberTypeList = MemberTypeComboBox.getSelectedItem().toString();
                String sql1  ="Select MemberTypeID from jeniskeahlian where Type = '"+memberTypeList+"' " ;
                pst = conn.prepareStatement(sql1);
                rs = pst.executeQuery();
                while(rs.next()){
                    String memberType = rs.getString("MemberTypeID");
                    pst.setString(12, memberType);
               }
                pst.execute();
                JOptionPane.showMessageDialog(null, "New member has been added");
            } catch (SQLException | HeadlessException e) {
                JOptionPane.showMessageDialog(null, e);
            }

Answer 1

您没有正确使用pst

预处理语句上的同名引用不能在不同的SQL语句上使用，除非其中一个被正确使用并关闭。在一条语句上准备、设置、执行、关闭，然后在另一条语句上重复相同的操作。

修改代码如下。

try
{
   String memberTypeList = MemberTypeComboBox.getSelectedItem().toString();
   String sql1  ="Select MemberTypeID 
                    from jeniskeahlian 
                   where Type = ? " ;
   pst = conn.prepareStatement(sql1);
   pst.setString( 1, memberTypeList );
   rs = pst.executeQuery();
   String memberType = "";
   while(rs.next()){
      memberType = rs.getString("MemberTypeID");
   }
   rs.close();
   pst.close();
   String sql2 = "Insert into ahli (MemberID, TrainerID, Name, 
                                    ICNumber, Address, Nationality,
                                    PhoneNumber, Email, EmergencyPerson, 
                                    EmergencyContact, DateReg, MemberTypeID)
                  values(?,?,?,?,?,?,?,?,?,?,?,?)";
   pst = conn.prepareStatement(sql2);
   pst.setString(1, MemberIDTextField.getText());
   pst.setString(2, valueTrainer);
   pst.setString(3, NameTextField.getText());
   pst.setString(4, jTextField1.getText());
   pst.setString(5, AddressTextArea.getText());
   //Nationality combo box
   String nationalityList = NationalityComboBox.getSelectedItem().toString();
   pst.setString(6, nationalityList);
   pst.setString(7, PhoneNumberTextField.getText());
   pst.setString(8, EmailTextField.getText());
   pst.setString(9, EmerContactPersonTextField.getText());
   pst.setString(10, EmerContactNumberTextField.getText());
   //Date Chooser
   pst.setString(11, ((JTextField)MemberDateChooser
                                 .getDateEditor()
                                 .getUiComponent()).getText());
   pst.setString(12, memberType);
   pst.execute();
   JOptionPane.showMessageDialog(null, "New member has been added");
} // try

Answer 2

虽然您的插入语句似乎正确填充，但在While -loop中，您很少尝试执行没有参数的select语句，因此pst.setString(12,memberType)失败。

String sql1  ="Select MemberTypeID from jeniskeahlian where Type = '"+memberTypeList+"' " ; //no ? in this query

Answer 3

在执行查询之前，您应该为所有参数设置所有值。