ERROR:java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)

Question

<%            
       try {
            String username = (String)session.getAttribute("username");   
            String leaveType, startDate, endDate, reason;
            leaveType = request.getParameter("leaveType");
            startDate = request.getParameter("StartDate");
            endDate = request.getParameter("EndDate");
            reason = request.getParameter("reason");
                         
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","12345");
            username = (String)session.getAttribute("username");                                          
            PreparedStatement pst = con.prepareStatement("insert into 'leave (leaveType,StartDate,EndDate,Reason) values (?,?,?,?)");
      
            pst.setString(1, leaveType);
            pst.setString(2, startDate);
            pst.setString(3, endDate);
            pst.setString(4, reason);
           
            int row = pst.executeUpdate();
                  
            if(row==1)
            {
               %>
           <script>
            
            alert("Leave Applied");
            
        </script>
        <jsp:include page="profile.jsp"></jsp:include> 
        <% }       
       }

 catch(Exception e)
               {
                   out.println(e);
               }   
%>

What is the meaning of this error and solution? java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

URL look like this on error page(java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)): http://localhost:8080/AdvancedEmployeeManagementSystem/AleaveEmp.jsp?leaveType=SickLeave&StartDate=2023-01-28&EndDate=2023-02-05&reason=s

Stephen C · Answer 1 · 2023-01-15T07:40:08.553

I think that this is caused by the ' character. Basically, you have started a quoted string ('leave) without terminating it. My guess is that the JDBC driver's parser is assuming that the ? characters are part of the string ... and are therefore not parameter markers. Hence it says zero in the message "... 1 > number of parameters, which is 0".

Try changing " 'leave " to " `leave` "

Note it needs to be backticks rather than single quotes!! See How do I escape reserved words used as column names? MySQL/Create Table. Backticks are necessary because LEAVE is a MySQL reserved word.

ERROR:java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)

1 Answers1