how to solve this java.sql.SQLException: Statement parameter <number> not set

Question

this is what is my DAO class:

public Map<String, String> issueOrUpdate(Map<String,String> employeeTempcardMap){
        SlingDAO slingdao = new SlingDAO();
        Map<String, String> issueOrUpdateMap = new HashMap<String, String>();
        StringBuffer issueOrReturnTempcardQuery = new StringBuffer();
        String empname="",functionkey="",tempcardnumber="",dateofissue="",dateofreturn="";
        int empid=0,rowid=0,insertOrUpdateStatus=0;
        Connection connection = slingdao.getConnection();
        PreparedStatement issueOrReturnPS=null;
       if (connection != null) {
           try {
            if(employeeTempcardMap.get("functionkey").toString().equals("issueTempCard")){
                empid =Integer.parseInt(employeeTempcardMap.get("empid").toString()); //empname,tempcardnumber,dateofissue,rowid,dateofreturn
                empname =employeeTempcardMap.get("empname").toString();
                tempcardnumber =employeeTempcardMap.get("tempcardnumber").toString();
                dateofissue =employeeTempcardMap.get("issuedate").toString();
                //System.out.println("11111111111111111111111111111111111");
                //System.out.println("empid : "+empid+" : empname: "+empname+" : tempcardnumber : "+tempcardnumber+": dateofissue "+dateofissue+"Status : "+applicationConstants.ISSUED);

                issueOrReturnTempcardQuery.append(" INSERT INTO acct_tempcardhistory(empid,empname,tempcardnumber,tempcardstatus,issuedate,returndate) ");
                issueOrReturnTempcardQuery.append(" VALUES(?,?,?,?,?,?) ");
            }else{
                dateofreturn = employeeTempcardMap.get("returndate").toString();
                rowid = Integer.parseInt(employeeTempcardMap.get("rowid").toString());
                issueOrReturnTempcardQuery.append(" UPDATE acct_tempcardhistory  SET tempcardstatus=?,returndate=? ");
                issueOrReturnTempcardQuery.append(" WHERE id ="+rowid);
            }

                issueOrReturnPS = connection.prepareStatement(issueOrReturnTempcardQuery.toString());

               if(functionkey.equals("issueTempCard")){
                   //System.out.println("2222222222222222222222");
                   issueOrReturnPS.setInt(1, empid);
                   issueOrReturnPS.setString(2,empname.toString().trim());
                   issueOrReturnPS.setString(3, tempcardnumber.toString().trim());
                   issueOrReturnPS.setString(4, applicationConstants.ISSUED.toString().trim());
                   issueOrReturnPS.setString(5, dateofissue.toString().trim());
                   issueOrReturnPS.setString(6, "".toString().trim());
                   //issueOrUpdateMap.put("message","Temporary Card has been issued to "+empid);

               }else {
                   issueOrReturnPS.setString(1, applicationConstants.RETURNED);
                   issueOrReturnPS.setString(2,dateofreturn);
                   //issueOrUpdateMap.put("message","Temporary Card has been Returned by "+empid);
               }
               insertOrUpdateStatus = issueOrReturnPS.executeUpdate();

               System.out.println(insertOrUpdateStatus + "row(s) affected");

               if(insertOrUpdateStatus >0){
                   issueOrUpdateMap.put("message","Temporary Card has been issued/Returned to "+empid);
               }else{
                   issueOrUpdateMap.put("message","Temporary Card has not been issued/Returned to "+empid);
               }
               connection.close();

              // connection.commit();
           } catch (Exception ex) {
            ex.printStackTrace();
            issueOrUpdateMap.put("message","Temporary Card has not been issued/Returned to "+empid);
           } finally {
               try {
                slingdao.closeConnection(connection, null, issueOrReturnPS);
               } catch (Exception ex) {
                   ex.printStackTrace();
               }
           }
       } else {
           issueOrUpdateMap.put("dbmessage","Connection not Established. Please Contact Vendor");
       }
       return issueOrUpdateMap;
   }
}

UPDATE :

Stacktrace :

java.sql.SQLException: Statement parameter 3 not set.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1045)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:693)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedS
tatement.java:105)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedS
tatement.java:105)
        at com.slingmedia.notifier.dao.TempCardDAO.issueOrUpdate(TempCardDAO.java:158)
        at com.slingmedia.notifier.servlet.TempCardServlet.doPost(TempCardServlet.java:83)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j
ava:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)

        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999
)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.jav
a:565)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1812)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:662)

when i printed, i see all the values coming but still it shows that

java.sql.SQLException: Statement parameter <number> not set.

the same is working for when updating but it is not working for when inserting data, i have also made date as a string.

Please guide me how to go about or where do i make mistak?

Regards

Answer 1

it means you trying to insert less values as code expected...

Example you are inserting "1,2" but code expected 3 so,it should be "1,2," it will work

Answer 2

you have:

if(functionkey.equals("issueTempCard")){
                   //System.out.println("2222222222222222222222");
                   issueOrReturnPS.setInt(1, empid);
                   issueOrReturnPS.setString(2,empname.toString().trim());
                   issueOrReturnPS.setString(3, tempcardnumber.toString().trim());
                   issueOrReturnPS.setString(4, applicationConstants.ISSUED.toString().trim());
                   issueOrReturnPS.setString(5, dateofissue.toString().trim());
                   issueOrReturnPS.setString(6, "".toString().trim());
                   //issueOrUpdateMap.put("message","Temporary Card has been issued to "+empid);

               }else {
                   issueOrReturnPS.setString(1, applicationConstants.RETURNED);
                   issueOrReturnPS.setString(2,dateofreturn);
                   //issueOrUpdateMap.put("message","Temporary Card has been Returned by "+empid);
               }

in case the else is called , you only set parameter 1 and 2, but you have 6 params!

Answer 3

You forgot to set functionKey to the result of the expression

employeeTempcardMap.get("functionkey").toString();

Never declare, and especially never initialize your local vars in advance with dummy values. That just masks coding problems like this. If you had final String functionKey = ..., this kind of error would be theoretically impossible.

Answer 4

Look at this:

if(functionkey.equals("issueTempCard")){

Now consider where you expect the functionkey variable to get a value from. How are you ever expecting it to be equal to issueTempCard? Why is your condition for creating the SQL statement different from your condition for setting the parameters? Why do you even have two separate if/else statements?

I'd strongly advise you to refactor this method - if you make your code clearer, it'll help you to spot problems like this more easily. Also, I'd strongly encourage you to use parameters for all values, avoiding code like this:

issueOrReturnTempcardQuery.append(" WHERE id ="+rowid);