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

Question

I tried to search the data from mysql databases but I got this error:

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

main.jsp:

<form action = "SearchCheck" method="post">      
    <input type="text" id="search" class="fadeIn fourth" name="search" placeholder="search">
    <input type="submit" class="fadeIn fourth" value="Search">
</form>

SearchCheck.java:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    response.getWriter().append("Served at: ").append(request.getContextPath());
}

/**
 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
 */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    request.setCharacterEncoding("EUC-KR");     
    response.setContentType("text/html; charset=euc-kr");
    userSearch=request.getParameter("search");

    if(userSearch == null || userSearch =="" ) {

        PrintWriter out = response.getWriter();
        out.println("<script type=\"text/javascript\">");
        out.println("alert('이름을 채워주십시오');");
        out.println("location='main.jsp';");
        out.println("</script>");

    }


    else {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/member", "root", "Wlsdud1964");                         
            String sql = "SELECT * FROM  user where userName LIKE '%"+userSearch+"%'";              
            System.out.println(sql);        
            ps = conn.prepareStatement(sql);            
            ps.setString(1, userName);
            resultSet = ps.executeQuery();

            //response.sendRedirect("loginCheckResult.jsp");

            while(resultSet.next()) {               
                resultSet.getString(userName);
                resultSet.getString(userGroup);
                PrintWriter out = response.getWriter();
                out.println(resultSet.getString(userName) + resultSet.getString(userGroup));
                out.println("<br /");       
            }           
        } catch(Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if(stmt != null)stmt.close();
                if(conn != null)conn.close();
            }catch(Exception e) {
                e.printStackTrace();
            }
        }   
    }//else     
} //doPost

Possible duplicate of java.sql.SQLException Parameter index out of range (1 > number of parameters, which is 0) — Jerry Chin
– Jerry Chin, Commented Mar 7, 2019 at 2:13

Jozef Chocholacek · Accepted Answer · 2019-03-07 07:14:02Z

1

Tahir was almost right in his answer, the proper SQL query should be

String sql = "SELECT * FROM  user where userName LIKE ?";

and then you have to add % wildcards to the parameter "manually", i.e.

String queryString = "%" + userSearch + "%";
ps.setString(1, queryString);

answered Mar 7, 2019 at 7:14

Jozef Chocholacek

2,9242 gold badges22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tahir Hussain Mir Over a year ago

Wow, never noticed that. Will give it a look. Thanks for the info

Tahir Hussain Mir · Accepted Answer · 2019-03-08 01:14:39Z

0

EDIT: As Jozef mentioned in one answer, ? should not be put between string literal '%?%' so append % to the string parameter before it is set in the query There is no parameter in your sql query...

replace the query to

String sql = "SELECT * FROM  user where userName LIKE ?"

? is the parameter where your string will be set.
where string will be like "%"+inputString+"%";

edited Mar 8, 2019 at 1:14

answered Mar 7, 2019 at 2:07

Tahir Hussain Mir

2,6362 gold badges22 silver badges27 bronze badges

3 Comments

Jin Huh Over a year ago

i replaced the query to String sql = "SELECT * FROM user where userName LIKE '%?%'" but same error

Tahir Hussain Mir Over a year ago

it can't be. I think you check again or if it still persists, then someone wrote a comment on your question. Check that links 2nd answer

Jozef Chocholacek Over a year ago

@TahirHussainMir you cannot put ? into a string literal ('%?%'), it has always to be a separate parameter (... WHERE userName LIKE ? ...), then you have to add the % wildcards to the value "manually". See my answer.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related